Access ODBC Issue: ODBC--Call failed

Question

I created an Access front end for a SQL DB on my PC for use throughout my company. I am using a file ODBC connection and putting both the ODBC file and the Access file on a shared network drive.

When I load the access file, for some reason it seems to default to using my windows login credentials and pulls in the data perfectly. When a user attempts to open the file, they receive an error message saying "ODBC --call failed.". I can open the Linked Table Manager for them and check 'ask for new location' then specify the ODBC file and it all works fine...however it doesn't seem to save anything. I get the error each time someone other then myself opens this file.

Any idea what could be wrong? I am not an Access guy by trade, it just seems to be the tool we need for the moment.

--EDIT: For Clarification I am using a file ODBC connection

--Edit 2--

Riddle me this. So I have been troubleshooting this issue and I came across something interesting. I was logged in as one of my users and did the following:

1. Create a new access file that references a file ODBC connection on the desktop.
2. Create 3-4 linked tables in the access file, using the ODBC file on the desktop.
3. Save and close the access file.
4. Re-Open said file.... and I get an ODBC connection error! Right after everything was fine in a fresh file!

Anyone ever experience this?

Answer 1

i assume you didn't install the ODBC correctly on each users PC. you should create the ODBC-definition. You can create the relevant statements directly in the registry, see this branch
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

if you give the same name as on your developer machine, then it works. that's how i do it with my client applications that i develop on my machine and then install it at client site

Answer 2

After beating my head against the wall having this same issue, I finally discovered I had a checkbox on the Access form that defaulted to NULL. Since I put the backend into SQL, the checkbox fields cannot be NULL but the 'ODBC Call Failed' message did not help. I finally tried to add a record directly on the table via Access and it gave more information. I set all the checkboxes to default to zero and it resolved the problem!