Linking Tables by Using DAO

To link to an external table by using DAO, you must create and append a TableDef object to a database’s TableDefs collection that specifies the following items:

• The name you want displayed for the linked table in the local database
  
  
• The name of the table as it exists in its original location
  
  
• The database format and location of the external table, and any additional information required to open the database where the external table resides

The following code opens a specified database and creates a new table. It then opens a second specified database and creates a link to the new table in the first database. Note that this code works only when both databases are Microsoft Jet databases. In this example, strDbPathBase is the path to the database containing the base table, strBaseTableName is the name of the new base table, strFieldName is the name of the field in the base table, intType is a constant specifying what type of field to create, strDbPathLinked is the path to the linked table, and strLinkedTableName is the name of the new linked table:

Dim dbsBase As Database, dbsLinked As Database
Dim tdfBase As TableDef, tdfLinked As TableDef, fld As Field
	
' Open the first database and create a new TableDef object.
Set dbsBase = OpenDatabase(strDbPathBase)
Set tdfBase = dbsBase.CreateTableDef(strBaseTableName)
Set fld = tdfBase.CreateField(strFieldName, intType)
tdfBase.Fields.Append fld
dbsBase.TableDefs.Append tdfBase
dbsBase.Close
Set dbsBase = Nothing

' Open the second database and create a new TableDef object.
Set dbsLinked = OpenDatabase(strDbPathLinked)
Set tdfLinked = dbsLinked.CreateTableDef(strLinkedTableName)

' Create a link to the new table in first database.
tdfLinked.SourceTableName = strBaseTableName
tdfLinked.Connect = ";DATABASE=" & strDbPathBase
dbsLinked.TableDefs.Append tdfLinked
dbsLinked.Close
dbsLinked = Nothing

Depending on the database format you are linking to, the contents of the Connect property of the new TableDef object may change. The Connect property in this example begins with:

;DATABASE=

Note that nothing precedes the semicolon. This is the default value used when you are linking to tables in Microsoft Jet-format databases. If you are linking to a table in another format, such as FoxPro, you must specify the name of the installable ISAM driver that Microsoft Jet should use. For example:

FoxPro 3.0;DATABASE=C:\JetBook\Samples\FoxTables

The connection to an external ODBC source may require more information:

ODBC;DSN=SuperDb;UID=sa;DATABASE=NorthwindSQL;TABLE=dbo.Products

In the Microsoft Access user interface, you can view the connection information used to link a table by opening the table in Design view, and then viewing the text in the table’s Description property.

See Also For more information about how to specify different database formats by using the Connect property, search the DAO Help index for “Connect property.”