Solved

Problem linking MS Access to Filemaker Pro database using ODBC

Posted on 2011-02-15
14
2,068 Views
Last Modified: 2012-06-27
I am trying to link MS Access 2007 to a Filemaker Pro Advanced 11 file. I get to a point where it asks me to test the connection which is successful. However, when I click next and enter the user name and password I get error 802 - cannot connect to file. I have tried two different files (one is the sample file that comes with filemaker) with the same result.

The Filemaker file is open and ODBC is enabled. The ODBC driver seems to be installed correctly.

Why doesn't it work.
0
Comment
Question by:kwheil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 84
ID: 34897694
Often this means that you can connect to the "server", but that the username you're connecting with doesn't have permssion for that specific database/table. I'm not overly familiar with Filemaker, so this may be off the mark, and if so then please feel free to ignore my response.
0
 

Author Comment

by:kwheil
ID: 34901993
Thanks for your response. I setup an account as an administrator and opened the filemaker file using that account. When trying to establish the ODBC link from MS Access the test link works fine but then next step produces an "ODBC-call failed" error. Both files reside on my laptop that I have administrative rights to.
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34912625
Try this to start troubleshooting::

Go to Start>All Programs>Administrative Tools>Data Sources (ODBC)
Go to the User DSN tab
(You probably have several User Data Sources listed there.)
Select the User Data Source that uses the Drive you want to use.
Click Configure
Make sure connection parameters are correct. Click Test.
If the test is not successful then there is a problem in the connection parameters or driver of something else (not connected to FM).

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:kwheil
ID: 34920934
I tested the DSN for Filemaker and the test was successful. However, when I attempt to link the file I get the following error message:

ODBC-call failed.
[Filemaker][Filemaker] (802): Unable to open file (#802)

The following appears on the screen with the test button:

FileMaker ODBC Driver (32-bit) 11.3.76.0

Data Source Name:
Description:
Host: localhost
Database: Facilities.fp7
Use Long Varchar Type: No
Auto-detect Language Settings: Yes
Log Long Running Queries: No
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34921407
Is the FM user login an account with the extended privilege of Acces via ODB/JDBC?

To check, open the FM database, go to File>Manage>Accounts & Privileges
Click on the Extended Privileges tab
Make sure that the user account you are using is listed in the [fmxdbc] Set

fm-fmxdbc.jpg
Is the FM database configured to share via ODBC?

To check, open the FM database, go to File>Sharing>ODBC/JDBC
Make sure ODBCV/JDBC Sharing is ON.
Also make sure you have the ODBC/JDBC access set to All Users (for testing ... you can restrict to a privilege set later if you want to).

  fm-odbc-sharing.jpg
0
 

Author Comment

by:kwheil
ID: 34923164
The account in FM has full access via ODBC/JDBC privileges.

ODBC/JDBC Sharing is set to on for All Users.

Both conditions were true for all actions noted above.

It seems that there should be a data source name recognized by FM but it shows blank on the screen with the test button.
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34927908
>It seems that there should be a data source name recognized by FM but it shows blank on the screen with the test button.

Can you post a screenshot of that?
0
 

Author Comment

by:kwheil
ID: 34928640
I have attached an image with a sequence of screen shots from the linking process. In the process of creating the screen shots I tried something different. Rather than linking using the DSN file I clicked on the Machine Data Source tab and selected Filemaker. A login screen popped up and I was able to link to the Filemaker file and open tables. However, after closing the MS Access file, Filemaker would not let me close the file because it said another user was connected. I finally had to force it to shutdown.
Filemaker-ODBC-Screens.jpg
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34929585
When I add a User DSN with FileMaker ODBC driver

 FM_ODBC.jpg
The first screen I get is this:
 FM_ODBC_1stScreen.jpg
And then this
FM_ODBC_2ndScreen.jpg
I can't click Next without entering a Data Source Name. Do you not get that screen?
0
 

Author Comment

by:kwheil
ID: 34930161
The datasource does exist and has a name. It just doesn't show up on the Filemaker screen after it is selected.
Filemaker-DSN-Source.jpg
Filemaker-Select-DSN-Source.jpg
0
 
LVL 4

Expert Comment

by:challengeday
ID: 34930328
That's strange. When I name the Data Source, it shows up for m in the test dialog.


0
 

Author Comment

by:kwheil
ID: 34930401
Yes, that's what I would expect but it does not show up in mine as you can see from the first series of screen shots. However, the test is successful but the file connection is not.
0
 
LVL 4

Accepted Solution

by:
challengeday earned 500 total points
ID: 34930433
Ok, I was able to reproduce the problem. When you select File Data Source you are creating a File DSN instead of a User DSN, and the File DSN looks like it doesn't take a name.

Like you, I was successful when I created my data source from the ODBC Data Source Administrator as a User DSN and select Machine Data Source in Access.

I did not experience the having to force quit problem though.

0
 

Author Closing Comment

by:kwheil
ID: 34931509
Thanks for the help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question