Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Link External Tables with VBA

Introduction.

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

The Steps to follow

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 the Source Table Name.

The VBA Functions.

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

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 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 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 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 Table location. The first line is the same as above for this example also.

Earlier Post Link References:

Share:

5 comments:

  1. Excellent. Thanks for posting this. Very helpful to me. Tom in Minneapolis.

    ReplyDelete
  2. THANK YOU! This sentence resolved a lot of frustration: "The $ symbol is necessary with the (Excel) Worksheet name." Much appreciated.

    ReplyDelete
  3. I linked a table to an Excel spreadsheet. Then when the Excel spreadsheet is changed, an Access query/macro updates the table (update query). However, when creating the same spreadsheet (basically a new Excel speadsheet because the values are different) and running the query/macro again which updates the table again, all the values of the previous changes are wiped out. How can I keep the previous changes or replace the previous values when the unique ID is the same? Should I name each Excel spreadsheet a different name each time?

    ReplyDelete
  4. When you Link an Excel Worksheet with specific Sheet Name (or a Named Range) from a WorkBook MS-Access looks for this specific names for maintaining the link correctly. Even if you replace it with a different Workbook with the same Worksheet Name, Workbook Name and earlier data structure but with fresh data the link to access remains intact. You can even copy and paste the data into the earlier worksheet area replacing old data. You don't have to change the name of the worksheet/workbook etc.

    Now the Question of updating data on the table. Here there is something not clear to me from what you have stated above. If you are trying to update only certain records of the table while leaving others, which were updated last time untouched, then you have to device a method to identify records for updating into the table and to leave others untouched.

    For example: You can introduce a Date field in the Access Table and update this field with the current date while updating data from excel table. Next time you can setup criteria to exclude these records based on the last update date and update others etc.

    In either case it is better to create a Make-Table Query, to create a copy of the Access Table (Backup), and add it to the Macro before the update Query line. So that if you find that something went wrong then you can restore the data from this copy of the Table.

    You may create Queries on Linked Excel Table and on Access Table on UniqueIDs (as you have stated) and link them in a common query on UniqueID and check the output records or count of records through VBA (=DCount("*","QueryName") to check whether any matching records are there in both tables which likely to overwrite the earlier updated values before actually running the update Query.

    Regards,
    a.p.r. pillai

    ReplyDelete
  5. [...] How to link external database table in Access VBA? See the following site:- http://msaccesstips.com/2008/09/link...bles-with-vba/ [...]

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code