Link External Tables with VBA

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

1. Highlight Get External Data from 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. Find the location of the File and select it.


5. Click 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 from H:\ to K:\ or anything else, MS-Access will have no difficulty in finding the linked Table. Otherwise you have to go for the Option Tools - ->Database Utilities - ->Linked Table Manager for refreshing the changed location reference of the table.


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


Here, we will try to 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.


We have to 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 with the Source Table Name.

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

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

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 to print five records of Orders table from Northwind.mdb database.


The LinkExternal() Program performs the five steps of actions 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 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 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 BranchNames will be used as Table location. The first line is same as above for this example also.

---

Column Chart and VBA
Working with Chart Object in VBA
Linking IBM AS400 Tables
Repairing Combacting Database with VBA
Database Open Close Event Alerts