Tag Archives: Microsoft Access

Upgrading to a Database

You know it’s time for your business to upgrade to a database when: Spreadsheets aren’t enough to organize the growing amounts of electronic data anymore, it’s hurting business and clients are screaming for change. The advantages of replacing stand-alone programs outweigh the time and expense of switching to something new. In this age of hackers, viruses and identity theft, it’s not safe for people to keep valuable information on desktop or laptop computers. No matter what the reason, even the smallest business can benefit from upgrading to a database to centrally collect and manage vital company information, according to analyst and industry experts. First introduced in the 1970s, relational databases consolidate and store information in tables that can be shuffled and reshuffled myriad ways, helping companies better track diverse data such as sales transactions, inventory and customer profiles. Plethora of products Today, small businesses have a wealth of database types and vendors to choose from, including: Low-cost solutions like Microsoft Access, part of the Office product suite, or Filemaker Pro from Filemaker. Open source products like MySQL or the Base database fromOpenOffice.org. Software-as-a-service offerings such as InternetOffice.biz. Entry-level enterprise database software from industry leaders such as Oracle, Sybase and Microsoft But don’t put the cart before the horse. The first step in a database upgrade isn’t picking the software. It’s deciding what you’re going to use it for, who’ll be using it, and how far it has to scale as your company grows, says Noel Yuhanna, a database analyst at Forrester Research in Cambridge, Mass. Once you’ve created the database and know what needs to be moved, automated tools can easily transfer data from existing spreadsheets and other files to the new system, Yuhanna says. With identify theft and other computer-related crimes on the rise, security is another reason companies switch to centralized databases, Yuhanna says. “If someone were to remove files from your desktop or laptop, you might not know about it. Anybody can change a figure and you wouldn’t know it. In a database, you can track that.” Who’s in charge Deciding what you need a database to do might be a group effort, but the job of putting a plan into action typically falls to a select few. At small companies, that might be the most tech savvy person on staff, or a consultant who’s hired for a month or two to get things running and train the staff. Mid-sized companies might need one or two IT people to maintain associated hardware and software, according to Greg Nelson a former software company owner and currently chairman of the Naples, Fla., chapter of SCORE, the small-business advisory group. Costs will vary accordingly, Nelson says. For a small company using Microsoft Access to create a database for 10 people, the costs would be nominal. But a database with 500 users and roll-back capabilities that minimize lost data in the event of a power failure could be $25,000 in software alone, he says. However, any forward-looking company shouldn’t think twice about putting a properly constructed database in place, Nelson says. “Having the right information available at the right time,” he says, “can certainly make the difference between success and failure.”

Working Smarter With Microsoft Office

A decade ago, it was very difficult to complete a mail merge between applications from different vendors’ software packages. Don’t miss out on one of the most compelling benefits of Microsoft’s stronghold over desktop office automation software. Be sure to take advantage of the tight integration across Microsoft products. Don’t Retype It. Merge It! What are some easy ways you can begin putting this integration to use? Two examples immediately come to mind: mail merge and financial statement tables. Do you need to create automatically, some customized mailing labels, envelopes or form letters, but often end up settling for some low-tech, tedious, scissors-and-glue approach? The Mail Merge Helper wizard in Microsoft Word makes it very easy to pull in address lists, or similar data, from such programs as Microsoft Excel, Microsoft Outlook and Microsoft Access. Don’t even think about having your clerical staff retype the information! Preparing Tables: Just Say No to Spaces and Tabs Another small-business redundancy has to do with preparing financial statements within documents. In most companies, a CFO, accounting manger or controller prepares various financial statements in Microsoft Excel, and then passes the information over to an executive assistant to incorporate into regulatory documents, lender and investor updates, and annual reports. Make sure your company isn’t falling into this huge productivity trap. Be sure your executive assistant, or whomever is charged with pulling together these documents in Microsoft Word, is aware of simple, enormous timesaving shortcuts such as the Paste Special command on the Edit menu and the Object command on the Insert menu. All too often, I see the executive assistant retyping these financial statements from scratch in Microsoft Word, either by using the Insert Table feature or, even worse, by aligning text by using spaces and tabs. Ouch! Be Diplomatic and Tread Gently at First Not all companies will suffer from these same redundancies. But someone needs to take the time to go on a fact-finding mission in your organization to spot and remedy at least some of these inefficiencies. Because of the sensitive nature of this, you may be better off leaning on your accountants or auditors for this task. In other cases, there’s no reason a PC savvy office manager, controller or CFO can’t preclude or successfully manage this exploratory work. Discussion Points Do you have a hunch that employees are retyping the same information over and over again? Do your administrative staff members know how to fully exploit the mail merge features in the Microsoft Office suite? Does your financial and publications staff understand how to easily take data prepared in Microsoft Excel and seamlessly place it into Microsoft Word documents? Is there anyone in your company who’s in a natural position to spend some time with each department or job function and look for opportunities to streamline workloads through more efficient use of Microsoft Office applications? Joshua Feinberg (joshua@smallbiztechtalk.com) helps small businesses save money on computer support costs. His latest book, What Your Computer Consultant Doesn’t Want You to Know ($19.99, Small Biz Tech Talk Press), exposes 101 money-saving secrets of expensive techies. To order Joshua’s new book, visit www.SmallBizTechTalk.com or call 866-TECH-EXPERT (866-832-4397). © Copyright 2002, Joshua Feinberg Small Biz Tech Talk is a registered trademark of KISTech Communications

