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.
Linking Tables via Jet and ADO
From Wiki
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
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.