3

I have a client with FileMaker Pro 11 Advanced.

I need to be able to connect to the filemaker database via SQL. I am using MS SQL Server 2008 R2, or Server 2012 can be used.

These are the steps I have tried

0) Turned on ODBC/JDBC sharing in File -> Sharing on FileMaker Pro 11 Advanced for "All Users"

1) installed the FileMaker OBDC driver included on the filemaker disc

2) odbcad32.exe in C:\Windows\SysWOW64 and added a new filemaker System DSN and directed it to the filemaker database.

3) Attempted to add a linked server to SQL using Provider Microsoft OLE DB Provider for OBDC Drivers and the System DSN name i created for Data Source. I left all other fields blank.

I get this error:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "FILEMAKER2".
OLE DB provider "MSDASQL" for linked server "FILEMAKER2" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (Microsoft SQL Server, Error: 7303)

FILEMAKER2 is the what i called the System DSN.

What am I doing wrong/What do I need to correct in order to connect to filemaker with SQL?

Is there a connection string to put in for Provider String?

I have the same mismatch error trying to do a test connect with access.

Thank you for any help.

Kyle
  • 32,731
  • 39
  • 134
  • 184
  • When you add the DSN, did you specify a valid username/password for file access? In order for ODBC to work, the file must be open on the desktop or server. – andyknas Oct 04 '12 at 16:21
  • @andyknas Thanks for the reply. There is no where in the FileMakerDSNConfiguration screen that opens when I add the DSN to add a username/password. – Kyle Oct 04 '12 at 18:30

3 Answers3

3

The error you're getting is because of the driver installed is a 32bit driver propably on a 64bit system. Look in the downloads of FM for the correct xbit driver.

Jojo
  • 31
  • 2
1

I've connected to FM using ODBC from PHP apps to do basic inserts, updates, deletes of data. I haven't worked with it like you're attempting to do, though, and like @ted said, FM really wasn't built as a SQL platform.

Their answer to this was to go the other way around. You can setup External SQL sources within FileMaker, and bind your SQL database to FM so that those tables show up in FM the same as regular FM tables. These tables can then be used within FM scripts accordingly, so you could setup any data sync steps necessary there. You could create scripts to move data from FM layouts into the SQL DB layouts at given intervals throughout the day, for example.

Hope that helps.

Drew Angell
  • 25,968
  • 5
  • 32
  • 51
1

FileMaker 11 only comes with a 32 bit ODBC driver. The error you got happens when you use a 32 bit driver with the 64 bit ODBC admin utility. Set up your DSN using the 32 bit utility, found here:

C:\Windows\SysWOW64\odbcad32.exe

...and it will work fine.

Chris Moyer
  • 21
  • 3