Part 1: demo of migration of simple access database
This month, we’re lucky to have David Simpson, a FileMaker and SQL developer with 23 years experience. He’s showing the FMPro Migrator solution which converts Microsoft Access databases into FileMaker (and much more)
How does it do this? It creates a file from the Access solution similar to what the FileMaker DDR file is. It analyzes all of the objects in the database: tables, relationships, forms, queries, value lists, etc. Some of these objects are stored very different in Access, such as Value Lists. Access does not have the concept of named value lists, so Migrator handles this, creating a de-duplicated list.
It collects the VisualBasic code and converts this to FileMaker scripts, it uses the QuickKeys software on the Mac (and different software on Windows) to create Table Occurrences! It even sets the color for each occurrence on the graph, which is cool. Then it creates the relationships on the graph.
Layouts are created from scratch. The solution converts the objects, including graphics, fields, buttons (that call scripts) colors, lines, fields with value lists and calendar pickers. All looks very faithful to the original in Access (for better or worse… usually the latter)
It’s all really cool to watch it all happen when QuickKeys does it’s magic, vastly faster and more accurate than a human could do, but still kind of a hack. I mean, what I wish for is that FileMaker would allow you to create a layout in HTML or XML or some other text format that allows find-replace. I can see some great ramifications for mass change in FileMaker databases using this method, which is the idea behind FMPro Script Diff. This is another product in the .Com solutions array, using much of the same technology.
When layout objects are pasted, there is a spot where you can substitute fonts and other details in the text file before the XML code is built, put on the clipboard, and pasted in FileMaker. [did you know that when you copy layout objects to FileMaker that it creates XML code, which allows you to copy and paste between versions, between pro and advanced, etc.]
All the data is also brought in, via the ODBC connection. This requires the Actual ODBC drivers on the Mac, which offer some other advantage, despite the overhead of this. The data import works with unicode, and container fields, so it’s much more than a text export and import could achieve.