how to import microsoft access databases into fmpro … · overview - importing access database...

How to Import MicrosoftAccess Databases into FmPro

Migrator

1 Overview

1.1 Overview - Importing Access Database Files 4

2 Step 1

2.1 Step 1 - Get Info - Access 6

3 Importing Forms/Reports, Value Lists & Visual Basic Code

3.1 AccessDDRExport - Extracting Info From Access 14

3.2 Using Demo Mode - Access to FmPro 23

3.3 Using Licensed Mode - Access to FmPro 27

3.4 Manual Tasks - Access to FmPro 34

Overview

How to Import Microsoft Access Databases into FmPro Migrator - 3

Overview - Importing Access Database Files

This document provides an explanation of the steps required to import a Microsoft Access databaseinto FmPro Migrator Developer Edition. This migration process includes the import of Tables/Fields,Relationships, Value Lists, Forms/Reports and Visual Basic code. Once the metadata has beenimported into FmPro Migrator, the Microsoft Access database can then be converted into any of thedatabase development environments supported by FmPro Migrator.

Revision 0112/14/2010


Step 1


Step 1 - Get Info - Access

FmPro Migrator uses the information within the Access mdb/accdb file to perform the conversion oftables/fields, relationships and data from Microsoft Access. FmPro Migrator can be running on MacOSX (10.3, 10.4, 10.5) or Windows in order to perform the conversion. It is not necessary to have a copyof Access running on the computer which is being used to perform the data conversion, but the AccessDDR Export utility does need to run on a computer or virtual machine where Microsoft Access isinstalled.When using FmPro Migrator on MacOSX, the Actual Technologies ODBC Driver for Access is required.

Step 1 - Click FileMaker Tab

Launch FmPro Migrator, then click on the FileMaker tab at the top of the window.


http://www.actualtechnologies.com

Step 1 - Get Info - Select Output Directory

FmPro Migrator stores migration process information within a SQLite database file namedMigrationProcess.db3. This file contains the metadata for the source Access database file, includingtables, fields, table creation SQL code and status info. Therefore the first step in the migration processis to click the Browse button to select the output folder FmPro Migrator will use when creating theMigrationProcess.db3 file.

(1) Select the output directory, then (2) select Access from the Source Database menu.

Note: It is not necessary to enter an ODBC DSN for the Access database because FmPro Migratorautomatically creates a File DSN to read from the Access database.


Continue Migration...

Tip: To restart an existing migration project, select the output directory, then select ContinueMigration... from the File menu.Click the yellow Continue button to open the Migration Process window.

Step 1 - Get Info - Drag & Drop Access File

Drag & Drop the Access file onto the Step 1 Get Info icon.

Step 1 - Get Info - Troubleshooting

Note: FmPro Migrator makes a file DSN connection to read the structure info from the Accessdatabase file. On MacOSX, this process requires the Actual ODBC Driver for Microsoft Access fromActual Technologies (www.actualtechnologies.com).

On Windows, the Microsoft Access ODBC driver is pre-installed when Windows is installed.


www.actualtechnologies.com

Step 1 - Get Relationships - Troubleshooting - Windows

On Windows, it is necessary to enable Read-Only access to the MsysRelationships table in order toread the relationships from the Access database file.

Step 1 - Get Relationships - Troubleshooting - Windows - Tools -> Security menu -Access 2000/2003

Using Microsoft Access 2000/2003 - open the .mdb file, then select the Tools -> Security -> User andGroup Permissions menu.


Step 1 - Get Relationships - Troubleshooting - User and Group Permissions - Access2000/2003

(1) Select "Table" as the Object Type, (2) select MSysRelationships from the list of Object Names, (3)check the Read Design and Read Data Permissions, (5) click the Apply and (6) OK buttons.

Drag & Drop the Access .mdb file onto the FmPro Migrator Step 1 button again, and the relationshipswill be gathered along with the Table and Field info.


Step 1 - Get Relationships - Troubleshooting - Access 2007

Microsoft Technical Support has confirmed that it is not possible to read info from theMSysRelationships table via an ODBC connection with Microsoft Access 2007 .accdb database files onWindows. This feature was removed from Access 2007 due to the removal of the User and GroupPermissions feature.

