Archive for April, 2008

Use FileMaker Pro 9 to connect to a SQL / MySQL data source

One of the really powerful features of FileMaker Pro 9 is the ability to connect directly to an SQL data source. This means you can create a FileMaker 9 interface for your clients and allow them to add/modify/browse/delete records on a SQL data source.

Setting up FileMaker Pro 9 to connect to a SQL data source is actually a little more complicated than the FileMaker marketing would have you believe. In this short tutorial, we’ll talk you through the steps of connecting to a MySQL data source using FileMaker Pro 9 on a Mac and Actual Technology’s ODBC drivers (yep, you need third party software – surprised?)

There are two steps to setting up your FileMaker to MySQL connection:

1) Set up your ODBC preferences
2) Set up FileMaker Pro 9

1) Set up your ODBC preferences

The first thing to do here is to go to Actual Technologies and get a copy of the ODBC driver you require. (NB. Metaclarity is not affiliated with Actual Technologies in any way – we just like their software). We’re running MySQL so we bought a copy of the Open Source Databases driver. If you don’t want to spend money on a driver there are some free drivers available on the internet, but for $29 Actual will make your task a LOT easier.

  • Run the Actual ODBC Driver Pack installer
  • Go through the legal steps then click customize and choose the driver you want to install: we only installed the “Actual ODBC Driver for Open Source Databases” as that was all we required to connect to MySQL
  • With the installation finished successfully go to the Utilities folder on your drive and open the application called: “ODBC Administrator”
  • Click on User DSN then click on the “Add… button”
  • Select Actual’s Driver in the list that appears.
  • Press “Continue” in the new pop-up window.
  • Choose an easy to remember name for your connection and description – these are not critical to the connection. For DSN type, choose System.
  • Choose your database type (here we chose MySQL)
  • Enter the IP for your MySQL server in the Server field. Port is usually 3306.
  • Set your username and password for connecting to your online MySQL database in Login ID and Password.
  • In the next window the name of your database should be available in the Database popup field. Select it now then press continue. Then press “Done”

Now in the ODBC Administrator application under the System DSN tab you’ll see your data source and you’re ready to connect with FileMaker Pro 9

2) Set up FileMaker Pro 9 to connect to SQL / MySQL

  • Launch FileMaker Pro 9
  • Create a new empty database.
  • Open the define database dialog: File>Manage>Database…
  • Go to Relationships and double click on the existing “test” table
  • For Data Source, choose Add ODBC data source….
  • Choose your MySQL source from the list and press “Continue…”
  • Most of the defaults in the following screen are fine though if you don’t want to be prompted for a password each time you connect then use “Specify user name and password”
  • Now choose the table you want to connect to and read. Your table will populate with the fields from this remote table.
  • Press OK and watch what happens – you’re using FileMaker Pro 9 to access a remote SQL data source! You can now use FileMaker’s layout functions etc. to make this layout more attractive and get to work on learning how to better integrate Filemaker Pro 9 and MySQL!