Moving Data from FileMaker to Access

For many people, particularly beginning users of databases, FileMaker has some distinct advantages over Access. For most people, FileMaker is very easy to learn at a basic level, it is very flexible in allowing modifications of basic database design as one works with the database, and, if one has the use of a FileMaker server, it is very easy to put FileMaker databases on the web. However, for many other people, particularly advanced users of databases, Access has some distinct advantages over FileMaker. It has full-featured functionality as a stand-alone product and it allows refined, user-defined web-access through a variety of middleware products like ColdFusion and, quite easily, on a Microsoft IIS server, which is a commonly available type of server. Ideally, at least until one is comfortable with a more advanced database program like Access, one ought to be able to begin a project in FileMaker and later, if and when one wishes, continue it in Access without losing all the work already devoted to populating one's database. Unfortunately, those who begin working with FileMaker as a database program usually come to understand databases in the terms and concepts used by FileMaker, which are not always the standards ideas for true relational databases, such as Access, Oracle, and so on. Therefore, FileMaker users often find themselves confused about how to move their data from FileMaker to Access. This page provides the recipe.

To port databases from FileMaker to Access:

  1. Create a blank, named Access database. N.B.: At each stage, save a working copy. Delete old working copies as subsequent stages are seen to be correct.
  2. In FM, make all field names, which must be unique, eight characters or fewer.
  3. Select all records.
  4. Export FM db as *.dbf.
  5. Import (File | Get External Data | Import) *.dbf to Access db as a table. If one is using (quasi-) related FM dbs, each FM db should become a separate table in the same Access db.
  6. In Access, rename fields for clarity, including a new primary key Autonumber ID field the new name of which should reflect the table name (e.g., ReaderID instead of just ID in the Readers table). Rename the old ID field to tableID_Old.
  7. Y/N fields in FM should be made into Y/N fields in Access. Create a new field and use an Update Query to populate it. Then delete the old field.
    N.B.: In using an Update Query to port text data, the comparison term is LIKE and the crucial term is surrounded by single quotes. * is the wild card character. Thus “LIKE ‘*dog*’” (without the double quotes) can serve as a criterion to update all records where the specified field has content that has the string “dog” anywhere in it.
  8. Radio button and other one-option-only fields in FM should be controlled by a list which is added by using the Lookup Wizard in the field Data Type column in the Table Design View. Create a new field in Access to hold these data. If the list content is stable and small, write a Value List by hand (e.g., AuthorSex); if the list content is changing and/or large, store it in a separate table (e.g., Readers). Use an Update Query to port data from the old field to the new one within Access.
    N.B.: Access Help files discuss using an update query to update data in a field of a single table based on data from that table [e.g., to fill in data based on a formula drawing on the length of time someone has been a customer and the volume of orders given]. However, update queries can also draw their information from more than one table if you join the tables through appropriate fields. See the screen shot of a Reader to AuthReader update example. Note that the Update Query has a join even though there is no relationship between the Readers and author3 tables. Note that the field to be updated is the ReaderID field: even though one sees the data from the Reader field in the author3 table [due to the use of a Lookup to display possible Reader names in the Author table], the data that Access actually stores in the AuthReader field are, for the sake of efficiency, the foreign key which, in this case, is the ReaderID. This is a particularly important procedure for updating the index numbers in an index [a.k.a. primary key] field because Access will not allow you to change a field's datatype to or from Autonumber after any data exist in the field.)
  9. Check box fields in FM should be made into a series of Y/N fields in Access, one field per option. Create clearly named new fields for each check box option. Use an Update Query (see discussion under Y/N fields above) to populate them. Delete old field.
  10. Repeat steps 2-9 for each (quasi-) related FM db.
  11. Use Update Queries to carry new foreign keys into any table where needed. Create new foreign key column. Populate it. (For example, in an Update Query for the Texts table, join Authors.AuthID_Old and Texts.AuthID_Old to update Texts.AuthID to Authors.AuthID.) Do not delete any old foreign key fields before all key updates are successful.