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

LessThanDot christmas Logo

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 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.

  1. 'Reference Microsoft ADO Ext x.x for DDL and Security
  2.     Dim cn 'As ADODB.Connection
  3.     Dim ct 'As ADOX.Catalog
  4.     Dim tbl 'As ADOX.Table
  5.    
  6.     Dim strLinkXL 'As String
  7.     Dim strLinkMDB 'As String
  8.     Dim strMDB 'As String
  9.    
  10.     strLinkXL = "C:\Docs\LTD.xls"
  11.     strLinkMDB = "C:\Docs\db1.mdb"
  12.     strMDB = "C:\Docs\LTD.mdb"
  13.    
  14.     'Create Link...
  15.     Set cn = CreateObject("ADODB.Connection")
  16.     cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  17.            "Data Source=" & strMDB & ";" & _
  18.            "Persist Security Info=False"
  19.    
  20.     Set ct = CreateObject("ADOX.Catalog")
  21.     Set ct.ActiveConnection = cn
  22.    
  23.     Set tbl = CreateObject("ADOX.Table")
  24.     Set tbl.ParentCatalog = ct
  25.    
  26.     '1. Link MDB
  27.    
  28.     With tbl
  29.       'What the link table will be called
  30.       .Name = "LinkTableMDB"
  31.       'Name of the table to link
  32.       .properties("Jet OLEDB:Remote Table Name") = "Table1"
  33.       .properties("Jet OLEDB:Link Datasource") = strLinkMDB
  34.       .properties("Jet OLEDB:Link Provider String") = "MS Access"
  35.       .properties("Jet OLEDB:Create Link") = True
  36.     End With
  37.    
  38.     'Append the table to the tables collection
  39.     ct.Tables.Append tbl
  40.     Set tbl = Nothing
  41.    
  42.     '2. Link Excel using named range
  43.     Set tbl = CreateObject("ADOX.Table")
  44.     Set tbl.ParentCatalog = ct
  45.    
  46.     With tbl
  47.       .Name = "LinkTableXLRange"
  48.       .properties("Jet OLEDB:Link Provider String") = "Excel 8.0;DATABASE=" & strLinkXL & ";HDR=Yes"
  49.       'The named range
  50.       .properties("Jet OLEDB:Remote Table Name") = "Data_Range"
  51.       .properties("Jet OLEDB:Create Link") = True
  52.     End With
  53.    
  54.     'Append the table to the tables collection
  55.     ct.Tables.Append tbl
  56.     Set tbl = Nothing
  57.    
  58.     '3. Link Excel by sheet name
  59.     Set tbl = CreateObject("ADOX.Table")
  60.     Set tbl.ParentCatalog = ct
  61.    
  62.     With tbl
  63.       .Name = "LinkTableXLSheet"
  64.       .properties("Jet OLEDB:Link Provider String") = "Excel 8.0;DATABASE=" & strLinkXL & ";HDR=Yes"
  65.       'Note the use of $, it is necessary
  66.       .properties("Jet OLEDB:Remote Table Name") = "Sheet2$"
  67.       .properties("Jet OLEDB:Create Link") = True
  68.     End With
  69.    
  70.     'Append the table to the tables collection
  71.     ct.Tables.Append tbl
  72.     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#

  1. //Reference Microsoft ADO Ext x.x for DDL and Security
  2. ADOX.Catalog cat = new ADOX.Catalog();
  3. ADOX.Table tbl = new ADOX.Table();
  4.  
  5.  
  6. String linkToDB = "Z:\\Docs\\Test.accdb";
  7. String linkInDB = "Z:\\Docs\\Test2.accdb";
  8.  
  9. String cn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + linkInDB;
  10.  
  11. cat.let_ActiveConnection(cn);
  12.  
  13. tbl.ParentCatalog = cat;
  14. tbl.Name = "LinkTableDB";
  15. tbl.Properties["Jet OLEDB:Remote Table Name"].Value  = "Table1";
  16. tbl.Properties["Jet OLEDB:Link Datasource"].Value = linkToDB;
  17. tbl.Properties["Jet OLEDB:Link Provider String"].Value = "MS Access";
  18. tbl.Properties["Jet OLEDB:Create Link"].Value = true;
  19.  
  20. cat.Tables.Append (tbl);

607 Rating: 2.9/5 (19 votes cast)