Access 2007 SQL 2k5 Linked DB Gives "ODBC Call Failed" to Second User

I recently converted an Access 2007 self-contained DB to an SQL 2k5 Linked DB.  I moved the tables into an MSSQL 2k5 Standard DB and then linked the tables into Access.  I used a System ODBC connection to connect Access to the SQL DB.

Everything works great for me.  When another user opens the Access DB, they get "ODBC Call Failed" when the default form opens, or they try to open a datasheet view of a table, or anything else related to anything housed on the SQL server.  If I refresh the linked tables w/the linked table manager for that user (meaning on their machine as them), then they're completely fine and everything works as it did for me.  At this point though, if I go back and open the Access DB on my end - I'll get the Call Failed message.  If I refresh my tables, then I'm ok - but now the user can't get in again.

So to sum up - only one person at one machine can use the SQL tables at a time.  All other users will get "ODBC - Call Failed".  The only way I found so far to fix that message is to refresh the tables on their machine, however this immediately breaks the other individual connected.

For the sake of testing, I created a domain users login and associated it to the DB user.  Permissions are dbo all around.  SQL 2k5 and the DB itself are configured for multiple connections, the local/remote stuff has been setup (surface area config), and all the ODBC connections test successfully w/login even when the tables won't open within Access - both via TCP and even Named Pipes.  ODBC's are configured for Windows Auth and, like I mentioned, once you refresh the tables the users have all the right permissions. (Obviously they won't be DBO's once I get this fixed, ha)

Many thanks in advance for any help anyone can provide, I really appreciate all the help I find here time and time again.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
>> even Named Pipes.

By default, I shut off Named Pipes on SQL Server as one of the first things. It was designed for small, very fast, very stable LANs. It has caused me no end of grief.

Are you all using the same Acc DB off a network? If you have a copy on your machine and the other user has a copy on their machine does it work?  If so, then I would suspect it is some kind of security issue. I haven't gotten to Acc 2007, yet, but if they have anything like Vista's security I'm dreading it.

BTW, you didn't mention your OS.

steckeljAuthor Commented:
Hi jimpen, thanks so much for your response.

I tried two files and that seems to work (I think, it gets a little confusing with how it's linked to SQL).

That fact led me to believe Access was somehow storing the ODBC logon info in the file, meaning Windows Authentication on the ODBC connection wouldn't work.  I tried creating a SQL logon on the SQL server and then changing both system ODBC (on each machine) to the same logon info, but this produces the same problem.  If you refresh the tables on one system it works, but then if you refresh the tables on the second system it breaks the first one w/the same message, ODBC call failed.

OSes are WinXP SP2 on the workstations, at the server is Windows 2003 x64 Enterprise w/SQL 2005 Standard, default instance.
Jim P.Commented:
Here is how to add a System DSN on the fly. I use the code frequently.

I think what you'll have to do is just use it as a front-end DB. There are many examples of how to do version control to copy the latest from a server location to a local PC.
steckeljAuthor Commented:
I have this resolved.

The scenerio above occurs when System ODBC connections are created via control panel on the machine.

The DB works perfectly when I created a DSN file and placed it in the same folder as the DB on a mapped network drive available to all clients.  I then re-linked the tables one last time via the file, and the DB can now be opened on any machine and connect to the DB without error.

I think this is due to the "Workstation ID" stored with the login information in the Access DB for the ODBC connections created through control panel.  This information isn't present in the DSN file.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.