Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Ads

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

Trending

Search

Infolinks Text Ads

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Activity Dates and Quarterly Reports

There are four Quarters in a Year: Jan - Mar = 1st Quarter Apr - Jun = 2nd Jul - Sep = 3rd Oct - Dec = 4th First three months of the yea...

Labels

Blog Archive

Recent Posts