We help IT Professionals succeed at work.

ODBC File DSN will not save default database

Mark LaGrange
Mark LaGrange used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
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 )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

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

https://docs.microsoft.com/en-us/office/troubleshoot/access/create-dsn-less-connection-linkted-table

Author

Commented:
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.
John TsioumprisSoftware & Systems Engineer

Commented:
Any chance you have 64bit and 32bit Access...each version requires it's own drivers
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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.

Author

Commented:
Hello - I had to step away for another project, but I'm back on this now. Checking points you all mentioned above.
Thanks

Author

Commented:
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?

Author

Commented:
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.

Author

Commented:
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...

Author

Commented:
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.
Software & Systems Engineer
Commented:
Given the feedback i am thinking that probably is a permissions issue..
John TsioumprisSoftware & Systems Engineer

Commented:
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..

Author

Commented:
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