If I recall correctly, I also ran into some problems trying to refresh links
as opposed to recreating them from scratch. The following code is edited
and redacted (hopefully without introducing bugs). Orginal code did other
things as well. This is for linking MS SQL tables. ADO/ADOX 2.6 or
higher is recommended.
I don't understand the question about CurrentProject.BaseConnectionString.
=========
Private Const ODBC_ConnectionString As String = _
"ODBC;Driver={SQL
Server};Trusted_Connection=Yes;Server=ServerName;Database=DBName"
Public Sub ReCreateLinks()
Dim cat As ADOX.Catalog, ncat As ADOX.Catalog
Dim tbl As ADOX.Table, ntbl As ADOX.Table
Dim local_name() As String, remote_name() As String
Dim ntabs As Long, i As Long
Dim stemp As String
' Note, must have ODBC; in string, or else you will get
' the error: "80004005 - Could not find installable ISAM."
stemp = ODBC_ConnectionString
' Open the catalog for current database.
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
ntabs = 0
ReDim local_name(100)
ReDim remote_name(100)
' Get list of SQL PASS-THROUGH tables - cannot modify catalog at same
time.
' SQL: PASS-THROUGH ; Paradox: LINK ; Access: TABLE, VIEW
For Each tbl In cat.Tables
Debug.Print tbl.Name, tbl.Type
If tbl.Type = "PASS-THROUGH" Then
If ntabs > UBound(local_name) Then
ReDim Preserve local_name(UBound(local_name) + 10)
ReDim Preserve remote_name(UBound(local_name) + 10)
End If
local_name(ntabs) = tbl.Name
remote_name(ntabs) = tbl.Properties("Jet OLEDB:Remote Table
Name")
ntabs = ntabs + 1
End If
Next
' ReCreate tables
i = 0
Do While i < ntabs
cat.Tables.Delete local_name(i)
Call LinkSQLTable(local_name(i), remote_name(i), stemp)
i = i + 1
Loop
Debug.Print ntabs & " tables linked."
cat.Tables.Refresh
End Sub
=========
Public Function LinkSQLTable(local_name As String, remote_name As String,
cns As String)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
' Open the catalog using the current Access database.
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
' Create the new table.
Set tbl = New ADOX.Table
tbl.Name = local_name
Set tbl.ParentCatalog = cat
' Set the properties to create the link.
' Note: For Access link the Provider must be Jet OLEDB - do not specify.
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Provider String") = cns
' tbl.Properties("Jet OLEDB:Link Datasource") = ""
tbl.Properties("Jet OLEDB:Remote Table Name") = remote_name
' Append the table to the tables collection of the catalog.
cat.Tables.Append tbl
Set cat = Nothing
End Function
=========
