Link Tables in Access Using ADOX

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

Anonymous

Version Compatibility: Visual Basic 6, Visual Basic 5

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
Private Declare Function GetShortPathName Lib "kernel32" Alias "GetShortPathNameA" (ByVal lpszLongPath As String, ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long

Code:

Private Sub LinkTable(psTable As String, psFromPath As String, _
  psToPath As String)

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim sShortPath As String
    
    'get short path name of the source database
    sShortPath = Space(255)
    Call GetShortPathName(psFromPath, sShortPath, 255)
    sShortPath = Trim$(sShortPath)
    sShortPath = Left$(sShortPath, Len(sShortPath) - 1)
    
    'connect to the target database
    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = psToPath
        .Open
    End With
    
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = cnn
        
    'link table
    Set tbl = New ADOX.Table
    With tbl
         .Name = psTable
         Set .ParentCatalog = cat
         .Properties("Jet OLEDB:Create Link") = True
         .Properties("Jet OLEDB:Link Datasource") = sShortPath
         .Properties("Jet OLEDB:Remote Table Name") = psTable
         
         On Error Resume Next
         cat.Tables.Delete psTable
         On Error GoTo 0
         
         cat.Tables.Append tbl
    End With
    Set tbl = Nothing
    
    'release references
    cnn.Close
    Set cnn = Nothing
    
    Set cat = Nothing
    
End Sub