Creating a Linked Table

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Linking a table from an external database allows you to read data, update and add data (in most cases), and create queries that use the table, in the same way as you would with a table native to the database. With the Microsoft Jet database engine, you can create links to tables in Access databases as well as other data formats supported by Microsoft Jet's installable ISAM drivers (Excel, dBase, Paradox, Exchange/Outlook, Lotus WKS, Text, and HTML) and ODBC drivers.

ADO and ADOX distinguish between tables that are linked from native Access database tables and installable ISAMs, and those linked by using ODBC drivers. If you use the ADO OpenSchema method and specify adSchemaTables as the QueryType argument to return a Recordset object that describes a database's tables, the TABLE_TYPE field returns "LINK" for linked Access tables and linked installable ISAM tables; however, it returns "PASS-THROUGH" for tables linked by using ODBC drivers. This is also true for the ADOX Table object's Type property. This is equivalent to using the DAO Attributes property with the read-only dbAttachedTable and adAttachedODBC constants.

Creating a Linked Access Table

To create a linked Access table by using ADOX and the Microsoft Jet 4.0 OLE DB Provider, you must specify the path to the external data source and the name of the external table. To do this, you need to specify the provider-specific Jet OLEDB:Link Datasource and Jet OLEDB:Remote Table Name properties. Additionally, to create a linked table for any kind of data source table by using ADOX, you must set the Jet OLEDB:Create Link property to True.

The following example shows how to link a table in another Access database.

Sub CreateLinkedAccessTable(strDBLinkFrom As String, _
         strDBLinkTo As String, _
         strLinkTbl As String, _
         strLinkTblAs As String)

   Dim catDB As ADOX.Catalog
   Dim tblLink As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open a Catalog on the database in which to create the link.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBLinkFrom

   Set tblLink = New ADOX.Table
   With tblLink
      ' Name the new Table and set its ParentCatalog property to the
      ' open Catalog to allow access to the Properties collection.
      .Name = strLinkTblAs
      Set .ParentCatalog = catDB

      ' Set the properties to create the link.
      .Properties("Jet OLEDB:Create Link") = True
      .Properties("Jet OLEDB:Link Datasource") = strDBLinkTo
      .Properties("Jet OLEDB:Remote Table Name") = strLinkTbl
   End With

   ' Append the table to the Tables collection.
   catDB.Tables.Append tblLink

   Set catDB = Nothing
End Sub

The CreateLinkedAccessTable procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.

Notice that it is not necessary to define the table structure for the linked table. The Microsoft Jet engine will automatically create the appropriate fields based on the definition of the table in the external data source.

Creating a Linked External Table

Even though ADO and ADOX distinguish between tables that are linked by using Jet I-ISAM drivers and tables linked from ODBC data sources, the method you use to create any linked external table is the same. To create a linked external table by using ADOX and the Microsoft Jet 4.0 OLE DB Provider, you must use the provider-specific Jet OLEDB:Link Provider String property to specify a Microsoft Jet connection string that tells how to connect to the external table. For I-ISAM tables, the connection string specifies the I-ISAM type and the path to the external data source. For ODBC tables, the connection string specifies the ODBC connection string that is necessary to connect to the data source. You then use the provider-specfic Jet OLEDB:Remote Table Name property to specify the name of the source table, which varies for the type of data you are linking to. For example, for an Excel worksheet, the source table can refer to an entire worksheet, a named range, or an unnamed range, whereas for Microsoft Exchange or Outlook, the source table refers to a folder in the mail storage. Just as when you create a linked Access table, you must also set the Jet OLEDB:Create Link property to True. The following code sample can be used to create a linked table for any I-ISAM or ODBC data source.