Database Driving Essentials

You’ve honed your HTML skills and now you want to take your Web site to the next level by adding some database interactivity. You’ve browsed around the Net looking for ideas, tried out Web-based tutorials, and you’re raring to get going with some real code. Here are five essential resources you’re going to need on your journey — don’t set out without them. Yes, the advocates of tools such as Macromedia Dreamweaver UltraDev and Microsoft Visual Studio claim you can build your site just by pointing and clicking, without writing a line of code. These packages come with excellent tutorials that appear to prove this point. But believe me, out there in the messy real world, when you are working on a live application, you will need to edit that code by hand. Ignore all those seasoned Web hands who boast they only use Notepad and get yourself a proper programmer’s text editor. It might cost you all of $20 (U.S.), but it will be worth every cent. I’ve been using TextPad constantly, for years, and I wouldn’t exchange it for anything else. But quite a few equally good editors are out there, including NoteTab, EditPlus and UltraEdit. Or try a search at a site such as Download.com to find others. Some text editors are free, others cost a few bucks. Download evaluation copies, try them and register the one you feel most comfortable using. Then spend some time getting to know its capabilities. All these editors offer some incredibly powerful features, such as advanced search and replace options, which can save you hours of tedious work. Test EnvironmentSet up a Web server and a database on your own computer for testing. When you’re just starting out in server-side programming, you will make plenty of mistakes — and some of these mistakes will crash your machine. I can assure you it’s much less hassle to crash your own PC than your live Web server. If you’re paying for your Internet access by the minute, testing off-line will also save you money. Microsoft Windows 98, 2000 and ME come with a Web server built into the operating system (Personal Web Server in the case of Windows 98, and Internet Information Server for Windows 2000) so you are likely to have one installed. You’ll probably also need to set up a database connection using Object Database Connectivity between your selected database — for example, Microsoft Access — and your Web server. If your target environment is a UNIX Web server running Apache, PHP and MySQL, a very common configuration, you can still set up a reasonably close approximation on your PC. Windows versions of all three of these applications are available. Setup is considerably more complex, but you’ll find some good tutorials at Webmonkey that take you through the process step by step. BookmarksWhich brings me to essential tool No. 3, a collection of bookmarks to help you learn your chosen technology. Whatever you need to do when you’re starting out, you’ll almost never have to develop it from scratch. You’re certain to find an example you can adapt to your needs on one of the many tutorial sites. Even when you’re more experienced, these sites are a rich source of information.

Database Groundwork

