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:
- 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.
- In FM, make all field names, which must be unique, eight characters or
fewer.
- Select all records.
- Export FM db as *.dbf.
- 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.
- 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.
- 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.
- 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.)
- 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.
- Repeat steps 2-9 for each (quasi-) related FM db.
- 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.