Link External Tables with VBA

Introduction.

We all know how to link to a table from external data sources manually.

1. Highlight Get External Data from the File Menu.

2. Select Link Tables from the displayed options.

3. Select the file type: dBase, Excel, etc., in the Files of Type control.

4. Track the location of the file and select it.

5. Click the link to attach the selected table to the Current Database.

If you are linking an external table from a Network Location, use the UNC (Universal Naming Conventions) type location reference (like \\hosfs03\accounts\myDatabase\...), rather than using a mapped drive location reference like H:\MyDatabase. 

You can even use your Local Drive's share name in this manner. 

\\yourPCName\C$\Databases\myDatabase.mdb

This method ensures that even if the drive mapping changes—for example, from **H:\** to **K:\** or any other letter—MS Access can still locate the linked tables without any issues. Otherwise, you would need to manually update the table locations using **Tools → Database Utilities → Linked Table Manager** to refresh the changed path references.

We have already seen that we can work with external tables without linking them permanently to the current database.

Here, we will link external Tables using VBA to the Current Database. After linking the table, we will print the contents of five records into the Debug Window and delete the link.

The Steps to follow

We will go through the following steps to link a Table to a Database with VBA:

1. Create a temporary Table Definition (Tabledef) without any Field Definitions in the Current Database.

2. Load the Connect Property of tabledef. with Connection String Value

3. Link the external Table to the temporary Table definition (Tabledef)

4. Add the temporary Table definition to the Tabledefs Group.

5. Rename the temporary Table to match the Source Table Name.

The VBA Functions.

We will write two VBA Functions for our examples. Copy and paste the following VBA code into a Global Module of your MS-Access Database and save it:

Public Function LinkMain()
Dim strConnection As String
Dim sourceTable As String

strConnection = ";DATABASE=D:\Program Files\Microsoft office\Office\Samples\Northwind.mdb;TABLE=Orders"

sourceTable = "Orders" 'Access Table Name

LinkExternal strConnection, sourceTable

End Function

Function LinkExternal(ByVal conString As String, sourceTable As String)
Dim db As Database, i As Integer, j As Integer
Dim linktbldef As TableDef, rst As Recordset

Set db = CurrentDb
Set linktbldef = db.CreateTableDef("tmptable") 'create temporary table definition

linktbldef.Connect = conString 'set the connection string
linktbldef.SourceTableName = sourceTable 'attach the source table
db.TableDefs.Append linktbldef 'add the table definition to the group
db.TableDefs.Refresh 'refresh the tabledefinitions

linktbldef.NAME = sourceTable 'rename the tmptable to original source table name

'open the recordset and print 5 records in the debug window
Set rst = db.OpenRecordset(sourceTable, dbOpenDynaset)
i = 0
Do While i < 5 And Not rst.EOF
  For j = 0 To rst.Fields.Count - 1
     Debug.Print rst.Fields(j).Value,
  Next: Debug.Print
  rst.MoveNext
  i = i + 1
Loop
rst.Close

db.TableDefs.Delete sourceTable 'remove to stay the table linked
db.Close
Set rst = Nothing
Set linktbldef = Nothing
Set db = Nothing

End Function

How it works.

The first program, LinkMain(), calls the LinkExternal() Sub Routine with strConnection and SourceTable name as parameters. Northwind.mdb sample database and Orders Table are passed as parameters. Open the Debug Window (Immediate Window) by pressing Ctrl+G. Click anywhere within the LinkMain() program and press F5 to run the code and print five records of the Orders table from the Northwind.mdb database.

The LinkExternal() Program performs the five steps of action explained above.

Replace the strConnection and sourceTable with the following sample values for opening a dBase Table:

strConnection = "dBase IV;HDR=NO;IMEX=2;DATABASE=D:\msaccesstips" sourceTable = "Branches" 'Access Table Name

Tip: If you don't have a dBase Table to try the Code, then export a Table from MS-Access into the dBase format and run the Code with changes.

Change the Database Folder name and the Table name with your own dBase Folder and Table names.

For Excel-based Tables, two methods are given below.

1. Uses Worksheet Reference (Sheet1$) as the source Table location. The $ symbol is necessary with the Worksheet name.:

   strConnection = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=D:\msaccesstips\Branch.xls" sourceTable = "Sheet1$" 'Excel Sheet Name Reference
   

   The topmost row contents of the table area will be used as Field Names.

   strConnection = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=D:\msaccesstips\Branch.xls" sourceTable = "BranchNames" 'Excel Range Name Reference
   

2. Excel Range Name, Branch Names will be used as the Table location. The first line is the same as above for this example, also.

Earlier Post Link References:

• Column Chart and VBA
• Working with the Chart Object in VBA
• Linking IBM AS400 Tables
• Repairing a Compacting Database with VBA
• Database Open, Close Event Alerts