In Win7, we're linking tables in an MS Access 2010 db to tables in a SQL Server 2008 R2 db. The driver user by the File dsn is SQL Server version 6.01.7601.17514 & we're using SQL Server Authentication.
For some reason, one user gets the msg "ODBC--connection to 'SQL Serverservername' failed" before they're even asked for a password, but for other users, the prompt comes up and when they uncheck the Windows Auth box, they enter their password and connect successfully to the SQL Server db. Both users have db_datareader access to the SQL Server db.
I had the user that gets the error msg log onto my PC and they get the same error (yet it works for me.)
This user was, however, able to successfully relink the tables, but then when we closed the access db and opened it again, the user got the "ODBC--connection..." error agin.
I'm stumped as to why this is happening for one user.
- Moved by Olaf HelperMVP Thursday, January 23, 2014 3:28 PM Moved from "SQL Database Engine" to a more related forum
Users have the same version of MS access and SQL server and both have read access to the SQL Server database, yet one is having the problem (user1).
And when user1 logs on to a different pc, even though other users can access the linked tables in the access db when they're logged into that computer, user1 still gets the error message. When other users log in and to open a linked table, they are asked to enter their id/password and then connect successfully.