Linked Server to FileMaker Pro
Has anyone successfully connected to FileMaker using SQL Server's Linked Server feature? I've tried every possible combination of entries into the Linked Server dialog box, but no success. I know my ODBC System DSN works because I can extract and update data in FileMaker using MS Query (from within Excel).
What's the problem did you get?
I have found the answer. In case you ever have to connect to FileMaker via a Linked Server, here's what you need:
1. The FileMaker file(s) must be opened, and set to Multi-User status and configured to have the Remote Data Access Companion enabled.
2. Set up a System DSN for the FileMaker Pro ODBC driver.
3. In the General tab of the ODBC FileMaker Pro Driver setup, ensure that the "Use Remote Connection" checkbox is checked, and the Server Address field is completed correctly (with either the IP address or the host name).
4. The Linked Server Properties dialog box needs only two entries: Provider Name to be "Microsoft OLE DB Provider for ODBC Drivers" (from the drop-down list), and Data Source to be your FileMaker System DSN as per item 2 above.
It was item 3 above that was causing all my problems. I was running FileMaker on the same (local) machine, and didn't think it would be considered a remote connection.