Remote Connection from MS Access to SQL Server fails, even with good DSN
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 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
- 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
strivoli
Please POST a PIC. Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All helpful. Right now the client's internal network has other problems. Need to wait for that to get resolved. When it does:
Yup I will send a pic as strivoli advised
Yup. I will create a DSN outside of Access as pcelba advised
Using Jim's excellent advice.
BTW: I know the shift key, Jim. But I appreciate you making sure.
And I definitely will replace sa with a different account. Used it at the start to 'keep it simple for testing' That didn't work out so well :-(
Yup I will send a pic as strivoli advised
Yup. I will create a DSN outside of Access as pcelba advised
Using Jim's excellent advice.
BTW: I know the shift key, Jim. But I appreciate you making sure.
And I definitely will replace sa with a different account. Used it at the start to 'keep it simple for testing' That didn't work out so well :-(
take a look at my sample application...if you put the credentials correctly you should be able to connect
https://www.dropbox.com/s/bdki4ldnifb7hl2/BenchMark_Release.accde?dl=0
The connection string should be :
Driver={ODBC Driver 17 for SQL Server};SERVER=NAME_OF_SERVER;Database=DATABASE_NAME;UID=USERNAME;PWD=YOURPASSWORD
The concept of the application is for other purpose but it should help you to check if you have everything in place
Hit the button with the thunder on bottom left...
EDIT : If you are missing driver 17 change to SQL Server
https://www.dropbox.com/s/bdki4ldnifb7hl2/BenchMark_Release.accde?dl=0
The connection string should be :
Driver={ODBC Driver 17 for SQL Server};SERVER=NAME_OF_SERVER;Database=DATABASE_NAME;UID=USERNAME;PWD=YOURPASSWORD
The concept of the application is for other purpose but it should help you to check if you have everything in placeHit the button with the thunder on bottom left...
EDIT : If you are missing driver 17 change to SQL Server
ASKER
That is helpful, John. Will try it out today.