Our client has a Microsoft Access 2010 application with AppFrontEnd.accdb and AppData.accdb. We have used SSMA to migrate AppData.accdb to AppData on a NEWSERVER. It is Windows Server 2019 with SQL Server 2019 Express edition.
- We have made sure that the SQL Server is all set for remote access with the recommended settings in SSMS, SS Configuration Manager, and Windows Defender Firewall.
- We have used the External Data features in AppFrontEnd.accdb to generate a DSN and correctly link the 23 tables in NEWSERVER\SQLEXPRESS. The DSN and AppFrontEnd.accdb reside in a folder named App_DSN.
- With that folder residing on the server, C:\App_DSN, the app starts and works just fine. (We installed Access 2010 to be able to prove that).
- We shared that folder with Advanced Sharing and granted Everyone Full Control.
- We signed into a user’s Windows 10 workstation with Access 2010 installed and copied the folder and its .accdb and .dsn file to be C:\App_DSN on the workstation
We cannot do anything with AppFrontEnd.accdb. It cannot connect to SQL Server. So we cannot even get to Linked Table Manager in design mode. We get a connection failure message. If we just let the app try to load, it closes abnormally leaving the lock file alive. And we are signed out of Windows.Configure SQL Server for Remote Access.docxAccessFEtoSQLSvr.dsn