Working with Tables in Microsoft Access

 

David Shank
Microsoft Corporation

October 5, 2000

When I talk about working with tables in Microsoft Access, I always have to mention what the discussion is not about. This is not about working with the data contained in a table. Rather, it's about working with the tables themselves—in other words, creating, modifying, and linking external tables. (For information on working with the data in an Access database, see my columns from last January and February).

In previous versions of Microsoft Access, developers used the Microsoft Data Access Objects (DAO) object library to programmatically work with tables, and the DAO object library was referenced by default in a new Access database. In Microsoft Access 2000, you use the ActiveX Data Objects Extensions for DDL and Security (ADOX) object model to create and work with a table structure or to link to external tables. The ADOX object model contains objects, properties, and methods for creating, modifying, and viewing the structure of databases, tables, and queries. The ADOX object model is not referenced by default in a new Access database. You must set a reference to the ADOX object library before working with it using Visual Basic for Applications (VBA). (For a good comparison of DAO and ADO/ADOX, see Migrating from DAO to ADO.)

List the Tables in an Access Database

In ADOX, the Table object represents a table, and the Tables collection provides access to information about all tables in the database. You can use the Tables collection to list all tables within a database. However, the Tables collection may also contain Table objects that aren't actual tables in your Access database.

For example, a query that returns records but doesn't have parameters (what is known as a select query in Access) is considered a View object in ADOX, and is also included in the Tables collection. The ADOX Tables collection also contains linked tables and system tables. You can distinguish among different kinds of Table objects by using the Type property. The following table lists the possible string values returned for the Type property when you use ADO with the Microsoft Jet 4.0 OLE DB Provider.

Type Description
Access Table A Microsoft Access system table
Link A linked table from a non-ODBC data source
Pass-Through A linked table from an ODBC data source
System Table A Microsoft Jet system table
Table A standard table
View A query that has no parameters and returns records

In the following example, the ListAccessTables procedure prints to the VBA Immediate Window the names of all tables in the database contained in the strDBPath argument.

Sub ListAccessTables(strDBPath As String)
   Dim catDB As ADOX.Catalog
   Dim tblList As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open the catalog.
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=" & strDBPath

   ' Loop through all the tables, but not queries,
   ' and print their names and types.
   For Each tblList In catDB.Tables
      If tblList.Type <> "VIEW" Then 
         Debug.Print tblList.Name & vbTab & tblList.Type
      End If
   Next

   Set catDB = Nothing
End Sub

You can also use the ADO OpenSchema method to return a Recordset object that contains information about the tables in a database. When you use this method, you can restrict the list of tables returned on the basis of Type as well as Name properties. In general, it is faster to use the OpenSchema method rather than loop through the ADOX Tables collection, because ADOX must incur the overhead of creating objects for each element in the collection. The following procedure shows how to use the OpenSchema method to print the same information as in the previous example.

Sub ListAccessTables2(strDBPath)
   Dim cnnDB As ADODB.Connection
   Dim rstList As ADODB.Recordset

   Set cnnDB = New ADODB.Connection

   ' Open the connection.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   ' Open the tables schema rowset.
   Set rstList = cnnDB.OpenSchema(adSchemaTables)

   ' Loop through the results and print the
   ' names and types in the Immediate window.
   With rstList
      Do While Not .EOF
         If .Fields("TABLE_TYPE") <> "VIEW" Then
            Debug.Print .Fields("TABLE_NAME") & vbTab _
               & .Fields("TABLE_TYPE")
         End If
        .MoveNext
      Loop
   End With
   cnnDB.Close
   Set cnnDB = Nothing
End Sub

Create and Modify an Access Table

You can view, create, and modify the structure of an Access database by using ADOX objects, methods, and properties. The following sections provide details on how to do this. You can use a transaction to "wrap" a set of changes you make to the structure of a database; by using the transaction as a wrapper around the changes, you ensure that all work is performed as a single unit.

Create a Table

To create a table you first create a Catalog object, then a new Table object. You use the Append method of the Table object’s Columns collection to add field definitions (Column objects) to the Columns collection. Finally, you append the Table object to the Tables collection of the Catalog object. It is not necessary to use the Create method to create Column objects for the field definitions before you append them to the Columns collection. The Append method can be used both to create and to append the Column object. The following example uses these objects to create a Contacts table in the database specified in the strDBPath argument.

Sub CreateTable(strDBPath As String)
   Dim catDB As ADOX.Catalog
   Dim tblNew As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open the catalog.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=" & strDBPath

   Set tblNew = New ADOX.Table
   ' Create a new Table object.
   With tblNew
      .Name = "Contacts"
      ' Create fields and append them to the 
      ' Columns collection of the new Table object.
      With .Columns
         .Append "FirstName", adVarWChar
         .Append "LastName", adVarWChar
         .Append "Phone", adVarWChar
         .Append "Notes", adLongVarWChar
      End With
   End With

   ' Add the new Table to the Tables collection of the database.
   catDB.Tables.Append tblNew

   Set catDB = Nothing
End Sub

Note: The data type names for ADOX fields can be different from those used in DAO. Consult the ADOX object model documentation for more information on field data types.

Set Field Attributes and Properties

In addition to specifying the data type for a field, you may also wish to specify other field attributes, such as whether the field is auto-incrementing (the AutoNumber data type in the Access user interface) or whether it will be used to store active hyperlinks (the Hyperlink data type in the Access user interface). When you create an auto-incrementing or hyperlink field by using DAO, you add the appropriate constant to the field's Attributes property. To create the same fields in ADOX, you set the appropriate property in the Properties collection of the Column object that is used to create the field.

The following example shows how to create an auto-incrementing field with ADOX by setting the field's AutoIncrement property to True.

Sub CreateAutoNumberField(strDBPath As String)
   Dim catDB As ADOX.Catalog
   Dim tbl As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open the catalog.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=" & strDBPath

   Set tbl = New ADOX.Table
   With tbl
      .Name = "Contacts"
      Set .ParentCatalog = catDB
      ' Create fields and append them to the
      ' Columns collection of the new Table object.
      With .Columns
         .Append "ContactId", adInteger
         ' Make the ContactId field auto-incrementing.
         .Item("ContactId").Properties("AutoIncrement") = True
         .Append "CustomerID", adVarWChar
         .Append "FirstName", adVarWChar
         .Append "LastName", adVarWChar
         .Append "Phone", adVarWChar, 20
         .Append "Notes", adLongVarWChar
      End With
   End With

   ' Add the new Table to the Tables collection of the database.
   catDB.Tables.Append tbl

   Set catDB = Nothing
End Sub

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 Sub

Notice 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 Sub

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.

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.

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"

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"

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=user_name;PWD= password;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=user_name;PWD=" _   
      "password;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 Sub

Where to Get More Info

The techniques and technologies discussed here should give you lots of ideas for working with Access tables using ADOX. For additional information, check out the following resources:

  • For information on migrating from DAO to ADO and ADOX, see Migrating from DAO to ADO.
  • For information on working with the data access components of an Office Solution, see Microsoft Access 2000: Data Access Models.
  • As always, check in regularly at the Office Developer Center (http://msdn.microsoft.com/office for information and technical articles on Office solution development.

David Shank is a programmer/writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native Northwesterners who still lives in the Northwest.