Create a Linked Table 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 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.Create 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 set the provider-specific Jet OLEDB:Link Datasource and Jet OLEDB:Remote Table Name properties. 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 to 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 SubNotice that it is not necessary to define the table structure for the linked table. The Microsoft Jet database engine will automatically create the appropriate fields based on the definition of the table in the external data source.Create 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-specific 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, in a Microsoft Excel worksheet, the source table can refer to an entire worksheet, a named range, or an unnamed range. In 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 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 SubTo 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.
Basic Jet I-ISAM connection string syntax All Jet I-ISAM drivers accept a connection string that uses the same basic syntax to specify which data source to open:"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. 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. Semicolons separate multiple arguments. Link to an Excel workbook 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; 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 strSourceTbl argument 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.Object Syntax Worksheet sheetname$ Named Range rangename Unamed Range sheetname$rangeaddress For example, you can 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, where the first row is treated as field names.CreateLinkedExternalTable _ "C:\Program Files\Microsoft " _ & "Office\Office\Samples\Northwind.mdb", _ "Excel 8.0;DATABASE=C:\Products.xls;HDR=YES", _ "Products$","LinkedXLS"Link to an HTML table HTML files are text files that contain tags that specify how information in the file is displayed. You can use the HTML table tags (<TABLE> and </TABLE>) 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 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. 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 strSourceTbl argument 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, you can use the CreateLinkedExternalTable procedure to create a table that is linked to a table named "Sales" in Sales_1.html, in which you can treat the first row in the table as field names.CreateLinkedExternalTable _ "C:\Program Files\Microsoft " & "Office\Office\Samples\Northwind.mdb", _ "HTML Import;DATABASE=C:\Sales_1.html;HDR=YES", _ "Sales","LinkedHTML"Link to an Outlook or Exchange folder 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. However, you can't modify existing items. And, while it is possible to write new items to Microsoft Exchange and Outlook message stores, not all fields are available or updateable, which limits your ability to create some items.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 strSourceTbl argument 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, you can use the CreateLinkedExternalTable procedure to create a table that is linked to a folder named "Big Project" that is a subfolder of the Inbox folder, then you can name the linked table "LinkedExchange."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"
Link to 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 strSourceTbl argument 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, you can 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 you can name the linked table "LinkedODBC." 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. Refresh 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, 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 SubPRB: ADOX Creates Read Only Linked Tables to Access Databases Q276035HOWTO: Change Logon Information for Access Linked Tables at Run Time with ADOX Q297963
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 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.Create 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 set the provider-specific Jet OLEDB:Link Datasource and Jet OLEDB:Remote Table Name properties. 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 to 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 SubNotice that it is not necessary to define the table structure for the linked table. The Microsoft Jet database engine will automatically create the appropriate fields based on the definition of the table in the external data source.Create 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-specific 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, in a Microsoft Excel worksheet, the source table can refer to an entire worksheet, a named range, or an unnamed range. In 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 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 SubTo 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.
All Jet I-ISAM drivers accept a connection string that uses the same basic syntax to specify which data source to open:"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. 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. Semicolons separate multiple arguments. Link to an Excel workbook
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; 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 strSourceTbl argument 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.Object Syntax
Worksheet sheetname$
Named Range rangename
Unamed Range sheetname$rangeaddress
For example, you can 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, where the first row is treated as field names.CreateLinkedExternalTable _
"C:\Program Files\Microsoft " _
& "Office\Office\Samples\Northwind.mdb", _
"Excel 8.0;DATABASE=C:\Products.xls;HDR=YES", _
"Products$","LinkedXLS"Link to an HTML table
HTML files are text files that contain tags that specify how information in the file is displayed. You can use the HTML table tags (<TABLE> and </TABLE>) 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 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. 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 strSourceTbl argument 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, you can use the CreateLinkedExternalTable procedure to create a table that is linked to a table named "Sales" in Sales_1.html, in which you can treat the first row in the table as field names.CreateLinkedExternalTable _
"C:\Program Files\Microsoft "
& "Office\Office\Samples\Northwind.mdb", _
"HTML Import;DATABASE=C:\Sales_1.html;HDR=YES", _
"Sales","LinkedHTML"Link to an Outlook or Exchange folder
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. However, you can't modify existing items. And, while it is possible to write new items to Microsoft Exchange and Outlook message stores, not all fields are available or updateable, which limits your ability to create some items.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 strSourceTbl argument 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, you can use the CreateLinkedExternalTable procedure to create a table that is linked to a folder named "Big Project" that is a subfolder of the Inbox folder, then you can name the linked table "LinkedExchange."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"
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 strSourceTbl argument 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, you can 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 you can name the linked table "LinkedODBC." 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. Refresh 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, 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 SubPRB: ADOX Creates Read Only Linked Tables to Access Databases Q276035HOWTO: Change Logon Information for Access Linked Tables at Run Time with ADOX Q297963
1、通过ODBC可以连接很多类型的数据库；
2、用ACCESS的中的“文件”菜单里的“导入/连接外部数据”可以通过ODBC连接你需要的数据库；
3、在ACCESS“表”里就会有相应的表连接别的数据库；
4、在ACCESS“报表”里就可以引用它们了。写程序：
1、ACCESS支持ADO，你可以通过ADO连接其它类型的数据库。
2、ACCESS仍支持DAO，你也可以通过DAO连接某些类型的数据库。
3、ACCESS的帮助里有很多现成的程序，MSDN里也有。