Sub CreateLinkedExternalTable(strTargetDB As String, _
         strProviderString As String, _
         strSourceTbl As String, _
         strLinkTblName As String)

   Dim catDB As ADOX.Catalog
   Dim tblLink As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open a Catalog on the database in which to create the link.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & strTargetDB

   Set tblLink = New ADOX.Table
   With tblLink
      ' Name the new Table and set its ParentCatalog property to the
      ' open Catalog to allow access to the Properties collection.
      .Name = strLinkTblAs
      Set .ParentCatalog = catDB

      ' Set the properties to create the link.
      .Properties("Jet OLEDB:Create Link") = True
      .Properties("Jet OLEDB:Link Provider String") = strProviderString
      .Properties("Jet OLEDB:Remote Table Name") = strLinkTbl
   End With

   ' Append the table to the Tables collection.
   catDB.Tables.Append tblLink

   Set catDB = Nothing
End Sub

The CreateLinkedExternalTable procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.

To use this procedure, you need to format the correct Jet connection string for the strProviderString argument to establish a connection to the external data source. You also need to specify the correct string for the strSourceTbl argument to identify the source table, such as the Exchange folder or Excel range you want to link to. The following sections describe how to create a Jet connection string and specify the source table for Excel, HTML, Microsoft Exchange or Outlook, and ODBC data sources.

Tip An easy way to determine the Jet connection string required to link an external data source is to create a linked table by using the Access user interface (File menu, Get External Data submenu, Link Tables command). Then open the linked table in Design view and display the Table Properties dialog box (View menu, Properties command). The Description property of a linked table shows the connection string used to link the table. You can display a long connection string by clicking in the Description property and then pressing SHIFT+F2.

Basic Jet I-ISAM Connection String Syntax

To specify the data source you want to open, all Jet I-ISAM drivers accept a connection string that uses the same basic syntax:

"identifier;source;options"

The identifier argument is a keyword that specifies the type of file being opened. This is the same string that is used with the Extended Properties property of the Connection object when you open an external data source. For example, Excel 8.0 is used when you open an Excel 97 or Excel 2000 workbook. For a list of Extended Properties property****settings for all database types supported by the Microsoft Jet 4.0 database engine, see "Extended Properties Property Settings" in ADOProperties.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM.