There are two work-arounds for this problem:1) Convert the Access 2007 .accdb file into an Access 2000/2003 .mdb database file. This feature isbuilt into Microsoft Access 2007. Open the .mdb file with Microsoft Access 2000/2003 to change theUser and Group Permissions and then drag and drop the file again onto FmPro Migrator.2) Perform the task of gathering info on a computer running MacOS X, using the Actual Technologies Access ODBC driver. The Actual Technologies Access ODBC driver has full access to read informationfrom the MSysRelationships table within .mdb/.accdb database files. No User and Group permissionsneed to be modified when using the Actual Technologies Access ODBC driver on MacOS X.


http://www.actualtechnologies.com

Step 1 - Get Info - Click Continue... Button

After the Gathering Info... dialog disappears, click the yellow Continue... button.


Importing Forms/Reports, ValueLists & Visual Basic Code


AccessDDRExport - Extracting Info From Access

FmPro Migrator Developer Edition includes features for migrating Microsoft Access Forms/Reports,Queries, Relationships, Value Lists and Visual Basic code.These features use the Access to FmPro Migration Service feature on the GUI tab of FmPro MigratorDeveloper Edition.

Access to FmPro Migration Service button

Click the Access to FmPro Migration Service button on the GUI tab within FmPro Migrator DeveloperEdition.


Access to FmPro Migration Service Window

Clicking the Access to FmPro Migration Service button opens the Access to FmPro Migration Servicewindow.As this window is opened, the AccessDDRExport.zip file is written into the output directory.

Note: Even though this feature is named "Access to FmPro Migration Service", it is not necessary tomigrate the database objects into FileMaker Pro. The metadata imported into FmPro Migrator is storedin a FileMaker Pro compatible XML format, but this info can be converted by FmPro Migrator for usewith other development environments.


AccessDDRExport.zip Contents

Extract the files from this zip archive.

The files within this zip archive include:AccessDDRExport??.accde - The Access 2007 DDR Export database file.AccessDDRExport??.mde - The Access 2000/2003 DDR Export database file.ijl15.dll - A dll file which is used by the Access DDR Export database for retrieving the images fromMicrosoft Access Forms/Reports.

Overview - Using the AccessDDRExport.accde/.mde Database Files

The process for extracting Forms/Reports, Queries, Value Lists and Visual Basic code from Accessdatabase files requires running one of the two Microsoft Access Export database files on a computer orvirtual machine running Windows. These database files cannot be used with CodeWeavers CrossOverMac. They must be run under a full copy of Windows with a full copy of Microsoft Access.The AccessDDRExport.accde/mde database creates a database design report text file within atop-level folder having the same name as the selected Microsoft Access database file. Imagesembedded within the Forms/Reports are exported into an Images folder within the top-level folder.Once the database design report and images have been extracted from the Access database file, thetop-level folder and its contents may be copied between computers and used on either MacOS X orWindows to complete the migration process.


AccessDDRExport - Installing the JPEG DLL on Windows

Before running the AccessDDRExport.accde/mde database files, the ijl15.dll needs to be installedwithin the C:\WINDOWS\System32 folder. This dll is used for extracting the embedded images from Microsoft Access Forms/Reports. If this file isnot installed, the rest of the database design report creation process will work Ok, but the images won'tbe processed.

After the ijl15.dll file has been installed, re-launch the AccessDDRExport.accde/mde database.

AccessDDRExport - Exporting the Database Design Report

Launch the AccessDDRExport.accde/mde database file. Click the Select Database button.


AccessDDRExport - Select Access Database

(1) Select the type of Access database file to be opened (accdb/mdb), (2) select the database file, thenclick the Open button.

AccessDDRExport - Processing Status

The selected Access database will be opened, and the processing status will be displayed in the lowerleft corner of the AccessDDRExport window. At the completion of the export process, the selecteddatabase file will be closed and the cursor will return to the arrow cursor and the status area text willchange to "Processing Completed".


AccessDDRExport - Results

The AccessDDRExport text file and the embedded Form/Report images will be extracted into a folderwithin the same directory as the original Access database file. This folder can be copied to anothercomputer if necessary for further processing by FmPro Migrator Developer Edition.


Access to FmPro Migration - Select AccessDDR Text File

Click the Browse button to select the AccessDDR text file.


Access to FmPro Migration - Forms/Reports Count

FmPro Migrator will read the Access DDR text file, validate the format of the file and display the countof Forms and Reports within the file.


Using Demo Mode - Access to FmPro

