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.
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
Last Comment
Mark LaGrange
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
https://docs.microsoft.com/en-us/office/troubleshoot/access/create-dsn-less-connection-linkted-table
ASKER
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.
As far as the DSN-less connection, I'll keep that in mind, Thanks.
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\SOFTWAR
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\SOFTWAR
ASKER
Hello - I had to step away for another project, but I'm back on this now. Checking points you all mentioned above.
Thanks
Thanks
ASKER
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?
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?
ASKER
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:
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
ASKER
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...
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...
ASKER
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.
Logged off, logged back on as the regular user, still can't see data tables.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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..
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..
ASKER
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
Thanks again
Just ensure that the end pc has the appropriate drivers (SQL) .