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
Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS