Please try these connection strings:
Live:
;DATABASE=\\musnas04\cpd_projects\CTO_OR\05- M&I\05-M&I Support\QAQC Supportive Documents\_QAQC Tool\Database\BE\ARDC-BE-R1.mdb;TABLE=
Test:
;DATABASE= C:\Scratch\C Data\Desktop\QA -QC tools\Main DB\R5.8\be\ARDC-BE-R1.mdb;TABLE=
And replace the vcLinkTableDefs() function with this updated one (it concatenates the source table name to the strings in the settings table):
Code:
Public Function vcLinkTableDefs()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strNewConnectionString As String, sSourceTable As String
On Error Resume Next
strNewConnectionString = IIf(DLookup("ConnectTo", "tblSettings") = "Test", DLookup("[ConnectionStringTest]", "[tblSettings]"), DLookup("[ConnectionStringLive]", "[tblSettings]"))
Set dbs = CurrentDb()
' Loop through TableDefs collection, only processing
' the table if it already has a Connection property.
' (all other tables are local ... not linked)
For Each tdf In dbs.TableDefs
If tdf.Connect <> "" Then
sSourceTable = tdf.SourceTableName
If tdf.Connect <> strNewConnectionString Then
tdf.Connect = strNewConnectionString & sSourceTable
tdf.RefreshLink
End If
End If
Next
Set tdf = Nothing
Set dbs = Nothing
End Function
Cheers,