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?

Aphorism

Answer 1

Well, I was able to narrow the issue a bit. Access for some reason keeps trying to use Windows Credentials instead of the username in my ODBC file. I can't find a way around it, but I was able to resolve the issue by creating logins on my SQL Server for the windows users that need access.

I am not incredibly happy about needing to manage more logins, but that's what i did to resolve this issue.

Aphorism

Answer 2

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

Bestial

I thought that since the ODBC file was on a shared drive that would be enough. So I will need to install an ODBC file on each machine separately? – Aphorism

aha, you use a FILE for the ODBC connection - do the users have rights to access the share, and is it mapped with the same drive letter/path ? (would have to test it once, but today i'm off the whole day ...) – Bestial

They all have access to the drive yes. I believe it is mapped to the same drive letter but it is possible that it isn't. How would I go about fixing that if they are different drive letters? – Aphorism

I just double checked. At least one of the machines does have the same drive letter. – Aphorism

Answer 3

I don't mean to resurrect the dead, but I had this same error stem from a different issue.

I was using an ODBC connection. When running the file using the 'Design' run button it worked fine. When I tried using the Navigation Pane and double clicking on it, the error would happen.

The structure of my query was the problem; I was porting a SQL server query over and the single quote ' parameter passing was not well received in Access. Changing these over to double quotes " made it work.

Crossruff

Answer 4

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!

Monas

Answer 5

In my case there were columns having +' or -' substring combinations; error disapeared after removing ' characters from column definition

Grovergroves