FmPro Migrator includes a Demo mode for the Access to FmPro Migration Service. Demo modeenables FileMaker developers to fully test the migration capabilities of the migration service with alimited number of layouts and scripts. Demo mode also enables FileMaker developers to quickly create "Proof of Concept" conversionprojects for prospective clients who need to migrate their existing Access database files.

Click Migrate Button

Click the Migrate button to convert the AccessDDR text info into FileMaker Layouts, Value Lists andScripts. A progress dialog will be displayed during the processing of the file.


Migration Statistics

After the migration has been completed, the processing statistics will be displayed above the Migratebutton. FmPro Migrator will typically process layouts in less than one second per layout. As seen in thisscreenshot the layouts, scripts and queries were processed in less than 2 seconds. Value Lists will also be created from the Form/Report objects imported into FmPro Migrator. TheseValue Lists will be visible under the Value Lists tab of the Migration Process window after thecompletion of processing.


Review Exported Visual Basic Files

The exported Visual Basic code has been converted into FileMaker Scripts formatted for pasting intothe FileMaker scripts window. This code has also been saved as individual text files within theAccessDDR Info directory.


Visual Basic Code Details

Each Visual Basic Subroutine or Function is extracted into a separate text file and correspondingFileMaker script (1) including the name of the original code module, (2) global variables for theenclosing code module, (3) followed by the actual text of the Subroutine or Function.

Troubleshooting - Visual Basic Code

If the AccessDDRExport database reports that 0 Visual Basic modules were found within the file, verifythat the Visual Basic code was compiled within the Access database. Uncompiled Visual Basic codewill cause the number of code modules to be reported as 0.


Using Licensed Mode - Access to FmPro

Using the Access to FmPro Migration Service in Demo mode, limits processing tasks to 5Forms/Reports and 5 Visual Basic scripts. Ordering a license key lifts these operating limits for thisservice. Using the License Key allows for the processing of an unlimited number of database files andscripts for the purchased Forms/Reports quantity during the duration of the license key.

Note: FmPro Migrator Platinum Edition includes a bundled license key for the Access to FmProMigration Service. FmPro Migrator Developer Edition can be upgraded to FmPro Migrator PlatinumEdition for the difference in price between the two products.

Select Processing Type - Licensed

(1) Select Licensed from the Processing Type menu. (2) Click the Order License Key button. FmPro Migrator will open the .com Solutions Inc. web storehosted by Kagi. The specified Forms/Reports quantity (3) and Access to FmPro service will


automatically be added to the Kagi shopping cart.

Obtaining a Price Quote

The Quote button links to a web form which creates a printable price quote & cost justificationdocument based upon the number of Forms/Reports imported into FmPro Migrator Developer Edition.This document is designed to be suitable for project budget planning purposes and review by corporatefinance departments.


Entering a License Key

Once the payment processing has been completed on the Kagi web store, an email receipt containingthe License Key for the Access to FmPro Migration Service will automatically be sent within a fewminutes. Copy the license key within the email receipt, then click the clipboard icon.


Validating the License Key

FmPro Migrator copies the license key from the clipboard, validates the key with the license key servervia the internet and then displays the Forms/Reports quantity and expiration date for the license key.


Migration Statistics - Licensed Mode

(1) Clicking the Migrate button with the license key enables the processing of the number ofForms/Reports as specified in the license key and the conversion of an unlimited number of VisualBasic scripts, with the (2) resulting processing statistics displayed below the license key field. ValueLists will also be created from the Form/Report objects imported into FmPro Migrator. These ValueLists will be visible under the Value Lists tab of the Migration Process window after the completion ofprocessing.


Review Exported Visual Basic Files

The exported Visual Basic code has been converted into FileMaker Scripts formatted for pasting intothe FileMaker scripts window. This code has also been saved as individual text files within theAccessDDR Info directory.


Visual Basic Code Details

Each Visual Basic Subroutine or Function is extracted into a separate text file and correspondingFileMaker script (1) including the name of the original code module, (2) global variables for theenclosing code module, (3) followed by the actual text of the Subroutine or Function.

Troubleshooting - Visual Basic Code

If the AccessDDRExport database reports that 0 Visual Basic modules were found within the file, verifythat the Visual Basic code was compiled within the Access database. Uncompiled Visual Basic codewill cause the number of code modules to be reported as 0.


