I have solved this issue by creating a module that executes when you open the database. Note that this is not a multiuser database and the code could be optimized to see if the application is logged on to SQLServer/ACTREADER and not execute if already logged on.
An Access table (tblACTREADERLogonInfo) stores the database name, server name and the SQLServer Id and Password I established for ACTREADER.
Public Sub SQLServerLogon()
Dim rstRecordSet As ADODB.Recordset
Set rstRecordSet = New ADODB.Recordset
rstRecordSet.CursorLocation = adUseClient
SQLString = ""
SQLString = SQLString + "SELECT tblACTREADERLogonInfo.* FROM tblACTREADERLogonInfo WHERE (((tblACTREADERLogonInfo.Id)=1));"
rstRecordSet.Open SQLString, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Do Until rstRecordSet.EOF
Dim mstrODBCConnect As String
Dim fLink As Boolean
Dim tdf As DAO.TableDef
Dim db As DAO.Database
mstrODBCConnect = "ODBC;Driver={SQL Native Client};" & _
"Server=" & rstRecordSet!ServerName & ";" & _
"Database=" & rstRecordSet!DatabaseName & ";" & _
"UID=" & rstRecordSet!LogInID & _
";PWD=" & rstRecordSet!Password
Set db = CurrentDb
For Each tdf In db.TableDefs
With tdf
' Only process linked ODBC tables
If .Attributes = dbAttachedODBC Then
fLink = LinkODBCTable( _
strLinkName:=.NAME, _
strConnect:=mstrODBCConnect, _
strSourceTableName:=.SourceTableName)
End If
End With
Next tdf
rstRecordSet.MoveNext
Loop
End Sub
Public Function LinkODBCTable(strLinkName As String, strConnect As String, strSourceTableName As String) As Boolean
' Links or relinks a single table.
' Returns True or False based on Err value.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db = CurrentDb
' Check to see if the table link already exists;
' if so, delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
' Ignore error and reset
Err.Number = 0
End If
Set tdf = db.CreateTableDef(strLinkName)
tdf.Connect = strConnect
tdf.SourceTableName = strSourceTableName
db.TableDefs.Append tdf
LinkODBCTable = (Err = 0)
End Function
