ODBC Error for one user after upgrading to Windows 10 Access 2016

[Alc]

Our company just upgraded everyone from Access 2010 on a Windows 7 device to Access 2016 on a Windows 10 device.
One of the problems encountered is as follows, but only happens to one person, which has me puzzled.

There is a line of code in a few of the databases that uses a global password and username to connect to a data warehouse, via an ODBC connection.

Dim db as database

Set db = OpenDatabase("", False, "ODBC;DSN={database name};UID={user id};PWD={password};DATABASE={database name};"

This code works fine for all bar one user. That one person gets a message: "Run Time Error: 3151 ODBC Connection to {database name} Failed".
Clicking on <debug> highlights the 'Set db...' line.

If I go to the data tab within that user's front end and try to connect to the database using the same username and password, it connects perfectly without any errors. so I know he has ODBC connections set up.

Tried without success:

1. Opening a different front end from the same user's PC
2. Compact and repair
3. Replacing that user's front end with a new version

 

[Isaac]

I'm no expert in using that specific method to open a remote odbc connection, but I do know that your declaration should probably prefix Database with dao. or ado. (Looks like DAO in this case).
And it could be that one specific user has a different reference set that makes it ambiguous...I'm not sure if that's your problem here but worth noting and changing.

 

[Gasman]

Never had to use OBDC, but is there anymore info in the Event Viewer?

 

[Minty]

Normally you have to specify the ODBC Driver to use in the connection string, DSN = {Database name} doesn't make a lot of sense? DSN normally refers to a file/system/user DSN File unless there is a connection string stored in a variable called DatabaseName (Which would be odd...

 

[Alc]

Thanks all.
1. Will change the declaration and see if that helps.
2. haven't used the Event Viewer before. Will look into it.
3. Yes, just the database name is given, no file/system/ etc. This was set up before I got here and has been running fine for years.

 

[Isaac]

Minty brings up a good point. Oddly, I did a quick google search and came across an equal number of purported "examples" showing DSN=[database_name] as I did DSN=[dsn_name] ... as if both might be correct in some cases. Not sure on that!...Weird.

Just wanted to mention that you may get some mileage from manually linking to the remote table, and then checking the linked table's connect string. Perhaps mimic however it gets created using the wizard. The subject of Connect Strings are seemingly infinite in variation, I sometimes feel.

 

[Alc]

Sorry, should have added that to the list of things I tried. The connection string generated by Access looks the same as the one in the code.