pjfromny
asked on
driver for FileMaker data
I am trying to set up a data link between Excel and a FileMaker Pro database. I am following these instructions from a MS website:
Create a File DSN Using Microsoft Excel
Using the Create New Data Source dialog box, you can create a DSN for your FileMaker Pro database. The DSN that is created this way will always be a File DSN.
1.
2. Start FileMaker Pro and open the database for which you want to create the data source.
3. Start Microsoft Excel.
4. On the Data menu, point to Get External Data and then click Create New Query.
5. On the Databases tab, select <New Data Source> and then click OK.
6. Type the name of your data source in the What name do you want to give your data source? box.
7. In the Select a driver for the type of database you want to access list, select ODBC 3.11 FileMaker Pro PPC.
8. Click Connect.
9. If you want to specify a default table for your data source, select the table in the Select a default table for your data source (optional) list.
10. Click OK.
When I get to step 7, the ODBC 3.11 driver is not listed; how do I access or download that?
Anything else I should know?
Create a File DSN Using Microsoft Excel
Using the Create New Data Source dialog box, you can create a DSN for your FileMaker Pro database. The DSN that is created this way will always be a File DSN.
1.
2. Start FileMaker Pro and open the database for which you want to create the data source.
3. Start Microsoft Excel.
4. On the Data menu, point to Get External Data and then click Create New Query.
5. On the Databases tab, select <New Data Source> and then click OK.
6. Type the name of your data source in the What name do you want to give your data source? box.
7. In the Select a driver for the type of database you want to access list, select ODBC 3.11 FileMaker Pro PPC.
8. Click Connect.
9. If you want to specify a default table for your data source, select the table in the Select a default table for your data source (optional) list.
10. Click OK.
When I get to step 7, the ODBC 3.11 driver is not listed; how do I access or download that?
Anything else I should know?
Here are some resources from FileMaker on using ODBC
http://www.filemaker.com/support/technologies/odbc.html
http://www.filemaker.com/support/product/docs/fms/fm11_odbc_jdbc_guide_en.pdf
http://www.filemaker.com/help/html/odbc.18.1.html
And it looks like the driver you need is in the "xDBC" folder.
You may need MDAC as well, available from Microsoft:
http://www.microsoft.com/downloads/details.aspx?familyid=78cac895-efc2-4f8e-a9e0-3a1afbd5922e&displaylang=en
http://www.filemaker.com/support/technologies/odbc.html
http://www.filemaker.com/support/product/docs/fms/fm11_odbc_jdbc_guide_en.pdf
http://www.filemaker.com/help/html/odbc.18.1.html
And it looks like the driver you need is in the "xDBC" folder.
You may need MDAC as well, available from Microsoft:
http://www.microsoft.com/downloads/details.aspx?familyid=78cac895-efc2-4f8e-a9e0-3a1afbd5922e&displaylang=en
ASKER
I've installed the ODBC driver for FileMaker. I have the FM file open and am using Excel. I choose "get data from Microsoft Query," new data source, enter a name, select the FileMaker ODBC driver, click on connect, which opens the FM DSN Configuration dialog.
I choose localhost (a local file), and connect to host for available databases.
My database is listed in the databases window, and I choose next.
I choose test, it asks for Filemaker Login, enter the ID (no password), and it returns test completed successfully. I choose ok.
I return to the DSN Configuration screen, and choose done. It opens the Filemaker Login again, I enter the same ID, and there is a Microsoft Query dialog box which says "unable to open file, error 802. The help option does not have any info on error 802.
Any ideas?
I choose localhost (a local file), and connect to host for available databases.
My database is listed in the databases window, and I choose next.
I choose test, it asks for Filemaker Login, enter the ID (no password), and it returns test completed successfully. I choose ok.
I return to the DSN Configuration screen, and choose done. It opens the Filemaker Login again, I enter the same ID, and there is a Microsoft Query dialog box which says "unable to open file, error 802. The help option does not have any info on error 802.
Any ideas?
Try closing the FMP file before attempting to have another application access it.
You may need to create an account with a password and use this to login, or you might need to set up the database for Sharing or actually allow ODBC connections, something that is done in the database file's Administration property window.
Go to "Sharing -> ODBC/JDBC" and make sure ODBC/JDBC is turned on. Then make sure the Access privileges is set to All Users
You may need to create an account with a password and use this to login, or you might need to set up the database for Sharing or actually allow ODBC connections, something that is done in the database file's Administration property window.
Go to "Sharing -> ODBC/JDBC" and make sure ODBC/JDBC is turned on. Then make sure the Access privileges is set to All Users
ASKER
If the FMP file is closed, the connection fails at the test stage. I have checked to make sure ODBC sharing is enabled. I added a password.
Same result - the test completes successfully, then next screen says filemaker logon, it returns error 802.
Same result - the test completes successfully, then next screen says filemaker logon, it returns error 802.
What version of FileMaker Pro and Excel are you running?
Are you using Web Publishing? Are you running Windows 7/Vista/XP?
Are you using Web Publishing? Are you running Windows 7/Vista/XP?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Win Vista; FMP 11; Set up the connection via administrative services, and it seems to have worked.
It's packaged deep in an ODBC folder- you'll have to dig for it unfortunately. I have gotten it to work before to SQL server so an Excel connection would probably be very similar. I'll see if I can find my original connection string for you.
Find that driver and install it (there might even be a ReadMe on that installation media for FMP) and test the ODBC connection, then you can move on to Excel.