Introduction.
We all know how to link a Table from external data sources manually.
- Highlight Get External Data from File Menu.
- Select Link Tables from the displayed options.
- Select the file type (dBase, Excel, etc.) in the Files of Type control.
- Find the location of the File and select it.
- 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:
- Create a temporary Table Definition (Tabledef) without any Field Definitions in the Current Database.
- Load the Connect Property of tabledef. with Connection String Value
- Link the external Table to the temporary Table definition (Tabledef)
- Add the temporary Table definition to the Tabledefs Group.
- 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.
- 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
- Excel Range Name Branch Names will be used as Table location. The first line is the same as above for this example also.
Excellent. Thanks for posting this. Very helpful to me. Tom in Minneapolis.
ReplyDeleteTHANK YOU! This sentence resolved a lot of frustration: "The $ symbol is necessary with the (Excel) Worksheet name." Much appreciated.
ReplyDeleteI 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?
ReplyDeleteWhen 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.
ReplyDeleteNow 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
[...] How to link external database table in Access VBA? See the following site:- http://msaccesstips.com/2008/09/link...bles-with-vba/ [...]
ReplyDelete