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
Some Notes For C#
- //Reference Microsoft ADO Ext x.x for DDL and Security
- ADOX.Catalog cat = new ADOX.Catalog();
- ADOX.Table tbl = new ADOX.Table();
- String linkToDB = "Z:\\Docs\\Test.accdb";
- String linkInDB = "Z:\\Docs\\Test2.accdb";
- String cn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + linkInDB;
- cat.let_ActiveConnection(cn);
- tbl.ParentCatalog = cat;
- tbl.Name = "LinkTableDB";
- tbl.Properties["Jet OLEDB:Remote Table Name"].Value = "Table1";
- tbl.Properties["Jet OLEDB:Link Datasource"].Value = linkToDB;
- tbl.Properties["Jet OLEDB:Link Provider String"].Value = "MS Access";
- tbl.Properties["Jet OLEDB:Create Link"].Value = true;
- cat.Tables.Append (tbl);
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.