[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-18
4
Medium Priority
?
874 Views
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.
0
Comment
Question by:steckelj
  • 2
  • 2
4 Comments
 
LVL 38

Expert Comment

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

0
 

Author Comment

by:steckelj
ID: 22989020
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.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 400 total points
ID: 22989167
Here is how to add a System DSN on the fly. I use the code frequently.
http://www.experts-exchange.com/Q_21616489.html#15210262

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

Accepted Solution

by:
steckelj earned 0 total points
ID: 22998596
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.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

834 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