How to migrate Access databases
to FileMaker 7
Note: Additional information is contained within the <tablename>_instructions.txt
file created during the file generation process. The <tablename>_instructions.txt
file contains the actual file names created by FmPro Migrator. This
document uses an example Access database table named "example",
the files created for your database will use the names of the database
tables in your Access database.
------------- Usage Instructions - Windows - Access to
FileMaker 7 -------------
Note: FmPro Migrator for Windows requires that Perl be installed on
your Windows computer before generating scripts. Activestate Perl may
be downloaded from www.activestate.com. FmPro Migrator for MacOS X utilizes
the version of Perl which was automatically installed with your operating
system.
1) Click the browse button to select a destination directory for the
migration scripts whichh will be created by FmPro Migrator.
2) Select "Access" as the Source Database in FmPro Migrator
(FileMaker 7 will then be automatically selected as the Destination
Database).
3) The FileMaker 7 ODBC DSN entered on the FileMaker tab is passed through
to the created scripts as entered on the FileMaker tab of FmPro Migrator.
Note: The default "Admin" account created for a new FileMaker
database is already configured with ODBC/JDBC access privileges. If
you use another account, insure that ODBC/JDBC access privileges have
been granted to the account.
4) Drag and Drop one or more Access database files onto the rectangular
panel at the left side of the FmPro Migrator window. The Access database
metadata will be read from the dropped files and migration scripts will
be created within the destination directory. Note: If any Access database
table names contain spaces, these should be removed before processing
the file(s).
5) Create a new test database in FileMaker 7, or open an existing database
file.
6) Make sure that the ODBC/JDBC Sharing Companion is enabled for the
FileMaker 7 database. Your migrated Access tables will be created within
this new database.
7) Install the Perl DBI, DBD::ODBC modules on the Windows computer along
with the FileMaker ODBC Driver. (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
8) Run the example_create_table1.sql Perl script to create
the new database table in the FileMaker database.
9) Import the data from the Access database into FileMaker 7 by using
the Import Records feature. From within FileMaker 7, select the menu
item File -> Import Records -> ODBC Data Source ..., then select
the ODBC DSN for the Access database.
------------- Usage Instructions - MacOS X - Access to
FileMaker 7 -------------
1) Click the browse button to select a destination directory for the
migration scripts whichh will be created by FmPro Migrator.
2) Select "Access" as the Source Database in FmPro Migrator
(FileMaker 7 will then be automatically selected as the Destination
Database).
3) Each ODBC DSN entered on the FileMaker tab is passed through to the
created scripts as entered on the FileMaker tab of FmPro Migrator. Note:
The default "Admin" account created for a new FileMaker database
is already configured with ODBC/JDBC access privileges. If you use another
account, insure that ODBC/JDBC access privileges have been granted to
the account.
4) Drag and Drop one or more Access database files onto the rectangular
panel at the left side of the FmPro Migrator window. The Access database
metadata will be read from the dropped files and migration scripts will
be created within the destination directory. Note: If any Access database
table names contain spaces, these should be removed before processing
the file(s).
5) Transfer these migration scripts to a computer running Windows (or
Virtual PC). This step is necessary due to the lack of FileMaker 7 and
Access database ODBC driver software for MacOS X.
6) Use the MacOS X FileMaker 7 installation CD to install FileMaker
7 and the FileMaker 7 ODBC driver on Windows. Both MacOS X and Windows
versions of FileMaker 7 are available on the same install CD. Close
FileMaker 7 if it is running on MacOS X, then enter your FileMaker 7
license key on the Windows installation.
7) Create a new test database in FileMaker 7 on Windows, or open an
existing database file. Enable the ODBC/JDBC Sharing Companion for this
FileMaker database. Your migrated Access tables will be created within
this new database.
8) Install ActiveState Perl, the Perl DBI, and DBD::ODBC modules on
the Windows computer. (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
9) Run the example_create_table1.sql Perl script to create
the new database table in the FileMaker database.
10) Import the data from the Access database into FileMaker 7 by using
the Import Records feature. From within FileMaker 7, select the menu
item File -> Import Records -> ODBC Data Source ..., then select
the ODBC DSN for the Access database.
-------------- Usage Instructions - Image Export Script
--------------
[Enterprise Edition feature]
The example_access_image_export1.pl Perl script provides a JPEG image
export feature for Access databases. This script exports one specified
OLE Object 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 Access database field.
1) Create the access_sample_acs_dsn Access ODBC DSN entry in the Windows
ODBC Control Panel.
2) Fill in the fields of the FileMaker tab of the FmPro Migrator application.
3) Select or create the destination directory for the conversion files
which will be generated.
4) Save the configuration information by selecting Save As from the
File menu.
5) Open the Access database file.
6) Press the Migrate button to generate the migration scripts and database
documentation files.
7) Make the following changes within the example_access_image_export1.pl
Perl script in order to specify the name of the OLE Object 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 two trailing directory
separators 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_access_image_export1.pl Perl script on a PC running
Windows as follows:
perl example_access_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 OLE Object fields which need exported.
----------------------- FileMaker Folder Tab - Field Descriptions
The FileMaker folder tab enables the entry of general info and FileMaker
specific info about the migration process.
Click the Browse button to select an existing folder which
will contain the migration scripts and reports created by FmPro Migrator.
You must have write access to the output directory selected, otherwise
an error dialog will be displayed.
Destination Database ODBC DSN - field - Enter the FileMaker
7 database ODBC DSN within this field. This information is utilized
within the generated migration scripts.
Destination Database - menu - Select FileMaker 7 as the
database which will be the destination for the data.
Destination Database Username - field - The default username
for a new FileMaker 7 database is "Admin", therefore this
default value is automatically entered into this field whenever FileMaker
7 is selected as the destination database. Enter a different username
in this field if a different account is to be used to create and access
data within the FileMaker database. The username entered into this field
needs to have the extended ODBC/JDBC privilege within FileMaker. The
default Admin account is automatically granted this extended privilege
when the FileMaker 7 database is created. This information is utilized
within the generated migration scripts.
Destination Database Password - field - If the FileMaker
database requires a password in order to access the data, enter a password
in this field which allows access to all of the database tables within
the FileMaker database. The default password for the FileMaker 7 "Admin"
account is blank. This information is utilized within the generated
migration scripts.
Large Text Fields - field - Access memo fields are automatically
converted to VARCHAR(1000000) columns within the FileMaker database.
Therefore the Large Text Fields field does not require the entry of
any information when performing Access to FileMaker 7 migrations.
----------------------- Usage Notes -----------------------
Time Fields
When FmPro Migrator uses an ODBC connection to retrieve field type info
from Access, it is not possible to automatically determine whether the
destination field should be created as a date or time field. Therefore
FmPro Migrator assigns the field type as a time field if the text "time"
is contained within the field name. Otherwise the field type is assigned
to be a FileMaker date format field. If a field is mistakenly assigned
to be a time format field by FmPro Migrator, then this automated behavior
can be overridden by simply changing the name of the field in Access
so that the text "time" does not appear within the field name.
Large Text Fields
Access memo fields can contain up to 64,000 characters of information.
FmPro Migrator automatically migrates these fields into FileMaker 7
VARCHAR(1000000) columns. Standard Access TEXT fields are also migrated
to FileMaker 7 VARCHAR(100000) columns due to issues with small VARCHAR
character settings in FileMaker 7.
ODBC driver and client software installation
The best way to transfer data in Access memo and OLE Object fields is
by using the Example_access_to_fmp_xfer_odbc1.pl program. This program
is generated by FmPro Migrator based upon the specified Access table
structure in order to facilitate the transfer of data to FileMaker.
The Example_access_to_fmp_xfer_odbc1.pl program transfers data for all
Access field types including memo fields up to 64000 bytes and image
data from OLE Object fields. The program makes use of bind variables
to specify field names and ODBC field types. The Example_access_to_fmp_xfer_odbc1.pl
program needs to be run on a Windows computer which has Perl, the Perl
DBI module, the Perl DBD::ODBC module, and FileMaker 7 ODBC driver software
installed. The Example_access_to_fmp_xfer_odbc1.pl script will then
read the data from Access and then be written into a FileMaker database
running on the same computer. The FileMaker 7 database needs to be running
on the same computer which is running the Example_access_to_fmp_xfer_odbc1.pl
script if FileMaker Pro 7 is being used. If FileMaker 7 Server is being
used, the database may be located on a different computer and accessed
thru the network.
OLE Object Fields
Access uses OLE Object fields for storing binary data such as pictures.
Data located within Access OLE Object fields is transferred to FileMaker
7 container fields (BLOB column type).
FileMaker Pro 7 vs FileMaker Advanced Server 7 and ODBC
FileMaker Pro 7 enables ODBC/JDBC access only for programs running on
the same computer as the FileMaker Pro 7 database. The driver is configured
with hostname "localhost" or IP address 127.0.0.1 within the
DataDirect SequeLink ODBC driver DSN for a individual FileMaker database.
The default TCP/IP port number used for the DataDirect SequeLink server
is 2399.
FileMaker 7 Advanced Server allows ODBC/JDBC network connections from
other computers on a network, depending upon the security which has
been configured within the database.
Report Differences
FmPro Migrator for Windows generates a slightly different database report
compared to FmPro Migrator for MacOS X. FmPro Migrator for Windows utilizes
a ODBC connection to the FileMaker database since AppleScript is not
available for Windows. The only information available about the database
thru an ODBC connection is:
The Name of the Database
The List of Field Names
The Type of each Field
The Empty Ok attribute for each Field
The following information listed on the report consists
of default values used to fill in the report:
Field IDs (always an incrementing value) Note: Field IDs for Access
databases are accurately read from the Access database file on MacOS
X.
Empty Values (always set to "Empty Ok" for Access files)
Unique Values (always set to Not Unique)
Repeating Values (always set to No)
Global Values (always set to No)
Access Status for each field (always set to read/write)
Protection Status for each field (always set to formulas/protected)
Calculation for each field (always blank)
Script Names (none listed)
Layout Names (only 1 listed - named All Fields, containing all fields)
----------------------- Troubleshooting -----------------------
Error 3201 [DataDirect][ODBC SequeLink driver][SequeLink
Server] Unable to retrieve error message from backend ODBC Driver. This
error may be seen when clicking the "Test Connect" button
in the DataDirect SequeLink for ODBC setup.
Solution: SequeLink error 3201 is documented as an internal error with
the SequeLink Server software built into FileMaker 7. Contact FileMaker
Technical Support for a resolution.
Migrated Access databases display "Empty Ok"
for all fields within the Access97, Access 2000 or Access 2003 database
file.
Solution: FmPro Migrator sets the NULL attribute information to "Empty
Ok" for every field within Access97 database files. This attribute
should then be set manually within FileMaker 7.
Some Access97 files display a non-existent table named
"DataAccess" within the list of tables.
Solution: This problem indicates that the Access97 database file has
been opened within Access 2000. When Access 2000 opens an Access97 file,
it makes changes to the format of the file which cause this issue. Changes
occur within the data files even if the user doesn't specify for the
file to be converted to Access 2000 format. The solution to this problem
is to use Access 2000 to convert and save the database file as either
an Access97 file or an Access 2000 file. Select the menu item Tools->Database
Utilities->Convert Database->To Prior Access Database Version
in order to perform this type of file conversion within Access 2000.
Scripts for duplicate Access tables are overwritten within
the output directory.
Solution: Scripts for duplicate tables will be overwritten in the output
directory. If duplicate table names are observed within the open databases
field, then it is recommended that the Access database files be processed
individually instead of as a group. The duplicated scripts can be renamed
manually between migrations. Alternately, the duplicate tables can be
renamed within the Access database application, and then the entire
group of files can be migrated at once by FmPro Migrator.
When some Access 2003 files are dropped onto FmPro Migrator,
multiple tables are displayed with the same name.
Solution: Access 2003 files utilize a different internal structure for
storing Access database metadata compared to previous versions of Access
(like Access97 and Access 2000). Sometimes, it is necessary to convert
Access 2003 files into either the Access 2000 or Access97 file format
in order to resolve this issue. select the menu item Tools->Database
Utilities->Convert Database->To Access 2000 File Format or
Tools->Database Utilities->Convert Database->To Access 97 File
format
in order to perform this type of file conversion within Access 2003.
When some Access 2003 files are dropped onto FmPro Migrator,
some table names are actually Access Queries, instead of actual table
names.
Solution: Access 2003 queries may appear to FmPro Migrator as if they
are table names. FmPro Migrator attempts to filter out these queries
from the list of tables by removing object names containing " Query"
(that is an object name with a space prior to the word "Query").
Microsoft Access queries are not usable within FileMaker 7 databases
anyhow, so it is best to delete all of the queries from Access 2003
files prior to performing the migration.
[DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC
FileMaker driver][FileMaker]database schema is locked by another user
(303) (SQL-HY000) [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC
FileMaker driver][FileMaker]Query Failed (SQL-HY000)(DBD: st_execute/SQLExecute
err=-1) at example_create_table1.pl line 64.
Solution: This error occurs if the example_create_table1.pl
Perl program is being run to create the FileMaker 7 database table while
the Define Database window is open. Clicking either the Ok or Cancel
buttons will close the Define Database window and prevent this error
from occurring.
[DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker
driver][FileMaker]duplicate name (12) (SQL-HY000) [DataDirect][ODBC
SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Query
Failed (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at example_create_table1.pl
line 64.
Solution: This error occurs if the FileMaker database
table example already exists within either the FileMaker Define Database
Tables window or the Define Database Relationships window. Delete the
example table from both of these locations, and then run the example_create_table1.pl
program again.
Error: Can't call method "execute" on an undefined
value at example_create_table1.pl line 100.
Solution: There are multiple reasons why this error may
occur. One reason is that there is a FileMaker reserved word or illegal
name being used as a column name. FmPro Migrator contains a pre-defined
list of FileMaker SQL reserved words. Column names which are found to
consist of these words are renamed by FmPro Migrator by appending the
"_" character at the end of the column name. If additional
reserved words need to be added to this list, please send an email to
.com Solutions Inc. with your additions so that they may be added to
future revisions of FmPro Migrator.
This error may also occur due to almost any type of SQL syntax error
within the CREATE TABLE SQL statement. Using incorrectly spelled column
types, omitting a comma at the end of each statement line or having
a comma at the end of the very last line of the SQL code will cause
these errors. These types of errors generally occur after manually editing
the CREATE TABLE SQL statements. If the number of edits have been minimal,
it may be easier to regenerate the script again with FmPro Migrator,
then manually re-edit the file.
FileMaker 7 column names created via an ODBC connection to the database
cannot start with a number or an underscore "_" character,
and these conditions will also result in this error being displayed.
