Can't create ODBC link to SQL 2016

[dkaplan]

I have an Access database (Access 10) that is linked to a SQL db via ODBC.

Recently that SQL db was migrated from a SQL 2008 server to a SQL 2016 server.

After that migration I established an ODBC link to the 2016 server using file DSN. This link works for myself, but users get the error: "connection to SQL Server Native Client 11.0'ServerName' failed."

Prior to the migration this problem did not occur.

I have checked the users permissions, and there appear to be no problems there.

Any thoughts?

 

[kray4660]

I am assuming that that each user has their own copy of the front end (FE) of the Access database. Have you provided them a copy of your working FE? If so, then I wonder if there is some sort of Access security that needs to be changed (been a while since I looked at Access security settings).

 

[dkaplan]

Thanks for your response, kray4660.

Actually, each user is connecting to the FE on a network.

I'm pretty sure it's not an Access security issue. Since my post, I've figured out that most of the users do not have an OBDC driver that is compatible with SQL Server 2016. My next focus will be to install new drivers on the users' machines and hope for the best.

 

[zandsc1]

I've got a similar setup but I'm using ODBC to connect to Postgres. We had to go through and configure the System DSN on each workstation when we started using ODBC so I suspect that will yield results for you if you are installing drivers and confirming the connection.

I had played a bit with a file DSN that I could put in a network location and point to with each copy of the FE to make it easier to change if I needed to, but I never got it to work satisfactorily.

 

[dkaplan]

Thanks for you comments, zandsc1.

I finally did get it to work for a single user by downloading ODBC Driver 11 for SQL Server onto the users PC. The link for this is:

https://www.microsoft.com/en-us/download/details.aspx?id=36434

Unfortunately, this ultimately has to work for multiple users, and I haven't gotten that to work yet. As you did, I tried putting the dsn on a network location, but this only resulted in odbc errors for user 2. Oddly, in the past, when the source SQL server was 2008, this was no problem.

Anyone know if something has changed in higher versions of SQL that makes odbc less forgiving?