You’ve thought about consolidating your business records into a database, but you hesitate because of the time and the effort required. Think again. You probably need a database if: You enter or update the same information in more than one place. You have overflowing file cabinets. You juggle multiple spreadsheets. Once you decide to build and maintain it, a database saves you time and money. In addition, a database is an indispensable tool for assessing your business. Here’s how: Databases track information better than disparate records. For example, your customer’s name and address might be on an order form and a spreadsheet. If you track this information manually, you have to update each record when customers move. A database provides a central repository. One address update covers all needs. Databases organize information efficiently. By keeping business details in one database, you can sort and search details at lightning speed. If your business tracks parts and suppliers, a query to find all suppliers selling widgets returns the answers with a click of the mouse. Try finding data this quickly by hand. Databases report on the state of your business. Query responses and reports give you answers to questions about your business. Because the database compiles information fast, can you not only keep a closer eye on your business tactics, you can more quickly fine-tune processes that don’t work. Start Simply You already use database functions if you manage your finances with or store your address book on a computer. If you do not use these products, play around with a simple personal information manager such as the address book in Microsoft Outlook or Palm Desktop. Or automate your accounting with the popular and easy-to-use Quicken QuickBooks. Using these applications helps you understand database functionality and can give you ideas for how you might use a database to organize other records. Start by asking, what records do I want to automate? For example, you can see how the ledgers in QuickBooks are analogous to your paper ledgers. From there you can imagine how to organize your customer contact and order information into a customer database and use the database tools to see who bought what, when. Or think about how to enter your product information into a database to manage inventory levels. Reality Check Note that databases can only organize existing record-keeping. A database will not create organization from nothing. When building a database, you must have an idea of how specific pieces of information relate to each other. These relationships determine the structure of the database. Planning the structure will take time. But it’s time well spent. A well-designed database spares you the agony of inadequate record-keeping and the trouble of reorganizing later. Moreover, have reasonable expectations of what databases can do. You might be accustomed to robust applications such as Outlook and QuickBooks, but these are powerful, specialized programs with a long history of development. Building your database with a program like Microsoft Access or FileMaker Pro can’t match those standards, so think simply. Copyright © 1995-2001 Pinnacle WebWorkz Inc. All rights reserved. Do not duplicate or redistribute in any form.

Safe House

