Search code examples
sqlms-accessodbcms-office

Access ODBC Issue: ODBC--Call failed

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?

Solution

  • 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.