steckelj
asked on
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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.