Linking Your Access Databases with ADOX

Linking Your Access Databases with ADOX

This small routine creates a link from all the tables of one .mdb file to another. First, you need to select reference ‘Microsoft ADOX’, your available version.

Private Sub CreateLinkTables()  On Error Resume Next  Dim cnDB1    As New ADODB.Connection  Dim cnDB2    As New ADODB.Connection  Dim catDB1   As New ADOX.Catalog  Dim catDB2   As New ADOX.Catalog  Dim tblDB2   As New ADOX.Table  Dim tblDB1   As ADOX.Table  With cnDB1  ' Create connection to database 1    .Provider = g_strDBProvider    .Properties("Jet OLEDB:Database Password") = "myDBpassword"    .Open "c:myDB1.mdb"  End With  Set catDB1.ActiveConnection = cnDB1  With cnDB2  ' Create connection to database 1    .Provider = g_strDBProvider    .Properties("Jet OLEDB:Database Password") = "myDBpassword"    .Open "c:myDB2.mdb"  End With  catDB2.ActiveConnection = cnDB2  For Each tblDB2 In catDB2.Tables  ' Loop through myDB2 catalog tables    If tblDB2.Type = "TABLE" Then ' Link only user created tables not viewand system table      Set tblDB1 = New ADOX.Table ' Re-initialize object      tblDB1.Name = tblDB2.Name      Set tblDB1.ParentCatalog = catDB1      tblDB1.Properties("Jet OLEDB:Create Link") = True      tblDB1.Properties("Jet OLEDB:Link Datasource") = "c:myDB2.mdb"      tblDB1.Properties("Jet OLEDB:Link Provider String") = ";Pwd=" &"myDBpassword"      tblDB1.Properties("Jet OLEDB:Remote Table Name") = tblDB2.Name      catDB1.Tables.Append tblDB1   ' Append tables to myDB1.mdb catalog    End If  Next  ' Tidy up  cnDB1.Close: Set cnDB1 = Nothing  cnDB2.Close: Set cnDB2 = Nothing  Set tblDB1 = Nothing: Set tblDB2 = Nothing  Set catDB1 = Nothing: Set catDB2 = NothingEnd Sub
See also  5 Tips for Working With an Onsite Interpreter

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist