Avatar of Mark LaGrange
Mark LaGrange
 asked on

ODBC File DSN will not save default database

Hello - I've been migrating an Access 365 application back end to SQL Server 2019. So far, I have:
1) Installed SQL Server 2019 on a HyperV server running Windows Server 2016 (where I am the administrator).
2) Migrated the application backend to to a database in that instance.
3) Created a File DSN on my development pc (where I am the administrator) that successfully connects to the SQL Server db.
4) Used that DSN to to re-link the tables in the application front end

Everything seems to be working fine.

But now when I copy that re-linked FE and DSN over to a user pc (where I am logged as the pc's user), the tables no longer show up in the FE.
When I edit the DSN, the "Change default database to:" check box is not checked, although it seems to remember the database name.
When I re-check it, and re-select the database, the connection test is successfull, I save the DSN, go back into it, and the default db is gone again.

What's happeneing here?

(I set up the Domain User Login with the "Public" role and membership)

Thanks in advance.
Microsoft AccessMicrosoft SQL ServerWindows OSSQL

Avatar of undefined
Last Comment
Mark LaGrange

8/22/2022 - Mon
John Tsioumpris

Check the Linked Table Manager from Dale: https://www.experts-exchange.com/articles/32503/A-more-efficient-Linked-Table-Manager.html
Just ensure that the end pc has the appropriate drivers (SQL) .
Scott McDaniel (EE MVE )

I've always used DSNless methodologies when I have to distribute Access with linked tables:

Mark LaGrange

Thank you for your responses. I should have added that, when the dsn I created on my development pc didn't work on the user pc, I tried to create a new dsn on the user pc, and the SQL Server driver was there to select, I could connect to the server, select a default database (even though it wouldn't save it), and the connection test completed successfully. The problem continued to be that, when I tried to go back and link tables using that dsn, it was just picking up sys tables in the Master db.

As far as the DSN-less connection, I'll keep that in mind, Thanks.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
John Tsioumpris

Any chance you have 64bit and 32bit Access...each version requires it's own drivers

At least your manually set up DSN should keep the default database setting ...
Try if adding the corresponding value in the registry keeps it persistent. Best to look at the keys and values set on a working machine, and then copy the database one over. The required info should be located at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ resp. HKEY_CURRENT_USER\SOFTWARE\ODBC\ in the corresponding DSN key.
Mark LaGrange

Hello - I had to step away for another project, but I'm back on this now. Checking points you all mentioned above.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mark LaGrange

ok, Qlemo - I went down the registry paths you specified above, on my pc, as admin, and didn't see any dsn's at all, just the default ODBC drivers in the "ODBC.INI"  ?sub folder?
I didn't even see the one I created, that I was able to use to link to the SQL Server database. It works fine, seeing all the tbl's from the Access db.
I tried doing a search in the whole registry for the dsn name, and it wasn't found.
if the file name of the dsn was "MyFileDsn.dsn", and did a Find in the reg, from the top-level "Computer" node, for "MyFileDsn", would it find it like that?
Mark LaGrange

ok, John Tsioumpris - I checked between the 2 pc's (my dev pc, working, and the user pc, not saving the "Change default database" setting).
They appear to be the same:
  • Device: 64-bit OS, 64-bit processor
  • Windows version: W10 Pro, 64-bit
  • MS Access version: O365 MSO 16.0 64-bit
And I have been using the "ODBC Data Source Administrator (64-bit)"; the dsn's appear int he "Documents" folder on both pc's.
Mark LaGrange

ok, just tried adding a SQL Server Login for this particular user (of the user pc I've been trying to connect through).
Set her up just like my Login, except for being the admin/owner)
When I go back through her ODBC dsn, NOW it keeps the Change default database"
But when I try to link the Access FE via this dsn, it STILL is only seeing the "INFORMATION_SCHEMA" and  "sys_" tables.
I'm losing my mind...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark LaGrange

Next, I tried logging on to this user pc as the domain admin; created the file dsn, it worked. Copied it to the regular user's Documents folder.
Logged off, logged back on as the regular user, still can't see data tables.
John Tsioumpris

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

Also something else.... a file DSN is a simple text file .
So right click it and open it via Notepad....
Just use only this information to work your way through Access to  create a User/System DSN ...like all you had for info was the info provided in that particular file..
Mark LaGrange

Thank you all for your help. I wound up having a buddy take a look at it, and he had me add the DataReader and Data Writer roles to the Domain Users login. Worked fine after that.

Thanks again
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.