FileMaker Pro as ODBC Data Source - File Maker

We’ll use the same “Darn Good Security System” solution to demonstrate a real world example of how you might want to access FileMaker Pro as a data source. The scenario is that a database administrator wants to pull data from the Log.fp5 database in order to create meaningful charts and graphs of the records entered in the log.

Enabling the Data Access Companions
The first step in getting the log database ready for access by Microsoft Excel (where we’ll create our chart) is to turn on sharing via the Remote or Local Data Access Companion FileMaker Pro plug-ins. The only difference between these two plug-ins is that the Local Companion is designed for accessing databases that reside on the same workstation as the client application (in this case, Excel), while the Remote Companion is used when the database system is on a different workstation. In this case, to make things simple, you’ll use the Local Data Access Companion.

Now launch the LaunchDev.fp5 database (with the developer launcher database in order to bypass the iron curtain of SecureFM’s security features) and log in to the system. (The user name and password are -ckubica- and -wwww1111-, respectively.) Once you’re all the way in, switch to Log.fp5 using the Window menu, then go to File > Sharing. In the Companion Sharing area, check the Local Data Access Companion checkbox and then click OK. The Companion is set up. Easy, huh?

Configuring the ODBC Control Panel
Now you need to set up the ODBC control panel to allow access to the Log.fp5 data from any ODBC-compliant database. Depending on your platform, your ODBC control panel will be located here:

  • In Windows 95/98/NT, go to Start menu > Settings > Control Panel. Double-click the ODBC Control Panel (usually called “ODBC Data Sources (32 bit)”).
  • In Windows 2000/ME/XP, go to Start menu > Settings > Control Panel. Double-click Administrative tools and then single or double-click Data Sources (ODBC).
  • In Mac OS Classic, go to Apple menu > Control Panels > ODBC Setup PPC (or ODBC Datasource Administrator).
  • In Mac OS X, go to /Applications/Utilities/ODBC Administrator.

In any case, you’ll get a dialog like this:

Enabling the Data Access Companions

This is the User DSN (Data Source Name) tab, which shows all data sources that are set up on your computer for the currently logged in user. The System DSN tab shows DSN’s setup for anyone who logs in to the current computer. File DSNs are DSNs that are shareable by users of the computer who have the same drivers installed. Check the Drivers tab to make sure that your FileMaker Pro ODBC driver is installed.

Back on the User DSN tab, press the Add button as you’re going to add the Log.fp5 database as a data source. Now you’ll see this “Create New Data Source” dialog.

“Create New Data Source”

Select FileMaker Pro and click Finish. You’ll get the ODBC FileMaker Pro Driver Setup dialog:

File Maker Proas ODBC Data Source

Enter a meaningful Data Source Name (something users who need this access will recognize when seeing it) and a Description, if you like. Next, if you’re using the Remote Data Access Companion plug-in instead of the Local Companion, check the Use Remote Connection box and enter the IP address (or host name) of the computer running FileMaker Pro or serving the data via the Remote Companion plug-in.

Take a look at the Advanced tab, even though you’re not going to make any changes here for this example. Here’s what these advanced settings do:

  • Max Text Length: The maximum length of a field in an individual record that is shared via ODBC. 64,000 characters is the max. The lower this number, the better the performance you’ll see.
  • File Open Cache: The maximum number of FileMaker databases that a user is allowed to keep open via their ODBC connection at one time.
  • Fetch Chunk Size: The number of records that are sent to the client application per fetch request when the ODBC driver scrolls through a set of records.
  • International Sort: The sort order that records show up in when you use the ORDER BY SQL clause. If checked, your OS’s international sort order will be used, otherwise records will sort in ASCII order.
  • Number, Time, and Date as Text: Numbers, times, and dates are treated as text instead of as numeric data. This is useful when checked because it saves all data in a field even if the data doesn’t match the field type.
  • Applications Using Threads: Lets the ODBC driver function with multithreaded applications (ones like FileMaker Server that can handle more than one request at a time).
  • Translate button: Translates data from one character set to another. Leave these settings alone for now. Just click OK twice to get out of your ODBC control panel.

