David Simpson, part 2

Part 2: Northwind Database conversion

Microsoft uses a database called ‘Northwind’ that is a standard demo that is perhaps the most famous in the Access world. It is built by MS to show off the product, and is more complex, though still a pretty simple template by FileMaker standards. Maybe it’s about as complex as the sample files provided with FileMaker, but not in the league of the Business Tracker template from Excelisys.

David prepared two conversions of the file. One was a straight conversion with no modification of the text file to fix errors on the conversion. This file has a few errors in the relationships, portals, some missing fields, and other small errors that would need to be addressed in FileMaker.

There are many little details that need to be addressed to get a really good conversion. Little things like dealing with queries for portals, file naming conventions (using underscores in field names, etc) dealing with boolean and other data storage conventions (false = 0) These things all may take about 2 hours for an experienced developer on a system the size of Northwind.

There’s no easy way to troubleshoot the more complex queries used in Access upon conversion. The queries are stored in FileMaker in a comment, so you can still read them. David uses a tool to run the queries in an external tool called Navicat (cross platform) that can help with the queries. This tool tells you which field or part of the code is broken, which FileMaker does not tell you.

Once Navicat fixes the query code, you can copy and paste this back in to the conversion tool, and it will execute the updated code so you can test it.
He also has a 2nd version of the converted file with many of these fixes made in the Migrator program, where it’s perhaps more efficient to fix. This way, the same conversion error is not repeated and has to be fixed only once in Migrator. This uses the queries run though Navicat, plus font changes, and other mass changes done to the objects being converted.

Overall, this shows that there’s great deal of variability when you convert different Access databases into FileMaker.

Queries are brought into FileMaker as scripts that use the DoSQL plug-in, which is really cool. These are pretty easy to read and work with, though you might want to use different methods to get the same data in FileMaker, like a relationship or Enter Find Mode, Set Field, Perform find, and return the set of records as IDs, or whatever method you’re using now

In general, Access databases created by basic users that use the simpler functions and don’t write lots of queries and VB code will convert into FileMaker beautifully. Solutions created by professional Access developers may have queries behind every field on every layout, which would be very time intensive to convert. These may still be very worthwhile to convert in an automated way, but will just require more planning and work in the middle stage, and several test conversions. Also, David offers consulting services to do the conversion, since he obviously has great expertise with the tools, and has worked with many types of databases.