So you've decided you need a database program, but you're not sure which one to choose? The reigning champions, Access and FileMaker, take up the cudgels. By Angus Kidman

Published in APC, September 2001

Databases are the poor relations in the office suite. Nearly everyone dabbles in word processing, and spreadsheets are common, but relatively few people use database software directly in their day-to-day work. This fact is reflected in Microsoft's decision not to include Access in its standard Office suite. In the history of the PC so far, this has been understandable; database tools were costly and difficult for anyone other than skilled developers to use.

Nonetheless, for maintaining large and complex sets of non-numeric data, databases are far more useful and flexible than spreadsheets (which are often the default alternative for nervous buyers). They've also enjoyed a resurgence in the era of the Web, because they can easily handle text and multimedia. They are the backbone of most major commercial Web sites. You may not be planning on building the next Amazon.com just yet, but anyone who uses a PC frequently will probably need a database program eventually.

The contenders FileMaker Pro 5.5 Contact: FileMaker Phone: 1800 028 316 Online: http://www.filemaker.com.au/ Price: $482.90 (new); $284.90 (upgrade) Access 2002 Contact: Microsoft Phone: 13 20 58 Online: http://www.microsoft.com/office/ Price $1,288 (new); $749 (upgrade)

Over the years there have been many contenders for the position of top PC database, but there are currently only two really significant ones. Microsoft's Access lurks on many desktops as part of more sophisticated Office installations and enjoys strong developer support, but it has suffered from a reputation for being difficult to use. FileMaker (spun off from Apple's software division) is generally deemed to be easier for non-technical users to work with, but it has been criticised for lacking muscle in larger business applications.

Both databases have a long development history and a well-developed community of users, and both have recently been updated in an attempt to address some of their perceived shortcomings. Access 2002 isn't included in the standard version of Office XP, but comes with the Professional edition, which will set you back an additional $200 (in both upgrade and new releases). FileMaker Pro 5.5 is slightly more expensive as a standalone product ($482.90 if you don't qualify for an upgrade), but cheaper if database software is all you're after. It also has slightly lower system requirements.

Choosing between the two comes down to features and implementation. APC has rated the two products according to their ability to perform six key tasks: building new databases from templates, importing data, mail merging, scripting and extensibility, query speed and publishing databases online. For each task, we've awarded a score out of 10. Who will be the database champion?

Creating new databases

Early PC databases earned much of their reputation for difficulty because they required you to have a detailed plan for the data you wanted to store before you so much as put finger to keyboard. FileMaker and Access simplify this process with predefined templates that can be easily customised. For test purposes, we tried to set up a standard name and address database, one of the most common applications.

The approach of both products is initially similar: start a new database and select one of the predefined templates. FileMaker offers a total of 16 predefined templates, with an equal mix of home user options such as collection lists and recipe folders, and business choices like personnel records and expense reports. Once you have selected the template, you click on New and then begin adding records. The business card template contained all the fields we needed, and it was relatively easy to remove those we didn't want (by selecting the Define Fields option from the File menu) or add customised local options for fields such as state names.

Access has a slightly smaller set of predefined templates (10 in all), and a more obvious bias towards business users. Templates aimed at consumers are freely available from the Office Web site, which means there's a wide range of potential choices, but they're not as easily accessible.

