troubleshooting Question

Remote Connection from MS Access to SQL Server fails, even with good DSN

Avatar of Douglass MacLean
Douglass MacLeanFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
6 Comments2 Solutions18 ViewsLast Modified:
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.docx
AccessFEtoSQLSvr.dsn
SOLUTION
Pavel Celba
All in One

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros