Microsoft Jet databases can contain two types of tables. The first type is a local table, in which the definition and data are stored within the database. The second type is a linked table in which the table resides in an external database, but a link along with a copy of the table's definition is stored in the database.
Creating Local Tables
The following example creates a new local table named "Contacts."
DAO
Sub DAOCreateTable()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
' Create a new TableDef object.
Set tbl = db.CreateTableDef("Contacts")
With tbl
' Create fields and append them to the new TableDef object.
' This must be done before appending the TableDef object to
' the TableDefs collection of the Database.
.Fields.Append .CreateField("ContactName", dbText)
.Fields.Append .CreateField("ContactTitle", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)
.Fields("Notes").Required = False
End With
' Add the new table to the database.
db.TableDefs.Append tbl
db.Close
End Sub
ADOX
Sub ADOCreateTable()
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=.\nn.mdb;"
' Create a new Table object.
With tbl
.Name = "Contacts"
' Create fields and append them to the new Table
' object. This must be done before appending the
' Table object to the Tables collection of the
' Catalog.
.Columns.Append "ContactName", adVarWChar
.Columns.Append "ContactTitle", adVarWChar
.Columns.Append "Phone", adVarWChar
.Columns.Append "Notes", adLongVarWChar
.Columns("Notes").Attributes = adColNullable
End With
' Add the new table to the database.
cat.Tables.Append tbl
Set cat = Nothing
End Sub
The process for creating a table using DAO or ADOX is the same. First, create the object (TableDef or Table), append the columns (Field or Column objects), and finally append the table to the collection. Though the process is the same, the syntax is slightly different.
With ADOX, it is not necessary to use a "create" method to create the column before appending it to the collection. The Append method can be used to both create and append the column.
You'll also notice the data type names for the columns are different between DAO and ADOX. The following table shows how the DAO data types that apply to Microsoft Jet databases map to the ADO data types.
| DAO data type | ADO data type |
|---|---|
| dbBinary | adBinary |
| dbBoolean | adBoolean |
| dbByte | adUnsignedTinyInt |
| dbCurrency | adCurrency |
| dbDate | adDate |
| dbDecimal | adNumeric |
| dbDouble | adDouble |
| dbGUID | adGUID |
| dbInteger | adSmallInt |
| dbLong | adInteger |
| dbLongBinary | adLongVarBinary |
| dbMemo | adLongVarWChar |
| dbSingle | adSingle |
| dbText | adVarWChar |
Though not shown in this example, there are a number of other attributes of a table or column that you can set when creating the table or column, using the DAO Attributes property. The table below shows how these attributes map to ADO and Microsoft Jet Provider–specific properties.
| DAO TableDef Property | Value | ADOX Table Property | Value |
|---|---|---|---|
| Attributes | dbAttachExclusive | Jet OLEDB:Exclusive Link | True |
| Attributes | dbAttachSavePWD | Jet OLEDB:Cache Link Name/Password | True |
| Attributes | dbAttachedTable | Type | "LINK" |
| Attributes | dbAttachedODBC | Type | "PASS-THROUGH" |
| DAO Field Property | Value | ADOX Column Property | Value |
|---|---|---|---|
| Attributes | dbAutoIncrField | AutoIncrement | True |
| Attributes | dbFixedField | ColumnAttributes | adColFixed |
| Attributes | dbHyperlinkField | Jet OLEDB:Hyperlink | True |
| Attributes | dbSystemField | No equivalent | n/a |
| Attributes | dbUpdatableField | Attributes (Field Object) | adFldUpdatable |
| Attributes | dbVariableField | ColumnAttributes | Not adColFixed |
Creating a Linked Table
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(".\nn.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=.\nn.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: Microsoft Jet 4.0 OLE DB 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(".\nn.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=.\nn.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.
Modifying an Existing Table
Once a table is created, you may want to modify it to add or remove columns, change the validation rule or refresh the link for a linked table.
The following listings demonstrate how to add a new auto-increment column to an existing table.
DAO
Sub DAOCreateAutoIncrColumn()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
' Get the Contacts table
Set tbl = db.TableDefs("Contacts")
' Create the new auto increment column
Set fld = tbl.CreateField("ContactId", dbLong)
fld.Attributes = dbAutoIncrField
' Add the new table to the database.
tbl.Fields.Append fld
db.Close
End Sub
ADOX
Sub ADOCreateAutoIncrColumn()
Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
' Create the new auto increment column
With col
.Name = "ContactId"
.Type = adInteger
Set .ParentCatalog = cat
.Properties("AutoIncrement") = True
End With
' Append the column to the table
cat.Tables("Contacts").Columns.Append col
Set cat = Nothing
End Sub
In the ADOX example, notice that the ParentCatalog property of the Column object is set before the AutoIncrement property in the Properties collection is set to True. In order to access any property in the Properties collection, the Column object must be associated with a provider.
The next example shows how to update an existing linked table to refresh the link. This involves updating the connection string for the table and then resetting the Jet OLEDB:CreateLink property to tell Microsoft Jet to reestablish the link.
DAO
Sub DAORefreshLinks()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
For Each tbl In db.TableDefs
' Check to make sure table is a linked table.
If (tbl.Attributes And dbAttachedTable) Then
tbl.Connect = "MS Access;PWD=NewPassWord;" & _
"DATABASE=.\NewPubs.mdb"
tbl.RefreshLink
End If
Next
End Sub
ADOX
Sub ADORefreshLinks()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
For Each tbl In cat.Tables
' Check to make sure table is a linked table.
If tbl.Type = "LINK" Then
tbl.Properties("Jet OLEDB:Create Link") = False
tbl.Properties("Jet OLEDB:Link Provider String") = _
";pwd=NewPassWord"
tbl.Properties("Jet OLEDB:Link Datasource") = _
".\NewPubs.mdb"
tbl.Properties("Jet OLEDB:Create Link") = True
End If
Next
End Sub
Posts
