Following on from Paul's article, I use this function in various different setups:
Code:
Function GetConnectionString(strLinkType, strLinkServer, strLinkDatabase, _ strLinkUsername, strLinkPassword, strAppName ) As String
Select Case strLinkType 'Get the link type to determine the connection string
#If SQL_DRIVER = "Native" Then
Case "SQL"
GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=strAppName;" & _
"UID=" & Nz(strLinkUsername, "") & ";" & _
"PWD=" & Nz(strLinkPassword, "")
Case "SQL-Trusted"
GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=strAppName;" & _
"Trusted_Connection=yes;"
#ElseIf SQL_DRIVER = "MDAC" Then
Case "SQL"
GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=strAppName;" & _
"UID=" & Nz(strLinkUsername, "") & ";" & _
"PWD=" & Nz(strLinkPassword, "")
Case "SQL-Trusted"
GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
"SERVER=" & Nz(strLinkServer, "") & ";" & _
"DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
"APP=strAppName;" & _
"Trusted_Connection=yes;" & _
""
#End If
Case "Access"
If Nz(strLinkPassword, "") <> "" Then
GetConnectionString = "MS Access;" & _
"PWD=" & Nz(strLinkPassword, "") & ";" & _
"DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
Else
GetConnectionString = "MS Access;" & _
"DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
End If
Case Else
GetConnectionString = ""
End Select
End Function
The table details are listed in two tables
- tblTableLinkTypes - lists all external databases (SQL/Access) used by the FE application
- tblTableLinks - lists all tables in each of the linked databases
This makes it easy to add new tables or remove outdated tables
The function below is used to add/remove linked tables based on the connection string(s)
Code:
Option Compare Database
Option Explicit
Public LinkFlag As Boolean
Public MySet As DAO.Recordset
Public Function UpdateTableLinks()
'################################################
'Adds/removes linked tables depending on value of
'LinkFlag which is set in form used to manage table links
'################################################
On Error GoTo Err_UpdateTableLinks
Dim strSQL1 As String
strSQL1 = "SELECT tblTableLinks.TableName, tblTableLinks.TableAlias, tblTableLinks.LinkType," & _
" tblTableLinkTypes.LinkServer, tblTableLinkTypes.LinkDatabase, tblTableLinkTypes.LinkUsername," & _
" tblTableLinkTypes.LinkPassword, tblTableLinks.LinkActive" & _
" FROM tblTableLinks INNER JOIN tblTableLinkTypes ON tblTableLinks.LinkType = tblTableLinkTypes.TableLinkType;"
'Debug.Print strSQL1
Set MySet = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
With MySet
'CR - check if any links exist
If .RecordCount = 0 Then Exit Function
strLinkType = "SQL"
strLinkServer = !LinkServer
strLinkDatabase = !LinkDatabase
strLinkUsername = !LinkUsername
strLinkPassword = !LinkPassword
strConnectionString = GetConnectionString(strLinkType, strLinkServer, strLinkDatabase, strLinkUsername, strLinkPassword)
'Debug.Print "strConnectionString = " & strConnectionString
If LinkFlag = "Yes" Then
'add links to tables
Do Until .EOF
.Edit
strTableAlias = !TableAlias
strTableName = !TableName
If TestLink(strTableAlias) = False Then
LinkTable strTableName, strTableAlias, strConnectionString, True
End If
' Debug.Print !TableAlias & " relinked"
.MoveNext
Loop
.Close
Else 'LinkFlag = "No"
'remove all linked tables
Do Until .EOF
'strTableName = !TableAlias
.Edit
strTableAlias = !TableAlias
If TestLink(strTableAlias) = True Then
RemoveTableLink strTableAlias, True
End If
' Debug.Print !TableAlias & " link removed"
.MoveNext
Loop
.Close
End If
End With
Set MySet = Nothing
Exit_UpdateTableLinks:
Exit Function
Err_UpdateTableLinks:
If Err.Number <> 3265 And Err.Number <> 3021 Then MsgBox Err.Number & " " & Err.Description
Resume Exit_UpdateTableLinks
End Function
Once you have relinked the FE database to all external databases it is placed in a shared network Upgrade folder together with a version.txt file
Users click on a desktop shortcut which checks whether the version in the Upgrade folder is newer than the file on their own local drive.
If so, the newer file(s) are COPIED to that user's computer automatically
IMPORTANT: USERS NEVER RUN THE APPLICATION FROM THE SHARED NETWORK FOLDER