Linking (also known as attaching) a table from
an external database allows you to read data, update and add data (in most
cases), and create queries using the table in the same way as you would with a
table native to the database.
With Microsoft Jet you can create links to
Microsoft Jet data, ISAM data (Text, FoxPro, dBASE, etc.), and ODBC data.
Tables that are linked through ODBC are sometimes called pass-through tables.
The following listings demonstrate how to create
a table that is linked to a table in another Microsoft Jet database.
DAO
Sub DAOCreateAttachedJetTable()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create a new TableDef object.
Set tbl = db.CreateTableDef("Authors")
' Set the properties to create the link
tbl.Connect = ";DATABASE=.\Pubs.mdb;pwd=password;"
tbl.SourceTableName = "authors"
' Add the new table to the database.
db.TableDefs.Append tbl
db.Close
End Sub
ADOX
Sub ADOCreateAttachedJetTable()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Set the name and target catalog for the table
tbl.Name = "Authors"
Set tbl.ParentCatalog = cat
' Set the properties to create the link
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = ".\Pubs.mdb"
tbl.Properties("Jet OLEDB:Link Provider String") = ";Pwd=password"
tbl.Properties("Jet OLEDB:Remote Table Name") = "authors"
' Append the table to the collection
cat.Tables.Append tbl
Set cat = Nothing
End Sub
To create a linked table, you must specify the
external data source and the name of the external table. With DAO, the Connect and SourceTableName
properties are used to specify this information. With ADOX, several Microsoft
Jet provider-specific properties are used to create the link. When referencing
the Table object's Properties collection
prior to appending the Table to the Tables
collection, you must first set the ParentCatalog property.
This is necessary so ADOX knows from which OLE DB provider to receive the
property information. See the section, "Appendix B: Properties
Reference" for more information about the properties that are available in
the Table object's Properties collection
when using the Microsoft Jet Provider.
With ADOX, the Jet OLEDB:Link
Datasource property contains only the file and pathname for the
database. It does not contain the "database=;" prefix nor is it used
to specify the database password or other connection options as the Connect property does in DAO. To specify other connection
options in ADOX code, use the Jet OLEDB:Link Provider String
property. You do not need to set this property unless you need to set extra
connection options. In the previous example, if the pubs.mdb was not secured
with a database password, you could omit the line of code that sets the Jet OLEDB:Link Provider String property.
When used for a user ID or a password, the value
for this property follows the syntax for connection strings used for external
data. The syntax is given in the Microsoft Jet Database
Engine Programmer's Guide. Specifically, you must use "uid=;"
and "pwd=;" to set the user ID and password, respectively, and not
"User ID=;" or "Password=;".
Notice that when creating an attached table
using both DAO and ADOX it is not necessary to create columns on the table. The
Microsoft Jet database engine will automatically create the columns based on
the definition of the table in the external data source.
This next example shows how to create a table
that is linked to a table in an ODBC data source such as a Microsoft SQL Server
database.
DAO
Sub DAOCreateAttachedODBCTable()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Create a new TableDef object.
Set tbl = db.CreateTableDef("Titles")
' Set the properties to create the link
tbl.Connect = "ODBC;DSN=ADOPubs;UID=sa;PWD=;"
tbl.SourceTableName = "titles"
' Add the new table to the database.
db.TableDefs.Append tbl
db.Close
End Sub
ADOX
Sub ADOCreateAttachedODBCTable()
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Set the name and target catalog for the table
tbl.Name = "Titles"
Set tbl.ParentCatalog = cat
' Set the properties to create the link
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Provider String") = _
"ODBC;DSN=ADOPubs;UID=sa;PWD=;"
tbl.Properties("Jet OLEDB:Remote Table Name") = "titles"
' Append the table to the collection
cat.Tables.Append tbl
Set cat = Nothing
End Sub
Unlike DAO, which has a single Connect
property, ADOX with the Microsoft Jet Provider has a separate property that
specifies the connection string for tables attached through ODBC. When creating
tables attached through ODBC you may want to indicate that the password should
be saved as part of the connection string (it is not saved by default). With
ADOX, use the Jet OLEDB:Cache Link Name/Password property
to indicate that the password should be cached. This is equivalent to setting
the dbAttachSavePWD flag in the Table object's
Attributes property using DAO.
EmoticonEmoticon