ODBC Error for one user after upgrading to Windows 10 Access 2016 (1 Viewer)

Alc

Registered User.
Local time
Today, 11:40
Joined
Mar 23, 2007
Messages
2,407
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.
Code:
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

Lifelong Learner
Local time
Today, 08:40
Joined
Mar 14, 2017
Messages
8,738
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.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 15:40
Joined
Sep 21, 2011
Messages
14,056
Never had to use OBDC, but is there anymore info in the Event Viewer?
 

Minty

AWF VIP
Local time
Today, 15:40
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Today, 11:40
Joined
Mar 23, 2007
Messages
2,407
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

Lifelong Learner
Local time
Today, 08:40
Joined
Mar 14, 2017
Messages
8,738
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

Registered User.
Local time
Today, 11:40
Joined
Mar 23, 2007
Messages
2,407
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.
 

Users who are viewing this thread

Top Bottom