Using linked tables from Access 2010 to the SQL server 2012 backend with SQL Server Authentication. The linked tables are accessible for the current session, however, when I quit from Access and then re-open the database, it has lost the connection to the database. Furthermore, it goes into an unresponsive state for about one minute, then shows an error message that the Login Timeout has Expired. When I dismiss that dialog, a new dialog is presented to log into the SQL server. I log in with exactly the same credentials as before, and everything works fine until I quit from Access again. I have tried using a Trusted Connection, a DSN with Linked Table Manager, and variations of the code. Nothing seems to retain the current login to the SQL Server.
Here are some debug.print statements from the code. The first one prints the .Connect property of the TableDef object that creates the table, and the second one prints the Connect property of the local Access linked tables after the TableDef object has been appended.
ODBC;DRIVER=SQL Server;SERVER=LENOVONOTEBOOK\SQLEXPRESS;UID=Volunt eer;PWD=xxxx;APP=Microsoft Office 2003;WSID=LENOVONOTEBOOK;DATABASE=cresql;Network=D BMSSOCN
ODBC;DRIVER=SQL Server;SERVER=LENOVONOTEBOOK\SQLEXPRESS;APP=Micros oft Office 2003;WSID=LENOVONOTEBOOK;DATABASE=cresql;Network=D BMSSOCN
As you can see, the resulting .Connect string contains no reference to the User and Password. Is this the expected behaviour? If so, does Access store that information somewhere else on the computer? Or is it normal to require a login for every Access session?
Here is the relinking code
Code:
strConnect = "ODBC;Driver={SQL Server};UID=" & Me.txtUser & ";PWD=" & Me.txtPwd & ";" ' SQL Server login
' Walk through the recordset and create the links.
Do Until rst.EOF
Set tdf = db.CreateTableDef(rst!localtablename)
tdf.Connect = strConnect & "Server=" & strServer & ";Database=" & strDB & ";" ' Set the Connect property to establish the link.
tdf.SourceTableName = rst!remotetablename
db.TableDefs.Append tdf
Debug.Print tdf.Connect
Debug.Print CurrentDb.TableDefs(rst!localtablename).Connect
rst.MoveNext
Loop
This code worked until we recently upgraded the workstations from Windows 7 to Windows 10, and moved the SQL Server from a Windows 7 machine to a Windows Server machine. Any help would be appreciated to know how to retain the login credentials from Access to the SQL Server.