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

Posted on 2008-11-18
Last Modified: 2012-08-13
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.
Question by:steckelj
    LVL 38

    Expert Comment

    by:Jim P.
    >> 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.


    Author Comment

    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.
    LVL 38

    Assisted Solution

    by:Jim P.
    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.

    Accepted Solution

    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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now