Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Linking Tables via Jet and ADO

From Wiki

Jump to: navigation, search

You can link tables to an mdb without using Access, just Jet and ADO. I have set this up so that it will run in VBScript, but you can also run it in Access, so I have included notes on type declaration and a library reference.

   'Reference Microsoft ADO Ext x.x for DDL and Security
   Dim cn 'As ADODB.Connection
   Dim ct 'As ADOX.Catalog
   Dim tbl 'As ADOX.Table
   
   Dim strLinkXL 'As String
   Dim strLinkMDB 'As String
   Dim strMDB 'As String
   
   strLinkXL = "C:\Docs\LTD.xls"
   strLinkMDB = "C:\Docs\db1.mdb"
   strMDB = "C:\Docs\LTD.mdb"
   
   'Create Link...
   Set cn = CreateObject("ADODB.Connection")
   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strMDB & ";" & _
          "Persist Security Info=False"
   
   Set ct = CreateObject("ADOX.Catalog")
   Set ct.ActiveConnection = cn
   
   Set tbl = CreateObject("ADOX.Table")
   Set tbl.ParentCatalog = ct
   
   '1. Link MDB
   
   With tbl
     'What the link table will be called
     .Name = "LinkTableMDB"
     'Name of the table to link
     .properties("Jet OLEDB:Remote Table Name") = "Table1"
     .properties("Jet OLEDB:Link Datasource") = strLinkMDB
     .properties("Jet OLEDB:Link Provider String") = "MS Access"
     .properties("Jet OLEDB:Create Link") = True
   End With
   
   'Append the table to the tables collection
   ct.Tables.Append tbl
   Set tbl = Nothing
   
   '2. Link Excel using named range
   Set tbl = CreateObject("ADOX.Table")
   Set tbl.ParentCatalog = ct
   
   With tbl
     .Name = "LinkTableXLRange"
     .properties("Jet OLEDB:Link Provider String") = "Excel 8.0;DATABASE=" & strLinkXL & ";HDR=Yes"
     'The named range
     .properties("Jet OLEDB:Remote Table Name") = "Data_Range"
     .properties("Jet OLEDB:Create Link") = True
   End With
   
   'Append the table to the tables collection
   ct.Tables.Append tbl
   Set tbl = Nothing
   
   '3. Link Excel by sheet name
   Set tbl = CreateObject("ADOX.Table")
   Set tbl.ParentCatalog = ct
   
   With tbl
     .Name = "LinkTableXLSheet"
     .properties("Jet OLEDB:Link Provider String") = "Excel 8.0;DATABASE=" & strLinkXL & ";HDR=Yes"
     'Note the use of $, it is necessary
     .properties("Jet OLEDB:Remote Table Name") = "Sheet2$"
     .properties("Jet OLEDB:Create Link") = True
   End With
   
   'Append the table to the tables collection
   ct.Tables.Append tbl
   Set tbl = Nothing

Further Information

How To Use ADO to Refresh/Create Linked Table for Password Secured Jet 4.0 Database: http://support.microsoft.com/kb/181408

607 Rating: 3.0/5 (2 votes cast)