ODBC Call Failed when different ODBC tables are linked with different accounts (1 Viewer)

I have two linked ODBC tables in my access applications. If I link them both with the same account, they work fine.

If I link them each with a different account, when I view one, it will work fine. When I try to view the other, it will give an ODBC call failed.

Regardless of which one I view first, it will work, then the second will not work.

Is this a bug in Access 2003?

Are you using the same DSN? You may need to use a separate DSN if you want to link to the same datasource with different credentials at the same time.

Sorry but what exactly is a DSN? I see many references to it on the web and an abstract definition, but I'm not sure what it is if it's not simply the account I'm using to link a table.

Go to Control Panel/Administrative Tools/Data Sources (ODBC). There you will see the DSN's defined on your system that are used to link to remote data sources.

If you're doing what I think you're doing, I ran into the same problem and never found a solution. I have SQL Server (SS) databases for different things, for instance one for vehicle maintenance and another for driver activity. I created SS users for each and file DSN's for each, using the respective users. In Access, I linked the tables using the two different DSN's and users, selecting the box to save the passwords. As described by sharpnova, whichever table I opened first was fine, the second would throw a call failed error.

I can't say that I researched it that much. Being lazy, it was simpler to create a single SS user with permissions to both databases, and link using that (using 2 new file DSN's).

Yep. That's the solution I went for. It was just weird that this behavior occurred.

I guess we can just chalk it up to an Access bug. (could be a pain in some cases)