Shop Talk CEOs Search for the Right Technology A good data backup system can preserve not just your company but your sanity As the Y2K panic proved, the most common culprit for lost computer data is not system failure. It’s plain old user error. And the only way to combat that is with an electronic safeguard — a data backup system. Patrick Guthrie, president and chief technology officer of the Pajo Group, a $15-million Internet service provider in Long Beach, Calif., learned that the hard way. In early 1998 a manager’s tinkering rendered the company’s customer database inaccessible. Guthrie wasn’t too worried because he had easily recovered backed-up copies in the past. This time, however, none of his ideas worked. “We were frantic,” he says. Finally, he was forced to do something he hated: call in a consultant. “We paid him his $125 an hour,” Guthrie says ruefully. “It’s amazing how monetary limitations don’t apply when you’re trying to get your data back.” The incident was enough to spur him into looking for a backup system with more capacity and faster access. Like many start-ups, the Pajo Group had built its backup system around the Band-Aid principle — an effective enough method when it had to find lost E-mail for its 20 customers. The company’s first purchase was a Hewlett-Packard Colorado Trakker 350 tape drive that cost about $500. “Back then [in late 1997] we were running pretty lean and mean,” says Guthrie, “so we fixed problems as they happened.” The tape drive stored all Pajo’s data — a customer database, financial files, customers’ files, and the company’s own ISP-related files — on 350MB magnetic tapes that resembled double-thick cassettes. Each tape had cost about $20 or $30. Guthrie himself executed the backup, inserting a tape into the drive each night and removing it the next morning. He completed the procedure by storing the tapes in a fireproof box in the company’s offices in case of disaster. The system worked fine, but Guthrie found that the recovery process averaged 10 minutes per file — an inordinate amount of time — because he had to rewind and search the entire tape for the lost data. True, he had to go through the process only about two times a month, but he knew that the number of requests was going to grow. Plus, because of his expanding client base, 350MB was too little space per tape; on many nights the tapes filled up before backup was complete. Pajo hadn’t yet begun offering 24-hour technical support, so there was no one around in the wee hours to replace the full tapes with empty ones. Then came the last straw: the customer-database fiasco. Determined to have a more robust system, Guthrie purchased an Iomega Jaz drive for $300 at a computer superstore after spending time at Iomega’s booth at a trade show. It was bigger than his tape drive — up to one gigabyte (1,000MB) of data could be stored on a Jaz cartridge. And it was much faster. As he watched the Jaz drive back up the amount of data in 10 minutes that the Colorado drive had handled in two hours, Guthrie became an instant fan. But he realized too late that he’d made his decision too quickly. Business was still booming, and nightly backups were running about 650MB and climbing. He was now using one cartridge a day that cost $80 to $90 for storage. That meant Guthrie was paying more each week to store his data than he had spent on the drive itself. “Up until then I had always relied on our vendors for accurate technical advice,” says Patrick Guthrie. “I couldn’t do that anymore.” By early 1999, Pajo’s menu of services had expanded to include hosting Web sites, colocating Web servers (meaning that his customers’ servers actually resided at Pajo), and handling thousands of E-mail accounts and more than 150 T1-line customers. To support all the traffic, Pajo had a United Nations­like network that featured operating systems ranging from Windows NT to Linux to Unix and even to the Mac OS. If Pajo were ever to move beyond the Band-Aid approach to backup, the time had come. Guthrie started asking around for advice. The consensus, from Pajo vendors like Ingram Micro and Tech Data as well as some consultants, was that a digital audiotape (DAT) drive would be the way to go. A DAT drive can store up to 40GB of data on one tape, at a cost comparable to that of storing data on magnetic tapes — less than 10¢ per megabyte and half that for storing fully compressed data. However, compared with magnetic tapes, a DAT drive is less unwieldy to use for retrieving data. And although it’s not as fast as a Jaz drive, a DAT drive takes only about 40 seconds to locate a file. To run the DAT drive, Guthrie’s vendors suggested that he use Seagate Technology’s Backup Exec 7.2 software (it’s now a product of Veritas) — a far more sophisticated brand of backup software than he had used with the other drives. Guthrie wasn’t quite sold, but then his sanity-check Internet search for “backup software” turned up Seagate’s name repeatedly. So he purchased Seagate’s Backup Exec software in conjunction with Hewlett-Packard’s HP SureStore DAT24 drive, so named because it was capable of holding 24GB of data (again, in a perfectly compressed world). The price: $840 for the software and $1,251 for the drive. Guthrie installed the software as well as the DAT drive on a server running Windows NT. That was a snap, but configuring the software to back up data across a smorgasbord of operating systems wasn’t. To facilitate communication between Linux and the company’s other systems, Guthrie earlier had created shortcuts called “Samba shares.” For three days Guthrie tried to get the Backup Exec software to recognize the Samba shares, convinced that he had to be doing something wrong. Being a computer guy, he figured that if he couldn’t fix things himself, he was as good as doomed. “You’re S.O.L. once you call tech support,” he says. It certainly felt that way as he waded through Seagate’s voice-mail system. When he finally reached a technician on the third call, he explained his problem and was told he’d receive a callback. In the mean- time, he relied on the Jaz drive for backup. After two weeks had passed without a word from Seagate, he tried again. A manager assured him that he’d receive a call the next day. He did — and got some bad news: version 7.2 of Backup Exec didn’t include the right agents (technology used to accommodate different operating systems) to support any Linux shortcuts. But there was also some good news: the next version of the software would have the capability. (According to Stacey Ruscette, a spokesperson for Veritas, which purchased Seagate’s software division in May 1999, versions 7.3 and 8.0, released in June 1999 and February 2000 respectively, include the appropriate agent to support Linux.) Guthrie couldn’t wait, so he returned the software. “I kept the DAT drive,” he says, “but I was back to square one.” The experience showed him how little was commonly known about backup systems. “Up until then I had always relied on our vendors for accurate technical advice. I couldn’t do that anymore.” Guthrie instead turned to one of his young technicians, a recent college graduate with plenty of friends in other Internet companies. The technician made a few calls. He reported back to Guthrie that the highest praise for backup software capable of supporting a variety of operating systems went to Knox’s Arkeia, a product that was popular with Linux users. A few times Guthrie E-mailed Knox some questions that he was “looking for yeses to” — namely, whether the software would work with all Pajo’s operating systems (except the Mac OS), whether he could try the software risk free before buying, and whether he could get technical support 24/7. He also hoped to find a system that would allow him to start the backup from any machine, running any operating system, by means of an easy-to-navigate graphical user interface. He got his yeses. With the guarantee of a 30-day free trial, Guthrie’s young technician downloaded the Arkeia trial software from Knox’s Web site and installed it on Pajo’s Windows NT server that day — no snags, no glitches. “It was pretty sweet,” says Guthrie. Then, when he had to call Knox to clarify some settings, he got a bonus: he found himself on the phone with Sam Siegel, the company president. (As Knox was at that point only a six-person company, Siegel took his share of customer calls.) When he found out that Siegel had had a large hand in designing the software, Guthrie took great pleasure in grilling him about the product. Guthrie also got some free advice. When Siegel heard that Pajo was using a Windows NT server for primary backup, Siegel made a suggestion he’d made many times before to Linux users: why not speed up the process by running the backup from the Linux machine rather than from the Windows NT one? To Guthrie, the idea was a classic example of overlooking the obvious. “We were letting our primary operating system [Windows NT] dictate where we were going to do the backup from,” he says. Guthrie moved the DAT drive from the NT box to the Linux box. “It took longer to move the DAT drive from one computer to the next than it did to install the software. We had everything up and running within 20 minutes.” Not only did the system work perfectly, but Siegel’s claim that the backup would be 10 times faster using the Linux box was substantiated. Guthrie particularly liked the real-time graphic that monitored just how fast the backup was going. “We were all watching it, screaming, ‘Go, go, go!’ We’re men — we like to see meters,” he says. To date, the system has never failed. And it’s no problem to find that E-mail address that’s been lost in the abyss. With the DAT drive, an administrator just selects the file in question from Arkeia’s Explorer-like log, and a dialog box tells him which tape to insert into the drive to retrieve it. The process takes, at most, three minutes. Safety net Matthew Barrer calls his old method for backing up his company’s data “half-assed,” but his system is not as uncommon among small businesses as you might think. Barrer copied key files from one hard drive to another through his local area network before leaving for the night. In 1998, Barrer bought the five-year-old Philadelphia Enterpriser magazine, which is targeted at business owners and entrepreneurs in the metropolitan area. The following year he made his mark on the publication by instituting a few changes: he made the content truly regional in focus, since he knew he couldn’t compete with deep-pocketed national magazines, and he improved the company’s technology. His first upgrade was to implement GoldMine contact-management software. Instead of using Microsoft Access to house the subscriber database and boxes of note cards to keep track of advertisers, the company began operating off three GoldMine databases: one for the Enterpriser‘s 18,000 active subscribers, one for its advertisers, and one for Barrer’s own personal contacts. His second upgrade was to jury-rig that file-copying backup system to minimize the chance of losing files. But not having an official backup system gnawed at him. He didn’t want his company to become a statistic in some backup-system manufacturer’s brochure. “Reader data in the subscriber database is not something we can reconstruct easily,” he says. “Those demographics are what our advertising revenue depends on. I needed it to be secure.” Barrer started his search for a backup system as a relative novice. “I knew about tape drives,” he says, “but I didn’t know what else was out there at all.” To learn about his options, he began asking everyone he ran across about backup systems — both online and off. Barrer knew he wanted something that was not labor-intensive. And from what he was hearing, online systems virtually took care of themselves. No one would have to change the tapes and make sure the data were moved off-site. “I’d much prefer that the data be in some big data warehouse, where I’m the control point,” he says. “I don’t have a full MIS department; no one’s going to be able to do that for me.” Identifying vendors was as easy as launching his browser and searching for “online backup.” “I was looking for something that I could control and access with minimal effort, and that I could trust — it had to be encrypted and safe,” he says. He also wanted a solution that backed up any changes in his data on a daily basis. “I didn’t want to have to go back on more than a day’s activity,” he says. He ended up focusing on three Internet-based backup services that met his criteria: @Backup, Connected, and NovaStor. Using each company’s software, Barrer could connect to the Internet and automatically back up his company’s data. Further, the software allowed incremental backups to automatically launch at the same time every day (he could even choose the time) to ferret out the files that had changed in the past 24 hours. Barrer liked the sound of that — a workable day-to-day backup solution that would require little to no involvement from him. Now he just had to discover which one would best meet the Enterpriser‘s needs. With @Backup, for a $99 annual fee, users could back up as much as 100MB of data by means of a simple Internet connection. The company also offered a deal in which users could pay $300 a year to back up 500MB of data. Although both plans would have worked for Barrer personally, neither was good enough for his business. For the Enterpriser he wanted to make sure that he could restore everything, including applications and his Windows 98 operating system — 6.5GB of data — since he didn’t have an internal technical team to handle such a task. Besides, he didn’t much cotton to the idea of signing a long-term contract. Connected’s Online Backup and NovaStor’s NovaNet-Web (which is hosted by Compaq) both had the monthly, commitment-free pricing he liked — around $20 a month. Plus, they offered enough storage space for a systemwide backup. (In NovaStor’s case, if a company wants the initial backup to be done on-site, it must purchase a $200 NovaNet software package.) Price considerations alone would have made it easy to go with Connected, but Barrer was drawn to NovaStor’s connection with Compaq. Although both companies backed up clients’ data onto digital linear tape (DLT) at secure data facilities, NovaStor used a Compaq-owned data center whereas Connected had its own. (DLT drives start at twice the price of DAT drives, and their smallest capacity is 40GB — which is the largest capacity for DAT drives.) Moreover, Compaq was actually the provider to whom Barrer would be paying his monthly NovaStor bill; it offered backup service with NovaStor’s software through its Web site. “If it was good enough for Compaq,” Barrer says, “it sure as heck was good enough for me.” “If NovaStor backup was good enough for Compaq,” Matthew Barrer says, “it sure as heck was good enough for me.” The decision made, Barrer turned to an expert for the follow-through. InfoQuest, a NovaStor value-added reseller also located in Pennsylvania, installed NovaStor’s NovaNet 7 onto the Enterpriser‘s Windows NT and oversaw the initial backup, which involved 6.5GB worth of applications and operating systems on two tapes. Two copies of the information were made. One was transferred off-site to the Compaq data bank, and the other resides at InfoQuest, where it’s available for easy retrieval in case of a full-blown disaster. The rest of the Enterpriser‘s data — financial files, business correspondence, the GoldMine databases — were backed up by InfoQuest using NovaNet-Web, NovaStor’s online backup software. All Barrer had to do was install his own CD-ROM of software on the Enterpriser‘s server. Although he did call NovaNet’s customer-service reps to guide him, he was able, with virtually no problems, to use the software’s wizard to answer a series of questions that automatically set up the schedule of when he wanted his data backed up. “It passed my software test,” he said. “I was able to install it without looking at a manual.” Now, every night when the clock strikes 12, NovaNet-Web scans Barrer’s computers for changes and performs backups of any changed files. The whole process takes about 10 minutes. NovaNet-Web also backs up Barrer’s laptop nightly. “If I’m online at that late hour, I’ll get a message saying, ‘Do you want to back up now?” says Barrer. “And if I miss it, I can just back up the next time I connect to the Internet.” Barrer couldn’t be more pleased. Not only does he have a backup system that operates without human intervention, but he also has a system that works. In one case Barrer used NovaStor to restore his 45MB database of contacts, which, according to NovaStor, had been corrupted when something malfunctioned. Although the parties don’t agree on how the data were lost or whose fault it was, Barrer doesn’t particularly care. He just made sure he got a restored file, because into the void had gone the one record he’d never dare to delete: his mother’s. Mie-Yun Lee is the editorial director and founder of BuyerZone, an Internet buying service that features expert purchasing advice and tools for small and midsize businesses. You can conduct your own search for an online backup system at www.buyerzone.com/computers/backup-remote/index.html. Sandra Boncek contributed to this article. Please e-mail your comments to editors@inc.com.