The source argument contains a drive letter or transfer protocol (either http:// or ftp://), a fully qualified path, and the file name of the file you want to open. The source argument always begins with DATABASE=.

The options argument is used to specify additional, optional arguments to the I-ISAM driver, such as a password, or to indicate whether the first row specified in a spreadsheet file contains column header information. Multiple arguments are separated by semicolons.

Linking Excel Workbooks

The connection string to open an Excel worksheet has this format

"identifier;DATABASE={drive**:\** | FTP:// | HTTP://}path**\filename.xls** [;HDR={Yes | No}]"

where identifier is the appropriate data source identifier for the version of Excel you want to open (see "Extended Properties Property Settings" in ADOProperties.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM for a complete list); drive, path and filename point to the specific data source file; and HDR indicates whether the first row contains headers (set to Yes to use the first row as field names, or set to No to use the first row as data).

The strSourceTblargument of the CreateLinkedExternalTable procedure specifies the data you want to link and passes that value to the Jet OLEDB:Remote Table Name property. When you link a Microsoft Excel 5.0, 7.0 (95), 8.0 (97), or 9.0 (2000) workbook, you can specify a subset of the available data. You can link a single worksheet, a named range anywhere in the workbook, or an unnamed range in a single worksheet. The following table lists the syntax you use to specify the desired subset of data.

To access this object	Use this syntax
Entire worksheet in a workbook file	sheetname$ where sheetname is the name of the worksheet. Important You must follow the worksheet name with a dollar sign ($). The dollar sign character appended to the sheet name tells the I-ISAM driver that you're referencing the entire sheet, rather than just a range on the sheet.
Named range of cells in a worksheet or workbook file	namedrange where namedrange is the name previously assigned to the range in Microsoft Excel.
Unnamed range of cells in a single worksheet in a workbook file	sheetname$range For example, to access cells A1 through D12 in a worksheet named 1996 Sales, you would use the following in the source argument: 1996 Sales$A1:D12.

For example, to use the CreateLinkedExternalTable procedure to create a table that is linked to data on a worksheet named "Products" in an Excel workbook named Products.xls and treat the first row as field names, you can use a line of code like this:

CreateLinkedExternalTable _
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "Excel 8.0;DATABASE=C:\Products.xls;HDR=YES", "Products$","LinkedXLS"

Linking HTML Tables

Hypertext Markup Language (HTML) is a standard for presenting information over an intranet or the Internet. HTML files are text files that contain tags that specify how information in the file is displayed. You can use the HTML table tags (<TD> and </TD>) to embed one or more tables in an HTML file. The Microsoft Jet HTML I-ISAM driver can access information in HTML files that is formatted as tabular data or data in HTML files that is formatted as list data.

The Microsoft Jet HTML I-ISAM driver reads the data in the HTML table and chooses the data type for the data in the table columns by interpreting the data contained in the table cells. For example, if any of the data in a column is text, the I-ISAM driver interprets the column data type as text, with a field size of 255 characters. If the data in a column is numeric, the I-ISAM driver interprets the column data type as Long or Double, depending on whether most of the values are integer or floating point. If the data in a column is a combination of numeric and text values, the column data type is interpreted as text.

You can force the Microsoft Jet HTML I-ISAM driver to interpret column data as a specific data type by creating a Schema.ini file that contains information about the data type for each column of data. For more information about Schema.ini files, see Microsoft Access Help.

The connection string to open an HTML table has this format

"HTML Import;DATABASE={drive**:\** | FTP:// | HTTP://}path**\filename [;HDR={Yes | No}]"**

where drive is the letter of the disk drive on a local computer; path and filename point to the data source file; and HDR indicates whether the first row contains headers, instead of data. The path node delimiters can be either backslashes (\) or forward slashes (/).

The strSourceTblargument of the CreateLinkedIISAMTable procedure specifies the source table you want to link. For an HTML table, the table is identified by the name surrounded by <CAPTION> tags (if they exist) within the HTML file that contains the data you want to link. If the table does not have a caption, and it is the only table in the file, use the title of the HTML file to refer to the table. If more than one table exists and none of the tables has a caption, you refer to them sequentially as Table1, Table2, and so on. The I-ISAM driver interprets these references as the first unnamed table in the HTML file, the second unnamed table in the HTML file, and so on.

For example, to use the CreateLinkedExternalTable procedure to create a table that is linked to a table named "Sales" in Sales_1.html and treat the first row in the table as field names, you can use a line of code like this:

CreateLinkedExternalTable _
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "HTML Import;DATABASE=C:\Sales_1.html;HDR=YES","Sales","LinkedHTML"

Linking Outlook/Exchange Folders

The Microsoft Exchange I-ISAM driver lets you access Microsoft Exchange and Outlook data stored remotely on a Microsoft Exchange server or data stored locally in offline folder (.ost), personal folder (.pst), or personal address book (.pab) files.

Using the Microsoft Exchange I-ISAM driver to access Microsoft Exchange and Outlook data is primarily useful for reading information from message folders, public folders, address books, and other items. You can't modify any existing items. And, while it is possible to write new items to Microsoft Exchange and Outlook message stores, not all fields are available or updatable, which limits your ability to create some items.

The following table contains a description of the required and optional elements used to create the identifier, source, and options portions of a connection string for a Microsoft Exchange or Outlook external data source.

Element	Description
Identifier	Exchange 4.0 (required). The setting works with Microsoft Exchange 4.x, 5.0, and Outlook data.
Source table path	MAPILEVEL=storage\|folderpath (required). The storage entry is the exact name of a mailbox on a server, a personal folder, or public folder as it appears in the Outlook Folder List; and folderpath is the path to the folder immediately above the folder you want to connect to. The storage entry and the pipe character (\|) are always required. The folders entry is required to access folders below the top-level of folders within storage. When you are listing nested folders, separate each folder name with a backslash (\) (for example, "Mailbox – Pat Smith\|Inbox\Big Project").
Source table type	TABLETYPE=0 (for folders) (default) TABLETYPE=1 (for address books; required)
Database name	DATABASE=path (required). The path entry is the fully resolved path and file name to a Microsoft Access database (.mdb) in which to store system tables used by the driver (usually the current database).
Profile name	PROFILE=profile (optional; if not specified, the default profile is used). The profile entry is the name of the Microsoft Exchange or Outlook profile to use.
Password	PWD=password (optional; not required if your network logon password is passed to your Microsoft Exchange server). The password entry is the Microsoft Exchange or Outlook logon password.

Strung together, the connection string to open a Microsoft Exchange or Outlook data source has this format:

"Exchange 4.0;MAPILEVEL= storage**|folderpath;TABLETYPE={0|1};*DATABASE=path;[PROFILE=profile;PWD=password;]"*

The strSourceTblargument of the CreateLinkedIISAMTable procedure specifies the source table you want to link. For a Microsoft Exchange or Outlook item, this is the name of the folder you want to connect to as it appears in the Outlook Folder List.

For example, to use the CreateLinkedExternalTable procedure to first create a table that is linked to a folder named "Big Project" that is a subfolder of the Inbox folder, and then name the linked table "LinkedExchange," you can use a line of code like this:

CreateLinkedExternalTable _
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "Exchange 4.0;MAPILEVEL=Mailbox - Pat Smith|Inbox;TABLETYPE=0;" & _ 
   "DATABASE=C:\Program Files\Microsoft Office\Office\Samples\ _
               Northwind.mdb;" _
   ,"Big Project","LinkedExchange"

Linking ODBC Tables

The connection string to open an ODBC table has this format:

"ODBC;connectstring"

where connectstring is the connection string required to connect to an ODBC data source. For example, the ODBC connection string to connect to the Pubs sample database on SQL Server might look something like this:

"DSN=Publishers;;UID=sa;PWD=;DATABASE=pubs"

Other ODBC data sources will have different formats. A simple way to create an ODBC connection string is to create an SQL pass-through query in Access. Clicking the Build button of the ODBCConnectStr property in the Query Properties dialog box for an SQL pass-through query starts a wizard that walks you through creating an ODBC connection string. For more information about creating an SQL pass-through query, search Microsoft Access Help.

The strSourceTblargument of the CreateLinkedExternalTable procedure specifies the source table you want to link to. For an ODBC table, this is the name of the table in the database on the ODBC server you want to link to.

For example, to use the CreateLinkedExternalTable procedure to create a table that is linked to the table named "dbo.Authors" in the Pubs sample database on SQL Server, and name the linked table "LinkedODBC," you can use a line of code like this:

CreateLinkedExternalTable _
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "ODBC;DSN=Publishers;UID=sa;PWD=;DATABASE=pubs;", _
   "dbo.Authors","LinkedODBC"

The user ID and password from the connection string for a linked ODBC table aren't saved by default, which means users will be prompted for this information whenever they (or your code) open the table. If you want to save the user ID and password as part of the connection string, set the provider-specific Jet OLEDB:Cache Link Name/Password property to True. In DAO, this is equivalent to adding the dbAttachSavePWD constant to the table's Attributes property.

For a list of all the provider-specific properties that can be used when you create linked tables, see ADOProperties.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM.

Refreshing Links for Linked Tables

If the data source for a linked table is renamed or moved, you need to refresh the connection information used to establish the link. To refresh the link, update the connection string for the table by using the provider-specific Jet OLEDB:Link Datasource property, and then reestablish the link by setting the provider-specific Jet OLEDB:Create Link property to True. The following code example shows how to refresh the links for tables linked to another Access database.

Sub RefreshLinks(strDBLinkFrom As String, _
         strDBLinkSource As String)
   Dim catDB As ADOX.Catalog
   Dim tblLink As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open a catalog on the database in which to refresh links.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBLinkFrom

   For Each tblLink In catDB.Tables
      ' Check to make sure table is a linked table.
      If tblLink.Type = "LINK" Then
         tblLink.Properties("Jet OLEDB:Link Datasource") = strDBLinkSource
         tblLink.Properties("Jet OLEDB:Create Link") = True
      End If
   Next

   catDB = Nothing
End Sub

The RefreshLinks procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.