Manual Tasks - Access to FmPro

There are some processing steps which must be completed manually when performing an Access toFileMaker Migration.

Layout Parts - Header, Footer, Subsummary

Microsoft Access incorporates Form/Report part objects with similar functionality to FileMaker layoutparts. However there is not any method available to specify the inclusion of layout parts or theplacement of objects within parts when pasting FileMaker layouts via the clipboard. These layout partswill need to be added manually.

Unsupported Form/Report Objects

Some objects implemented on Microsoft Access Form/Reports do not have equivalent features within aFileMaker database:

acToggleButton - FmPro Migrator creates these objects as regular buttons on the layout.acBoundObjectFrame - These are created as regular field objects, using the table/field specifiedwithin the original object.acObjectFrame - These are created as regular field objects, using the table/field specified within theoriginal object.chart - There is no equivalent feature built directly into FileMaker Pro. This feature can be implementedusing a variety of plug-ins (i.e. xmChart) or add-ons which can use the WebViewer to display renderedJavaScript code (i.e. FusionCharts for FileMaker). acCustomControl - An acCustomControl can represent any compiled ActiveX dll installed for use withMicrosoft Access. These types of features can usually be replaced with FileMaker plug-ins availablefrom a variety of vendors.

Relationship Testing

Within Access databases, there are no TOs within the database structure. All Forms/Reports and fieldsare tied directly to a base table, SQL Query or view within the database. When creating the Accessrelationships within a FileMaker database, additional TOs will often be created automatically. It will benecessary to review the additional TOs to verify whether the base table or the new TOs should beused.

Portal Field Sizes

FmPro Migrator automatically creates a new portal to represent any SubForms/SubReports found onthe original Access Form/Report. Unlike FileMaker portal objects, Access SubForm/SubReport objectscontain horizontal scrollbars. Therefore there could be too many fields incorporated on the original


http://www.x2max.com

http://www.fusioncharts.com/

SubForm/SubReport to make a readable display within the area of the new FileMaker portal. FmProMigrator divides the width of the portal by the number of fields and creates each field of the same size.The fields are also created in the same left to right display order as they were created on the originalSubForm/SubReport. Therefore some objects surrounding the portal may need to be moved and theportal may need to be enlarged to support a readable display of large numbers of fields. It may also behelpful to manually resize the individual portal fields to accommodate the actual data which will bedisplayed within the portal fields.

Visual Basic Code

All of the Visual Basic code modules read from the original Access database are converted intoindividual scripts within the FileMaker database. Each line of these FileMaker scripts consists of a lineof commented code, allowing the original Visual Basic code to serve as a template for the writing ofnew FileMaker script functionality. Many Visual Basic commands can be deleted, because theyincorporate functionality which is not needed or doesn't exist within a FileMaker database. Othercommands will need to be carefully examined in order to replicate the original program logic asfunctional FileMaker script instructions.

Button Linking

Buttons on the original Access forms will generally be linked to form event code, which may make callsto Subroutines/Functions within the Visual Basic modules. Since the form event code is not extractedfrom the Access database, this functionality will need to be rewritten using FileMaker scripts. Many ofthe commonly used button functions can be linked directly to single-line FileMaker script instructions,which will not require any additional scripts to be developed.


Query Conversion & Multi-Table Joins

Access Queries are converted into TOs on the RelationshipGraph and are also converted intoFilemaker scripts. The entire SQL code of the original Access query is available for review. FileMaker TOs, can only reference a single table, so to implement a multi-table join as shown in thisSQL code, additional TOs and corresponding relationships would need to be manually added to theFileMaker RelationshipGraph.


Unbound Calculated Form Fields

Access Form/Report fields can be implemented as calculation fields which are not directly referenced toa a field in a database table. These fields can potentially be created as new calculated fields within arelated table in the converted FileMaker database file. In some cases, a centrally located global fieldmay be a more appropriate implementation.

Overlapping Layout Objects

There isn't any method to determine the top to bottom stacking order of objects on an AccessForm/Report. FmPro Migrator builds objects within the new FileMaker Layout in a specific logical orderto minimize issues with overlapping objects. The order of creating layout objects is:RectangleLineGraphic ImageText LabelField

Some cosmetic changes will potentially still need to be performed manually to fine tune the stackingorder.


how to import microsoft access databases into fmpro … · overview - importing access database...

Documents