Fire Up Microsoft Excel
This example uses Microsoft Excel 2000 for Windows 2000, but you could try another application or database system that you have handy and are familiar with.

  1. Launch Excel and create a new workbook. Next, go to the Data Menu, select “Get External Data” and select “New Database Query”. You’ll see a dialog like this:
  2. Fire Up Microsoft Excel

  3. If FileMaker Pro isn’t listed here yet, click “<New Data Source>” and then OK.
  4. Next you’ll get a dialog asking you to name the data source. Go ahead and call it something like “Matter Inc Log.fp5”. Pick the FileMaker Pro ODBC driver in the second box then click the Connect button.
  5. If everything is set up properly so far, the next box will populate with a list of FileMaker databases that are available via ODBC. Choose “Log”. If you want, check the box that will save your user name and password with the connection, then click OK. FileMaker Pro is added to the list of available data sources.
  6. Select FileMaker Pro and click OK. You’ll see this Query Wizard dialog (this is a great little dialog as it doesn’t require you to learn SQL per se):
  7. File Maker Proas ODBC Data Source

    In the upper left is a list of all available databases. Clicking the plus button shows you all fields in that database. If you click on a field and click the Preview Now button, you’ll see some sample data from that field.
  8. The next thing to do is pick which fields you want to add to your query of the Log.fp5 database. To make it simple, highlight the “Log” database name and click the little “>” arrow, which will move all of Log.fp5’s fields into the query
  9. (The “<” and “<<” will move the selected field or all selected fields out of the query, respectively). You can use the up and down arrows to reorder the fields in the query. The Options button brings up some options for how the databases appear in the upper left area of the dialog.
  10. Click Next and you’ll be presented with a dialog that lets you filter the results, which looks like this:
  11. File Maker Proas ODBC Data Source

    Filtering basically means that you are specifying a find request (“Show me only records where the date is earlier than today” or “Show me only records where ‘Giant Meat Cleaver’ is the ProductName”). Set up a basic filter by clicking the Type field at left. The Type field will populate to the right.

  12. In the first drop-down, select “equals” and in the field that opens up next to that, type “Logon” (minus the quotes). While you can add additional filters, too, to refine your results, just click Next for now.
  13. Now you’ll see this Sort Order dialog:
  14. File Maker Proas ODBC Data Source

    Choose “Date Created” and “Ascending” for “Sort by”, then “Time Created” and “Ascending” for “Then by”, as shown in the illustration. Click Next.
  15. Now you’ll get the last dialog where you’re asked whether you want to see the results of the query you just built in Excel, or in Microsoft Query (a query tool where you can tweak and manipulate the query prior to bringing the data into Excel). Your dialog may have other options also (like the ability to save the query for future use).
  16. Select “Return Data to Microsoft Excel” and click Finish. You’ll be asked for a password. Enter one (like “- - - - error”) and click OK. You should see a dialog in Excel that looks like this:
  17. File Maker Proas ODBC Data Source

  18. This dialog asks you want to put the records returned as a result of your query. Choose the default (starting in cell A1) and, unless you want to explore the options available under the Properties menu, click OK. If asked to save the workbook, save it to the desktop if you like.

Now, finally, at long last, there’s data from your Log.fp5 database in an Excel spreadsheet. From here, you can manipulate your data into meaningful, dynamic lists and reports, and even make charts out of it. For example, here’s a pivot chart showing the number of logons by date.

File Maker Proas ODBC Data Source

That’s it for getting data out of FileMaker Pro via ODBC, but here are some last minute pointers and reminders:

  • Always password protect databases that will be sharing data via ODBC so users accessing the FileMaker database can’t do dangerous or disastrous things (intentional or not) to your data.
  • FileMaker Pro, not FileMaker Server, must provide ODBC sharing. Therefore you must always have a workstation dedicated to this service that other people don’t have access to. (Don’t let Milty the mail room clerk play Doom IX on it, for example.)
  • Anyone who wants to connect to a FileMaker Pro database via ODBC must have their ODBC control panel set up as explained here for it to work.

  • Do a careful review of a database’s access privileges before allowing sharing via ODBC. You don’t want anyone to be able to update or delete records carelessly (or on purpose if they’ve just been pink slipped).

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics