How to migrate from FileMaker
Pro to Access
Note: Additional information is contained within the <database
name>_instructions.txt file created during the file generation process.
The <database name>_instructions.txt file contains the actual
file names created by FmPro Migrator. This document uses an example
FileMaker Pro database named "example", the file created for
your database will use the name of the FileMaker database you are converting.
1) Fill in the fields within the FileMaker
and Other
tabs of the FmPro Migrator application.
2) Select or create the destination directory for the conversion files
which will be generated.
3) Save the configuration information by selecting Save As from the
File menu.
4) Open one (or multiple) FileMaker database files.
5) Press the Migrate button to generate the conversion scripts and database
documentation files.
6) Examine the example_create_table1.sql file to determine if any changes
need to be made. Transfer this file to the Access server and execute
this code from within the Access query window to create the table which
will contain the FileMaker data. You will need to select View/SQL View
in order to open the SQL query window.
7) Change the name of the FileMaker Pro database to remove any spaces
or special characters, otherwise the SQL query which retrieves data
from FileMaker within the example_fmpro_to_access_xfer_odbc1.pl program
will fail. Then re-generate the conversion scripts.
8) Transfer the example_fmpro_to_access_xfer_odbc1.pl program to a Windows
server. At the present time the example_fmpro_to_access_xfer_odbc1.pl
Perl script needs to be run from a Windows server due to the lack of
an ODBC driver for Mac OS X.
9) Install the Perl DBI and DBD::ODBC modules on the Windows server
along with the FileMaker ODBC Driver. The Access driver is already installed
as part of the Windows installation. (The DBI/DBD::ODBC modules can
be downloaded from www.cpan.org) or installed with Activestate PPM.
Install commands follow:
ppm
PPM> install DBI
PPM> install DBD-ODBC
PPM> quit
10) Create the example_fmpro_odbc_dsn entry in the ODBC
Control Panel. Change the configuration of the Max Text length parameter
from 255 to 65000 in the Advanced tab of the FileMaker DSN.
11) Enable the Local and Remote Data Access Companions within the FileMaker
application preferences dialog. Enable Multi-User access and both the
Local and Remote Data Access Companions by selecting Sharing from the
File menu of each database file. Each database should allow complete
access without requiring a password in order for FmPro Migrator to read
the structure of each database file.
12) Execute the example_fmpro_to_access_xfer_odbc1.pl program to transfer
the data from FileMaker to Access via an ODBC network connection.
--------- Usage Instructions - Image Export Script ---------
[Enterprise Edition feature]
The example_fmpro_image_export1.pl Perl script provides a JPEG image
export feature for FileMaker Pro databases. This script exports one
specified container field as a JPEG file into a user-defined directory
on the computer where this script is running. The name used for writing
the JPEG file is retrieved from a user-specified FileMaker database
field.
1) Fill in the fields of the FileMaker and either the Oracle or Other
tabs of the FmPro Migrator application.
2) Select or create the destination directory for the conversion files
which will be generated.
3) Save the configuration information by selecting Save As from the
File menu.
4) Open the FileMaker database file.
5) Press the Migrate button to generate the migration scripts and database
documentation files.
6) Create the example_fmpro_odbc_dsn ODBC DSN entry in the ODBC Control
Panel. Change the configuration of the Max Text length parameter from
255 to 65000 in the Advanced tab of the FileMaker DSN.
7) Make the following changes within the example_fmpro_image_export1.pl
Perl script in order to specify the name of the container field, filename
field and destination directory.
The following example shows that the container field has been named
"image_field", the filename field has been named "filename_field"
and the output directory is named "my_images" at the top level
of the C: drive. The output directory needs to be specified using \
directory separators on Windows and needs to contain a trailing directory
separator at the end of the directory name.
--------- Example code follows
my $fmpro_image_data_field = 'image_field';
my $fmpro_image_filename_field = 'filename_field';
my $image_output_directory_path = 'c:\\my_images\\';
---------
8) Execute the example_fmpro_image_export1.pl Perl script on a PC running
Windows (or Vitual PC running on a Macintosh) as follows:
perl example_fmpro_image_export1.pl
The JPEG images will be exported from the FileMaker database
and written to the output directory. Repeat steps 7 and 8 as needed
if there are multiple container fields which need exported.
------------------ Usage Notes ------------------
Large Text Fields [STD/EE edition]
FileMaker fields containing more than 255 characters of text will be
converted to Access LongText columns when converted into the Access
database. The list of these column names should be entered into the
Large Text Fields field. Data is transferred between FileMaker and Access
by the example_fmpro_to_access_xfer_odbc1.pl program. This program makes
use of bind variables to specify field names and ODBC field types.
Extra FileMaker Fields
If there are fields within the FileMaker database which don't need to
be transferred to the Access database, these fields should be removed
before generating the conversion scripts. This can be easily accomplished
by making a copy of the FileMaker database, then removing the extra
fields from the copied database file. This technique reduces the chance
of making a mistake by manually editing the files generated by FmPro
Migrator.
ODBC driver and client software installation [STD/EE edition]
The only way to transfer data in large text fields and FileMaker Container
fields is by using the example_fmpro_to_access_xfer_odbc1.pl program.
This program is generated by FmPro Migrator based upon the specified
FileMaker database structure in order to facilitate the transfer of
data to Access.
The example_fmpro_to_access_xfer_odbc1.pl program transfers data for
all FileMaker field types including text fields up to 64000 bytes and
JPEG image data from Container fields. The program makes use of bind
variables to specify field names and ODBC field types. The example_fmpro_to_access_xfer_odbc1.pl
program needs to be installed on a Windows server which has Access ODBC
driver software, Perl, Perl DBI module, Perl DBD::ODBC module, and FileMaker
Pro ODBC driver software installed. (Note: Access ODBC driver software
is installed by default during the Windows installation.) This is due
to the lack of a FileMaker Pro ODBC driver for Mac OS X. The example_fmpro_to_access_xfer_odbc1.pl
script running on the PC can then read the data from FileMaker Pro (hosted
on either Mac OS, Mac OS X or Windows) and then be written into a Access
database running on Windows.
Repeating Fields
There is no data type within an Access database which is equivalent
to the Repeating Fields feature within FileMaker. Data from within repeating
fields is copied correctly to the Access database, however the data
appears within one field. Each repeating field entry is separated by
an ASCII (29) character which is used by FileMaker Pro to separate values
within repeating fields. For full support of this functionality, redesign
of the FileMaker Pro database structure should be considered. This type
of feature would normally be supported via separate rows of data within
an Access database with the child records having a common foreign key
value pointing back to the primary key of the parent record.
Relationships
FileMaker Pro relationships are not automatically traversed and converted
by FmPro Migrator because full pathname info is not available for each
file. The FileMaker Pro developer should manually open each related
file and then FmPro Migrator STD Edition will create conversion scripts
for each open file. Please note that it may not be necessary to manually
open each related file because some files may be opened automatically
in the background by FileMaker. In this case, no additional action is
required. FmPro Migrator Lite edition requires the opening of each file
individually prior to starting the conversion process because only one
file at a time is processed.
Container Fields
FileMaker Pro uses Container fields for storing pictures, sound and
QuickTime video. Data located within FileMaker Pro Container fields
is transferred to Access LongBinary columns. There are some limitations
which have been discovered while retrieving Container field data from
FileMaker Pro. The only Container field data supported by transfer with
FmPro Migrator is JPEG image data. All container fields include the
text "HTTP/1.0" prefixed to the data. This header information
is removed from the data, with the remaining JPEG file data in the field
being transferred into the Access database table. Other types of container
field data are also prefixed with the "HTTP/1.0" file header
information which is followed by a JPEG icon representing the type of
data contained within the field.
FileMaker Pro does not support writing Container field
data into a FileMaker Pro database via an ODBC connection on either
Windows or Macintosh platforms.
Container Field Workaround:
One workaround to the issue of transferring container field data is
to store only a pathname to the data within the FileMaker Pro database.
The example_fmpro_to_access_xfer_odbc1.pl program can be modified to
read the pathname from the FileMaker Pro database, then read the binary
data directly from a local or remotely shared directory. An example
of this type of modification is included in the example_fmpro_to_access_xfer_odbc21.pl
program.
Working with FileMaker Pro on Windows - FmPro Migrator
STD/EE Editions can work with FileMaker Pro databases running on Windows
servers for the data transfer phase of the conversion process. However
a copy of the FileMaker Pro database must be running under Mac OS X
in order for FmPro Migrator to gather the list of fields and field attributes
information. FmPro Migrator cannot retrieve database structure information
from stand-alone applications created for the Windows platform because
Apple Events are not available on Windows. Furthermore, it is not possible
to retrieve data from stand-alone applications via ODBC due to the lack
of networking with stand-alone solution files.
Working with Stand-Alone Solutions Files - FmPro Migrator
STD/EE Editions can read the database structure from stand-alone solutions
files created with FileMaker Pro Developer on Mac OS X. If the developer
of the solution has enabled file exporting, then a tab or comma separated
value export file may be created. However it will not be possible to
extract the information from the file by using an ODBC connection because
networking capability is not available with FileMaker stand-alone files.
The stand-alone file will also need to be the Macintosh version of the
executable in order for Apple Events used by FmPro Migrator to read
the structure of the database file.
Primary Key Determination - FmPro Migrator examines the
structure of the FileMaker Pro database in order to determine which
column should represent the primary key for the Access database table.
The primary key column of the Access database table will be migrated
as primary key column using a compatible datatype derived from the original
FileMaker database. FmPro Migrator selects the first FileMaker Pro field
which is configured with UNIQUE and NOT NULL options as the primary
key. This determination is made by examining each field option in the
field creation order, as is listed on the database structure report.
To insure that FmPro Migrator selects the correct field as the primary
key, it may be necessary to temporarily disable either the UNIQUE or
NOT NULL options for other database fields while generating conversion
scripts.
Access AutoNumber fields - Access databases support a
field type listed as AutoNumber in the table design window. Fields selected
with this feature will be numeric fields of the Long datatype. The AutoNumber
field will start at 1 and increment with each additional record which
is entered. It is not a straightforward process to change the starting
or next increment values for this type of field in Access. The following
Microsoft Knowledge Base article describes the 16 step process which
is required for changing these values: http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q209696&
If an AutoNumber field is needed in Access to serve as
a primary key, it is recommended that this feature implemented independently
within Access instead of being migrated from FileMaker to Access. First,
make sure that none of the FileMaker fields are recognized by FmPro
Migrator as primary key fields. Then use the commands within the example_create_table1.sql
file to create the Access table. (Note: The SQL commands within this
file need to be executed one line at a time.)
Once the Access table has been created, add a new AutoNumber field and
configure it as the primary key. Then import the data from FileMaker
with the example_fmpro_to_access_xfer_odbc1.pl Perl program. The AutoNumber
field will start at 1 and increment as each new record is added to the
table.
