I have a Access/VBA front-end application that will connect to a SQL Server back-end database and link several tables using a DSN. I need to store the User ID and Password in the DSN such that the front-end Visual Basic code to link the tables will be structured as shown below ...
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=DynamicsGP", acTable, "dbo.rm00101", "RM00101"
Currently, I get prompted for the password each time and for each table. In my DSN when prompted ...
"How should SQL Server verify the authenticity of the Login ID'?
1. With Integrated Windows Authentication.
2. With SQL Server Authentication using Login ID and Password entere by user.
I have #2 selected and entered a Login ID with Password but the Password never remains saved thus I get prompted each time in the VB Code to link the tables using this DSN.
Any help will be appreciated.