Other user can't open SQL Table in Access

[temple owls]

Hello. Apologies in advance...this is the first time I'm linking a MS SQL table to Access so maybe I'm doing something stupid.

I moved my Access data to my IT's SQL server and then linked it to my Access DB. It works flawlessly for me.

When our other user tries to open the table in Access though, they are given a "ODBC--connection to 'ODBC Driver 17 for SQL Server XYZserver' failed." message.

I'm at a loss for what the issue is.

• The user is able to access XYZserver in SQL Management Studio (SSMS) via Windows Authentication.
• They can access the database in SSMS.
• They can see and select all the tables in SSMS.
• They are a db_owner in SSMS, same as me. I previously had them as a db_datareader user and it still threw the error.
• The table link in Access uses Windows Authentication, same as SSMS.
• They have the same exact version of Access as me (I even had them log into my device to test and it still threw the same error)

Here's my connection string: DRIVER=ODBC Driver 17 for SQL Server;SERVER=XYZserver;Trusted_Connection=Yes;APP=Microsoft Office;

Any help or suggestions you could provide are much appreciated. Thanks!!

 

[dalski]

I'm brand new to MSSQL like you, possibly password needed for server? ODBC driver 17 is fine I think, 18 is not for Access. It might also be that on creating the table a wizard was used rather than importing the table via VBA; which VBA avoids some sort of local records being created I think; further obfuscating/ unneccessary prompts. Others will know more than me. Just a quick response in case it helps.

 

[temple owls]

hmm interesting. Yes, I did use the wizard to import the table. I'll look into importing it via VBA (hopefully others know more as well). Thanks!!

 

[dalski]

Check out Sean MacKenzie's video for this command. I believe it avoids a DNS file being created. If you don't select to remember password (which you shouldn't because the ODBC password is stored locally then; allowing hackers to obtain the password) so it prompts the user to enter the password to the server on creating each table. This only happens the first time, but shouldn't if you've handed it setup in this way to another user. It's probably not the case in your situation but I mention as it's helpful info for future.

 

[cheekybuddha]

The odbc driver must be installed on each client machine.

Is that the case?

 

[cheekybuddha]

Oops! Didn't see that they can connect via ssms, so the drivers must be installed.

You are using trusted connection. Are all the other users set up to be able to access the server?

 

[RonPaii]

I think, 18 is not for Access

We have been using ODBC driver 18 with Access for almost a year.

Verify each user has the driver called out in the connection string. If possible have your IT push it out to all users.

 

[temple owls]

Oops! Didn't see that they can connect via ssms, so the drivers must be installed.

You are using trusted connection. Are all the other users set up to be able to access the server?

When I access the permissions of the database in SSMS, the users are listed and they can access everything in SSMS (sorry; not sure if that's what you're asking)

 

[dalski]

We have been using ODBC driver 18 with Access for almost a year.

Sorry @temple owls, I gave bad info. No matter what I did i couldn't get it to work.

 

[tvanstiphout]

ODBC driver 17 is fine I think, 18 is not for Access.

Somebody should say that to my apps, which use ODBC 18. A typical connection string:
ODBC;Description=xxx;DRIVER=ODBC Driver 18 for SQL Server;SERVER=.\SQL2022;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=yyy;TrustServerCertificate=Yes;

 

[dalski]

I don't know why mine wouldn't work, I spent hours on it, just could not get it going.

 

[tvanstiphout]

I don't know why mine wouldn't work, I spent hours on it, just could not get it going.

Maybe because of the new-ish TrustServerCertificate clause.

 

[Minty]

I'm brand new to MSSQL like you, possibly password needed for server? ODBC driver 17 is fine I think, 18 is not for Access. It might also be that on creating the table a wizard was used rather than importing the table via VBA; which VBA avoids some sort of local records being created I think; further obfuscating/ unneccessary prompts. Others will know more than me. Just a quick response in case it helps.

Just a correction: Version 18 of the ODBC driver works fine with Access, we use it with quite a number of clients. Not sure why you would be having issues with it.

Edit: apologies I missed both Ron and Tom's earlier reply. Doh!

 

[RonPaii]

Try adding Encrypt=No to the connection string.

 

[dalski]

I'm pretty sure I tried without encryption, also removing the password argument entirely, & without Trust Server amongst countless other things for a few hours. I'll try again later, atm I'm just grateful I got my data in there & a mountain of things to learn.
Obvs better to use latest driver I recon. Famous last words - I don't think it'll falter me later to just relink the tables with an ODBC 18 ver; please correct me if I'm no doubt wrong & I will persevere in getting ver 18 working.

Sorry to hijack your thread @temple owls.

 

[temple owls]

I'm an absolute idiot, guys. I linked the table through the import wizard, and I'm just realizing that it saved the DSN file in my personal Documents folder. Moving it to a network folder fixed the issue. I'm sorry.

 

[theDBguy]

I'm an absolute idiot, guys. I linked the table through the import wizard, and I'm just realizing that it saved the DSN file in my personal Documents folder. Moving it to a network folder fixed the issue. I'm sorry.

I was about to suggest/recommend trying a DSN-less connection.

 

[The_Doc_Man]

I'm an absolute idiot, guys. I linked the table through the import wizard, and I'm just realizing that it saved the DSN file in my personal Documents folder. Moving it to a network folder fixed the issue. I'm sorry.

Two comments: First, many times we have ourselves done things leading to a sudden realization that we are quite human and prone to error.
Second, thanks for revealing the solution, because you never know when someone will find it and realized 'Oh, THAT is why it won't work!" Thanks for contributing a solution!

 

[Gasman]

Please mark it solved then

 

[The_Doc_Man]

@temple owls
Since you are new to the forum, here's a hint. Open the thread to the 1st page and just below the thread title there is a short list of options you can click, one of which is "Solved." When you solve a problem you let us know that you don't need any more help on this problem. That makes it easy for the people who would help you to know they can spend more time on someone else. And we don't care at all if you have to open another thread for a new, different problem at a later time.