The contact management template we selected produced a fully fledged application (as opposed to FileMaker's simpler set of cards). This is useful if the template matches your needs, but it is trickier to customise. For an application such as a simple mailing list, new users will be better off selecting a blank database and then using the Table Wizard to add the relevant fields from one of 25 sample tables.

The Database Wizard requires you to select which fields you want before beginning data entry, and it doesn't give you the immediate option of deleting fields you don't need. You can choose from a variety of background layouts, and the template automatically creates a front end for the database which allows data entry, report generation and other activities.

While both products make relatively easy work of setting up a new database, Access still makes fewer concessions to the new user, quickly requiring you to grapple with design views if you want to make even minor changes. The flexibility this offers is welcome when developing full-scale applications, but a little intimidating for the beginner. FileMaker has fewer complications, but beginners shouldn't have too much trouble with Access either.

Access 7 FileMaker 8

Importing data

Very few databases are created entirely from scratch. More often than not, the data that needs to be stored in them is already accessible in another, sometimes less orderly digital form. The ability to import that data quickly and intelligently without numerous manual corrections is a critical test for any database. The data sources we used to test the import capabilities of each product included an Excel spreadsheet that contained information on 482 companies, a similarly formatted text file, and an email address book file from Outlook, exported in CSV form.

Both products enable you to customise data-importing operations so that only specific sets of information within the source data are used. This is a slightly fiddlier option in FileMaker, which uses drag and drop to reorder required fields. Access gives you a preview in table form, but the entire option to customise imports is easy to miss if you click the Finish button prematurely.

Although each system can make intelligent guesses as to the format of imported data, there are definite limits to such intelligence. For instance, both products can use the first row of data in a spreadsheet to map field names, but neither can manage this trick if such information is on any other line. FileMaker also has a handy option which allows you to import named ranges from within Excel, while Access supports the full range of cross-app copying and pasting options offered in Office XP.

On smaller data importing operations, Access and FileMaker both performed quickly (faster than we were able to time with a stopwatch in some instances). However, as the size of the file to be imported increased, Access demonstrated a definite advantage. For instance, importing the data from our 3.6M Excel test file took around 10 seconds in Access and 50 seconds in FileMaker.

We expected Access to perform more strongly in this particular test, given the expected levels of integration between different Office products, but the same phenomenon could be seen with a similar-sized text file. Access imported this in five seconds, but FileMaker took 30 seconds.

If you are expecting to import data regularly from other sources, these time differences could quickly pile up. That aside, there's relatively little difference between the two products in this area.

Access 8 FileMaker 7

Queries and reports

Once a database has been set up, there are two main uses to which it will be put: searching for individual data; and producing reports that summarise the data. There's very little separating our two competitors in these areas.

The most traditional measure of database performance is query speed: how quickly individual items of data can be located. While this remains a highly relevant measure for high-end databases, its application to desktop products has receded rapidly. The reason? Unless your database is enormous, most queries will be completed almost instantaneously.

Even on our test PC, which had just 64M of RAM and a relatively non-beefy 600MHz Celeron processor, we found few situations where searches weren't complete before we'd had time to remove our hands from the keyboard. Access appeared to be slightly faster than FileMaker in some cases, but not significantly so.

Access does offer more options for searching and filtering records. Both have the conventional filter-by-form option (you specify the content of one or more fields and the database returns all the documents that match), but Access also allows you to select all records which match the currently selected field automatically, with a single click. This is much easier for simple queries.

Report generation (formatting the whole database, or a selection of it, for printing) is equally well handled in both products. Databases which have been built using existing templates generally have a selection of reports already prepared. For instance, FileMaker's business cards template includes a preformatted report for printing cards, while Access's contact management template includes a weekly call summary.

Setting up new reports is also well handled. Access offers a report wizard that will generate the basics for you, although to make any real impact on the output you need to access the somewhat less intuitive design view, which would do well with an adopted layout idea or two from Word.

FileMaker's report wizard is friendlier than that used in Access. Where the former offers you a range of functional styles such as envelopes and labels to choose from, the latter restricts you to choosing basic layout types. Once the reports are created, the editing environment is similar to that of Access.

Access 8 FileMaker 8

Mail merging

Mass-producing letters for marketing and other purposes has always been an important application of the database. Mail merging has usually meant creating printed material such as letters or invoices, but in recent years, businesses have started to use mass customised emails as well.

Microsoft has an obvious advantage with this kind of application, since Word is already bundled with Access. Creating a mail-merged letter or email requires nothing more than activating the Mail Merge Wizard from within Word and selecting the appropriate option. The Task Pane (one of the key additions to Office XP) is put to particularly good use by this wizard, making what has always been a tricky task for many users about as straightforward as possible. The wizard takes you through a series of logical options, and you can backtrack and make changes at any time (not always easy in earlier versions of Office).

Word can import native Access data automatically , or you can choose a selection of merge data for export within Access. You can also restrict the fields available for inclusion in mail merge documents once the process begins, and there's a fast and flexible preview option.

Merging is clearly a straightforward activity using Access, but it's worth noting that it's Word, not Access, that is doing virtually all the grunt work. Although Word doesn't support directly importing FileMaker files, converting them into a format that it will accept is a straightforward process. Once that's done, there's no functional difference at all between a Word mail merge that uses an Access file and one that uses FileMaker data.

FileMaker users who don't have Word can perform mail merges directly from within FileMaker by creating a customised layout and inserting merge fields drawn from the main database. Although this doesn't offer the raft of document production and layout features that Word has, and doesn't include any direct email merging options, it's more than adequate for routine credit or marketing documents. Producing mailing labels with FileMaker is also straightforward.

Access is clearly the winner at producing mail merge documents. However, that isn't to say that FileMaker makes the task impossibly difficult.

Access 8 FileMaker 7

Web publishing

Publishing a database online can mean anything from exporting the data in static form by saving it as an HTML page to creating a highly customised, self-contained Web application. The products reviewed can handle either of these extremes, but we were particularly interested in how easy they made it to give secure access to an existing database with a similar interface. (There are further comments on self-contained applications in the 'Extended capabilities' section.)

Within FileMaker, it's particularly easy to allow database sharing. Enabling the Web Companion option in the File Sharing menu makes the database accessible with a browser, provided it is stored on a machine with an IP connection. Typically, this would be a dedicated server, but it is also possible for users on any network connection (including a dialup) to share data in this way.

Security privileges that have been set for the database (including passwords and editing restrictions) will continue to apply. It's possible to customise the interface, but the defaults supplied by FileMaker are more than adequate for most purposes. The standard release restricts Web access to 10 unique IP addresses within a 12-hour period.

Microsoft has worked hard to simplify the process of publishing data online in Access 2002, but it lacks the one-click simplicity of FileMaker. To publish a database, you create a data access page as a new database object. (These pages only work in Internet Explorer 5.0 or later.) This is then saved using either the Web Folders feature of Windows or a standard server upload.

When designing data access pages no defaults are supplied, but there is another wizard to take you through the process of creating them. It's possible to build sophisticated applications using this method, but for users with lesser demands, the process is overly convoluted and you need a working understanding of server naming conventions to ensure that the database is accessible to outside users. Once online, restrictions on user numbers will depend on the server. Should you merely wish to provide static data snapshots, both products also offer a standard HTML save option. Access allows you to use HTML templates to customise the final output, but produces much larger files even if you don't use that option. In our tests, sample HTML pages were almost twice as large, despite no notable differences in the finished output.

Access 7 FileMaker 9

Extended capabilities

Unlike most applications, databases are often designed to be invisible. Extensive scripting controls mean that rather than simply telling users to use standard desktop software to retrieve and enter information, developers can create standalone applications customised for specific divisions or individuals. This helps to shield those users from unnecessary difficulty, and also makes it easier to maintain data security and limit updates to authorised parties.

While developing such applications is unlikely to be an area that database beginners will dabble in, it's useful to know that such extensions are possible. After all, if you don't fancy developing a complete solution yourself, you can turn to one of the many solution developers who work with both products. (There are more developers for Access than FileMaker, but there are plenty of consultants who work with both). Both Access and FileMaker perform well on the extension front; each boasts its own scripting language, can interact with larger enterprise databases, and supports XML as a data exchange medium.

Access often gives the impression that its primary function is to develop standalone applications rather than to be used as an environment in its own right. If that's your aim, Access can well and truly deliver the goods, allowing you to incorporate data from other Office applications as well as database information (from Access and other enterprise environments) into your custom apps. The extensive help documentation provided is thorough and useful, even for beginners. Automation at a simpler level can be accomplished using the built-in macro editor.

FileMaker's options in this area are less extensive, but it's still no slouch. Its ScriptMaker tool enables complete control over the user interface, and is particularly useful in creating custom summaries of data or simplified front ends. The addition of full ODBC access in the more recent versions of the product also makes connecting to enterprise systems a simpler task than in earlier releases.

If you imagine that your database needs will expand, but don't want to expend a lot of time and energy learning how to use the tools you need, FileMaker remains a viable choice. If you want unlimited flexibility, Access is the best option.

Access 9 FileMaker 7

And the winner is?

In a market that has developed to the point where only two competitors remain, neither choice is likely to be appalling. This certainly proved to be true in our database testing. The differences between Access and FileMaker were minimal in many areas, and both could be confidently recommended for most applications.

As our test scores indicate, FileMaker ultimately has the edge, largely because it concentrates on being easy to use, rather than on flexibility. Tedious tasks such as placing databases online or designing reports are made as simple as possible, but this doesn't detract from the product's potential as a powerful database development tool for general use.

Access comes into its own in two areas: users who imagine they'll be developing standalone applications in the near future; and businesses for whom mail merging will be a major part of their database activity. If these aren't your priorities, then the slightly less friendly interface presented by Access (especially in areas such as Web publishing) make it a less compelling choice.