ODBC Call Failed when different ODBC tables are linked with different accounts

[sharpnova]

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?

 

[sharpnova]

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.

 

[pbaldy]

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

 

[sharpnova]

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)