Creating and Managing Tables Programmatically

Sometimes—perhaps even most of the time—you will create tables manually. However, on occasion you might find it convenient to create a table dynamically, such as when you need to persist intermediate results for reuse in a subsequent operation. Access 2000 lets you do this using ADO techniques as well as the more traditional Data Access Objects (DAO) techniques. This section describes how to create and manage tables using ADO techniques. Once you learn this approach, you'll be able to take advantage of upcoming innovations in Microsoft data access technology.

After creating a table programmatically, you will often want to populate it with data. Even if you do not create a table programmatically, populating it with data programmatically has appeal. You can use many different data sources for populating an Access table. This section explores using another Access table, an Excel table (as an example of an ISAM data source), and an ODBC data source. You'll also learn how to populate an Access table using OLE database (OLE DB) providers and the native Access DoCmd object, which makes it easy to link to remote data sources. Unlike using OLE DB, using DoCmd makes the linked data source available from the Database window.

Creating a Table

To add tables dynamically, all you need is ADO—the ADOX library, to be specific. Just as when you manually create a table in a database, when you create a table dynamically you must name it, add columns to it, and append it to the Tables collection.

The MakeLocalTable procedure below creates a table dynamically. It starts by declaring the Catalog object and the Table object. The Catalog object is a container for the database schema, including the Tables collection. Next, the procedure instantiates the Catalog and Table objects. It names the new table FamilyMembers2 and assigns four columns to it using four Append methods nested inside a With...End statement. Each Append method includes a column name, a constant specifying a data type, and a length argument, if appropriate. The three Text fields (those created by specifying the adVarWChar data type) each have a field size specification. The procedure ends by appending the completed table to the catalog's Tables collection and closing the Catalog object.

Sub MakeLocalTable() Dim cat1 As ADOX.Catalog Dim tbl1 As ADOX.Table 'Reference objects for table     Set cat1 = New Catalog     cat1.ActiveConnection = CurrentProject.Connection     Set tbl1 = New Table 'Name table and append columns.     With tbl1         .Name = "FamilyMembers2"         .Columns.Append "FamID", adInteger         .Columns.Append "Fname", adVarWChar, 20         .Columns.Append "Lname", adVarWChar, 25         .Columns.Append "Relation", adVarWChar, 30     End With 'Append new table to Tables collection 'and free catalog resource.     cat1.Tables.Append tbl1     Set cat1 = Nothing End Sub 

The following table lists the data type and subtypes represented by the ADOX library's DataTypeEnum class constants.

Column Type Constants and Their Manual Data Type Equivalents

Constant	Value	Manual Data Type
adBoolean	11	Yes/No
adCurrency	6	Currency
adDate	7	Date/Time
adDecimal	14	Number—Decimal
adDouble	5	Number—Double
adGuid	72	Number—Replication ID
adInteger	3	AutoNumber
adInteger	3	Number—Long Integer
adLongVarBinary	205	OLE Object
adLongVarWChar	203	Hyperlink
adLongVarWChar	203	Memo
adSingle	4	Number—Single
adSmallInt	2	Number—Integer
adUnSignedTinyInt	17	Number—Byte
adWChar	130	Text

The table shows a couple of interesting points. First, the Hyperlink data type is equivalent to the Memo data type from a programming perspective. Second, there is no distinct data type for AutoNumber fields in the ADOX library. If your code checks the Type property of an AutoNumber field, you get the value adInteger. This value does not reflect the dynamic nature of the AutoNumber data type. Jet 4, however, does have a distinct data type, Identity, that corresponds to the AutoNumber data type. (See Chapter 2 for more on using this data type.) Also, the "Creating Tables Manually" section earlier in this chapter mentions more keywords for programmatically managing AutoNumber fields.

Avoiding replacing a table

When you add a table to a database manually, it is easy to check whether a table name already exists. If you inadvertently try to save a table with the name of an existing table in the database, Access warns you and asks if you want to overwrite the existing table. When you create a table programmatically, however, Access VBA halts with a run-time error when a procedure attempts to create a new table that has the name of an existing table. Therefore, you need error trapping to handle this situation. There are at least a couple of approaches to this task. The one that makes the most sense depends on the frequency with which you will be creating new tables.

The MakeLocalTableErrCatcher procedure below uses a classic error trapping approach. First, it enables a custom error handling routine so that the program can manage errors. The On Error statement at the beginning of the procedure accomplishes this. Next, it attempts to create and append the same FamilyMembers2 table as in the MakeLocalTable procedure. If the table already exists in the catalog, the Access VBA generates an error (-2147217857) and transfers control to the TableErrCatcher error handling routine. The error trapping logic checks for the "already exists" error. If it detects that error, it deletes the existing table and returns control to the line that caused the error. This allows the program to save the new table and exit the procedure normally. If another error causes a visit to the error handling routine, the routine prints the error's number and description to the Immediate window before gracefully exiting the procedure. The procedure never ends abnormally with a system message.

Sub MakeLocalTableErrCatcher() On Error GoTo TableErrCatcher Dim cat1 As ADOX.Catalog Dim tbl1 As ADOX.Table 'Reference objects for table     Set cat1 = New Catalog     cat1.ActiveConnection = CurrentProject.Connection     Set tbl1 = New Table 'Name table and append columns.     With tbl1         .Name = "FamilyMembers2"         .Columns.Append "FamID", adInteger         .Columns.Append "Fname", adVarWChar, 20         .Columns.Append "Lname", adVarWChar, 25         .Columns.Append "Relation", adVarWChar, 30     End With 'Append new table to Tables collection 'and free catalog resource.     cat1.Tables.Append tbl1     Set cat1 = Nothing      'Exit the procedure. TableErrExit:     Exit Sub          TableErrCatcher: 'Trap "table already exits" error. 'Delete table and resume.     If Err.Number = -2147217857 Then         cat1.Tables.Delete "FamilyMembers2"         Resume     End If 'Print details for other errors and exit.     Debug.Print Err.Number, Err.Description     Resume TableErrExit End Sub 

Replacing a table

If the database application regularly creates the FamilyMembers2 table, the procedure can generate the "already exists" error nearly every time it runs. In this situation, your procedure will run faster if you try to delete the existing table before appending the new one. This generally avoids the need to process an error. You still need an error handler for when the table does not already exist or when another condition generates an error. The following procedure writes the error handler when you unconditionally delete a table with the same name as the one that you are about to append to the database.

Sub MakeLocalTableErrCatcher2() On Error GoTo TableErrCatcher Dim cat1 As ADOX.Catalog Dim tbl1 As ADOX.Table 'Reference objects for table     Set cat1 = New Catalog     cat1.ActiveConnection = CurrentProject.Connection     Set tbl1 = New Table 'Name table and append columns.     With tbl1         .Name = "FamilyMembers2"         .Columns.Append "FamID", adInteger         .Columns.Append "Fname", adVarWChar, 20         .Columns.Append "Lname", adVarWChar, 25         .Columns.Append "Relation", adVarWChar, 30     End With      'Delete the old table (if it is there). 'Append the new one, and free the resource.     cat1.Tables.Delete "FamilyMembers2"     cat1.Tables.Append tbl1     Set cat1 = Nothing      'Exit the procedure. TableErrExit:     Exit Sub      TableErrCatcher: 'Trap "object not in collection" error. 'Resume at next line.     If Err.Number = 3265 Then         Resume Next     End If 'Print details for other errors and exit.     Debug.Print Err.Number, Err.Description     Resume TableErrExit End Sub 

The 3265 error mentioned in MakeLocalTableErrCatcher2's error handling routine results when you attempt to delete an object that is not in the collection. MakeLocalTableErrCatcher2 simply traps the error and resumes after the line that caused it. Any other error causes the program to end gracefully with the only trace being the error number and description in the Immediate window.

Working with Indexes

You can also add primary keys, indexes, and relationships programmatically. You can define primary keys and indexes across a single field or multiple fields.

Creating a primary key

Adding a primary key or an index to a table is similar to adding a new table to a catalog. First, you create a context to which to add the index, including a catalog and a table. Second, you define the index properties. These can differ between indexes and primary keys. Third, you append a column to the index and then append the new index to the table. If an error occurs, for example if the index already exists, you must respond appropriately. The AddPK procedure below dynamically creates a primary key.

Sub AddPK() Dim cat1 As New ADOX.Catalog Dim tbl1 As New ADOX.Table Dim pk1 As New ADOX.Index 'Create a context for the new primary key.     cat1.ActiveConnection = CurrentProject.Connection     Set tbl1 = cat1.Tables("FamilyMembers2") 'Set the primary key properties.     With pk1         .Name = "MyPrimaryKey"         .PrimaryKey = True         .Unique = True         .IndexNulls = adIndexNullsDisallow     End With      'Append column to index and index to table.     pk1.Columns.Append "FamID"     tbl1.Indexes.Append pk1      'Free resources.     Set cat1 = Nothing End Sub 

The procedure begins by declaring and instantiating Catalog, Table, and Index objects. (You need all three objects to create a primary key.) Notice that there is no explicit object for a primary key. Next, the procedure sets the context for defining the new primary key. It sets the ActiveConnection property of the Catalog object to point the catalog at a specific database. Then it sets the table reference to a table within that database. This reference is the table to which your procedure will add the new primary key.

Next, the procedure sets four index properties. The first is the name of the primary key. It appears as an entry in the Index Name column in the table's Indexes window. The remaining three properties differentiate the primary key from a simple index. You should always set these properties as they appear in the AddPK procedure when you create a primary key.

The procedure then invokes two Append methods. The first one appends the FamID column from the FamilyMembers2 table to the index. The second one appends the index to the table. Finally, the procedure sets the Catalog object to Nothing, thereby freeing the resources used to create the primary key.

It is often desirable to have an AutoNumber, Long Integer, or Text field as a primary key. These can be faster than a multiple field primary key. However, at times a multiple field index makes sense in terms of uniquely defining records and in terms of how you will use the data from the table. When a multiple key index is appropriate, you simply append more than one column to the index before you append the index to the table.

In the preceding example, you can replace the line

pk1.Columns.Append "FamID" 

with the lines

pk1.Columns.Append "Lname" pk1.Columns.Append "Fname" pk1.Columns.Append "Relation" 

These lines define a primary key on three fields instead of just one. The AddPK procedure generates a primary key based on a single field; the top Indexes window in Figure 3-14 shows the result of calling AddPK. The bottom Indexes window shows the result of calling AddPK3, which is identical to AddPK except for the code replacement shown above. (Both procedures are in the database for Chapter 3 on the companion CD.) Because you can have only one primary key at a time, you must manually remove the primary key between calling the AddPK and AddPK3 procedures.

Figure 3-14. The Indexes windows after running the AddPK and AddPK3 procedures.

The AddPK and AddPK3 procedures can fail for any of several reasons. Two errors that the following AddPKErr procedure traps are an already existing primary key (-2147467259) and the table already being open (-2147217856). (As stated above, you cannot add a new primary key if one already exists; also, you cannot even modify the index structure if the table is open.)

Sub AddPKErr() On Error GoTo PKErr Dim cat1 As New ADOX.Catalog Dim tbl1 As New ADOX.Table Dim pk1 As New ADOX.Index Dim iNumber As Integer 'Create a context for the new primary key.     cat1.ActiveConnection = CurrentProject.Connection     Set tbl1 = cat1.Tables("FamilyMembers2") 'Set the primary key properties. 'The label (SetPKvariable) gives the procedure a 'recovery point from a previously existing primary key. SetPKvariable:     With pk1         .Name = "MyPrimaryKey"         .PrimaryKey = True         .Unique = True         .IndexNulls = adIndexNullsDisallow     End With 'Append column to index and index to table.     pk1.Columns.Append "FamID"     tbl1.Indexes.Append pk1 'Exit procedure. PKErrExit:     Set cat1 = Nothing     Exit Sub PKErr: 'Checks for table already in use     If Err.Number = -2147217856 Then         MsgBox "FamilyMembers2 currently in use.  This" & _             " operation requires the table to be closed." 'Checks for primary key already exists     ElseIf Err.Number = -2147467259 Then         For Each pk1 In tbl1.Indexes             If pk1.PrimaryKey = True Then                 tbl1.Indexes.Delete (iNumber)                 Resume SetPKvariable             End If             iNumber = iNumber + 1         Next pk1 'Traps for other errors     Else         MsgBox "Open Immediate window for Bug report"         Debug.Print Err.Number; Err.Description         Resume PKErrExit     End If End Sub 

With a few exceptions for error trapping, the AddPKErr procedure follows the logic of the AddPK procedure in its main segment. AddPKErr first enables an error handling routine with an On Error statement. The declaration area includes a new Dim statement for an Integer variable. The error trapping logic uses this variable as an index in the loop that iterates through the Indexes collection of the table.

The error trapping logic in AddPKErr starts at the PKErr label. An If...Then statement initially tests for whether the table is open. If it is, the routine displays a message box that explains the problem and offers a solution before gracefully exiting. The ElseIf clause detects the existence of a primary key. In this case, the routine enumerates the indexes in the table until it detects the one with its PrimaryKey attribute set to True. Then it deletes that index and returns control to the initial step for defining a new primary key. This is necessary since the error wipes out the previous settings. If the table is not open and no primary key exists, the routine writes the error number and its description to the Immediate window before exiting the procedure. A message box tells the user to view the Immediate window for the cause of the error.

Creating an index

Adding a simple index to a table is not much different from adding a primary key. The major difference is that you do not set the PrimaryKey property to True. (It is False by default.) The AddIdx procedure below has a design similar to that of AddPK. Aside from not setting the primary key property, the major difference is that it explicitly assigns a sort order for the index. By instructing the table to sort on the FamID column values in descending order, the procedure makes the table show the most recent records first (assuming that records with a higher FamID value are added after earlier ones). When assigning the sort order, you must specify which number column it applies to. The numbering for columns is zero-based.

Sub AddIdx() Dim cat1 As New ADOX.Catalog Dim tbl1 As New ADOX.Table Dim idx1 As New ADOX.Index 'Create a context for the new index.     cat1.ActiveConnection = CurrentProject.Connection     Set tbl1 = cat1.Tables("FamilyMembers2") 'Set the index properties.     With idx1         .Name = "LastIsFirst"         .Unique = True         .IndexNulls = adIndexNullsDisallow     End With 'Append column to index and set its sort order. 'Append new index to table.     idx1.Columns.Append "FamID"     idx1.Columns(0).SortOrder = adSortDescending     tbl1.Indexes.Append idx1      'Free resources.     Set cat1 = Nothing End Sub 

Dynamically Populating a Table

After you design a table and set its indexes, you populate it with values using one of two approaches. When the data for the table resides in another table, you can define recordsets on the data source table and the new table. Then you simply navigate through both recordsets in synchrony as you copy records to the new table using the AddNew method. (This approach loops through a recordset, so it is not appropriate for very large tables.) A second approach is to use SQL to insert values into the new table based on the values of the original table. This approach does not rely on recordsets or the AddNew method, but it does need at least one Command object.

Using recordsets

The first approach, illustrated in the following procedure, requires a Connection object and a Catalog object in addition to a pair of recordsets. The Catalog object and the two recordsets share a common Connection object. Because so many objects share a common connection, it makes sense to declare a Connection object and invoke it for each of the other objects that need it.

Sub AddValues() Dim cnn1 As ADODB.Connection Dim cat1 As New ADOX.Catalog Dim rst1 As New ADODB.Recordset Dim rst2 As New ADODB.Recordset 'Set context for populating new table (FamilyMembers2). 'Empty values from FamilyMembers2 before running.     Set cnn1 = CurrentProject.Connection     Set cat1.ActiveConnection = cnn1     Set rst1.ActiveConnection = cnn1 '    Set rst2.ActiveConnection = cnn1 'Open recordsets based on new and original tables.     rst1.Open "FamilyMembers2", , adOpenKeyset, _         adLockOptimistic, adCmdTable     rst2.Open "FamilyMembers", cnn1, adOpenForwardOnly, _         adLockReadOnly, adCmdTable 'Loop through recordsets to copy from original to new table.     With rst1         Do Until rst2.EOF             .AddNew                 .Fields(0) = rst2.Fields(0)                 .Fields(1) = rst2.Fields(1)                 .Fields(2) = rst2.Fields(2)                 .Fields(3) = rst2.Fields(3) '            .Update             .MoveNext             rst2.MoveNext         Loop     End With End Sub 

After declaring objects and assigning the connection to the other objects that will use it, the procedure opens recordsets on the table with the source records, FamilyMembers, and the new table, FamilyMembers2.

The code above shows two approaches to assigning a connection to a recordset. You can assign a connection to a recordset's ActiveConnection property—AddValue uses this technique for rst1. Or you can reference a Connection object in the Open method for a recordset—the Open method for rst2 uses this technique. You can use either approach with both recordsets. The AddValues procedure comments out a line that reveals how to set rst2's ActiveConnection property with an assignment. If you use that technique for rst2, you can remove the reference to cnn1 in the Open method for rst2.

The Do...Loop statement that iterates through the records in rst2 occurs inside of a With...End statement that references rst1. (Because there are so many references to rst1 in the loop, this statement makes the code faster and much easier to read.) Within the loop, an AddNew method enables the assignment of the current record in rst2 to rst1. The use of the Update method to complete the operation of the AddNew method is optional. (The sample shows it commented out of the procedure.) After adding the current record in rst2 to rst1, the procedure advances one record in rst2. There is no need to advance in rst1 since each iteration of the loop simply adds a new record to the end of rst1. The Do loop continues until it encounters an end-of-file (EOF) for rst2.

Saving a recordset

After populating a recordset and its underlying table with values (and maybe even updating them), you can save a copy of the recordset using the new recordset Save method and its persistent file type, which uses a compact binary format. Access 2000 offers several ways to save and retrieve recordsets with this new format and a corresponding provider. The following procedures save a recordset, by using a client cursor, and then open it.

Sub SaveRST() On Error GoTo SaveRSTErr Dim rst1 As New ADODB.Recordset 'Open client cursor and recordset.     rst1.CursorLocation = adUseClient     rst1.Open "FamilyMembers2", CurrentProject.Connection, _         adOpenStatic, adLockBatchOptimistic     rst1.Save "c:\FamilyMembers3.adtg", adPersistADTG SaveRSTExit:     Exit Sub SaveRSTErr: 'Trap file already exists error     If Err.Number = -2147286960 Then         Kill "c:\FamilyMembers3.adtg"         Resume     End If 'Exit for other errors.     MsgBox "View Immediate window for error diagnostics.", _         vbCritical, "Programming Microsoft Access 2000"     Resume SaveRSTExit End Sub Sub OpenSavedRST() Dim rst1 As New ADODB.Recordset 'Open saved recordset file.     rst1.Open "c:\FamilyMembers3.adtg", "Provider=MSPersist" 'Print selected info to confirm retrieval.     Debug.Print rst1.Fields(0).Name & " = " & _         rst1.Fields(0).Value End Sub 

The main part of the SaveRST procedure opens a client cursor and then places a recordset in it. Then it invokes the recordset's Save method. Persisting a recordset to a file is no more complicated than that. The ADTG (Advanced Data TableGram) file is minimal (just 1 KB on my system). This represents substantial savings over storing the recordset as a table in a database file.

If you regularly save a recordset after updates, the Save method will fail unless you delete the old filename or use a new name. Alternatively, you can trap the error, delete the old file version, and then invoke the Save method again. The error handling routine in SaveRST illustrates this technique.

You can open a saved recordset with just two lines of code, as demonstrated by OpenSavedRST. The first line declares and instantiates a Recordset object. The second line invokes the Open method for the recordset. Its source is the file saved by the SaveRST procedure, and its connection argument references the persistence provider that ships with Access 2000. A third line prints a field from the recordset to confirm that the information in the file is correct.

Using SQL

To many developers, looping through a recordset one record at a time has the appeal of being concrete. It is easy to envision moving from one record to the next in rst2 and adding individual records to rst1. However, it is more efficient to use SQL code to perform the same task. In addition, the SQL approach requires just a single Command object from the ADODB library. The looping approach relies on several objects from both the ADODB and ADOX libraries. If you are uncomfortable with writing SQL, you can use the Access visual query designer to get a first draft of the correct SQL syntax for a task, such as inserting records from one table into another. The following AddValuesSQL procedure demonstrates how compact the SQL approach can be. (In fact, if your application does not need to know how many records it adds, the SQL approach can be even shorter.)

Sub AddValuesSQL() Dim cmd1 As ADODB.Command Dim intRowsAdded As Integer Dim str1 'Instantiate cmd1.     Set cmd1 = New ADODB.Command 'Set the connection and text for cmd1 'before executing it.     With cmd1         .ActiveConnection = CurrentProject.Connection         .CommandText = "INSERT INTO FamilyMembers2 " & _             "SELECT FamilyMembers.* " & _             "FROM FamilyMembers;"         .CommandType = adCmdText         .Execute intRowsAdded     End With 'Report how many records cmd1 added.     str1 = intRowsAdded & " rows were added to the table."     MsgBox str1, vbInformation, "Programming Microsoft Access 2000" End Sub 

The procedure begins by declaring and instantiating a Command object. It uses a With...End statement to simplify setting the object's properties and invoking its methods. The With...End code block begins by setting the Command object's ActiveConnection and CommandText properties. (The command text is always a SQL statement with this approach.) The block then sets the CommandType property to adCmdText to optimize performance. (Failing to set the CommandType property forces Access to resolve the type of Command object before it can process the command.) Finally, the block calls the Execute method with an optional argument, intRowsAdded. When the call to Execute returns, intRowsAdded contains the number of records the SQL statement affected. A subsequent message box statement uses intRowsAdded to report to the user the number of records that were added to the new table.

Working with Data in Other Formats

In Chapter 2, you learned how to use the Jet 4 OLE DB provider to open the Northwind database from an application. You can use this technique to access the data in one Access database from any other Access database. The technique even works for data stored in other formats for which an OLE DB provider exists. Even when an OLE DB provider is not available, you can link to another database via DAO and access its information with all the ease of use and power of any linked data source.

You can use the Jet 4 provider to open an Excel data source. Figure 3-15 shows a simple worksheet in an Excel workbook, named Customers.xls. Sheet1 contains a Customers range (see the Name Box) with information about customer ID, company name, contact name, and payment terms. The following procedure opens a range in an Excel 2000 workbook with Access 2000.

Figure 3-15. Access can easily work with the information in this Excel spreadsheet.

Sub OpenPrintXLDataSource() Dim cnn1 As New ADODB.Connection Dim rst1 As Recordset 'Open and set recordset.     cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=C:\Programming Access\Chap03\Customers.xls;" & _         "Extended Properties=Excel 8.0;"     Set rst1 = New ADODB.Recordset     rst1.CursorType = adOpenKeyset     rst1.LockType = adLockOptimistic     rst1.Open "customers", cnn1, , , adCmdTable 'Open recordset and print a test record.     Do Until rst1.EOF         Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value         rst1.MoveNext     Loop End Sub 

OpenPrintXLDataSource uses a Connection object to open an Excel workbook. The Open method for the cnn1 object creates a connection to the worksheet file. The provider in this call is the same one used for Jet 4 files, the Extended Properties argument specifies a format compatible with Excel 2000 files, and the Data Source argument points to an Excel workbook in the My Documents folder of the C drive. The next four lines in the procedure create and open a reference to a recordset defined on the Customers range in the Customers.xls file. The Do loop at the end of the procedure prints the first two fields in the recordset to the Immediate window.

After you open a recordset for an Excel data source, you can do anything that Access permits you to do with a record source, such as update field values. The difference is that your application updates values in a range on the Excel worksheet rather than those in an Access table. The OpenPrintXLDataSource example clearly demonstrates the power of ADO and OLEDB providers within an Access application.

Using the MSDASQL provider

Access 2000 works with other OLEDB providers, such as MSDASQL, which works for all ODBC data sources. Although there is a special provider for Microsoft SQL Server, the following procedure uses the general OLEDB ODBC provider to illustrate how you can use the provider with any ODBC data source. Since this provider always references an ODBC data source, you can use it in coordination with a DSN. The procedure shows the MSDASQL provider referencing the SQL Server Pubs database to print the author social security number, first name, and last name.

Sub GetODBCThroughOLEDB() Dim cnn1 As New ADODB.Connection Dim rst1 As ADODB.Recordset 'Open ODBC sources with msdaSQL provider and DSN reference.     cnn1.Open "Provider=MSDASQL;DSN=Pubs;"     Set rst1 = New ADODB.Recordset     rst1.CursorType = adOpenKeyset     rst1.LockType = adLockOptimistic     rst1.Open "authors", cnn1, , , adCmdTable 'Open recordset and print a test record.     Do Until rst1.EOF         Debug.Print rst1.Fields(0).Value, _             rst1.Fields(2), rst1.Fields(1).Value         rst1.MoveNext     Loop End Sub 

The GetODBCThroughOLEDB procedure starts by opening a connection to the SQL Server Pubs database and invokes the object's Open method. The provider argument specifies the MSDASQL provider, and the DSN argument contains the connection information and acts as a Data Source argument for the Jet 4 provider. The Open method can specify the connection string information, which eliminates the need to reference the DSN. The following statement shows how to open the SQL Server Pubs database without denoting a DSN:

cnn1.Open "Provider=MSDASQL;DRIVER=SQL Server;" & _     "SERVER=CAB2200;DATABASE=Pubs;uid=sa;pwd=;" 

The SQL Server name is CAB2200. Notice that the connection string also contains user ID and password arguments. The connection string is specific to the database to which you connect.

The next four lines of the procedure open a recordset on the Authors table in the SQL Server Pubs database. The syntax of the Open method is identical to the one used to open a range on an Excel worksheet. This symmetry of form between two such diverse sources illustrates the power of ADO with OLE DB providers. The final Do loop iterates through the records to print the social security number, first name, and last name for each author. Since the Value property is the default for the Recordset object's Fields collection, you don't need to specify it. (Notice that the second argument to the Debug.Print method does not explicitly reference the Value property of the Fields collection.) The overall design of the GetODBCThroughOLEDB procedure shows that Access 2000 can link to a SQL Server database as easily as it can to an Excel workbook.

The TransferDatabase and TransferSpreadsheet methods of the DoCmd object are an easy and robust way to link to data in another application; they do not even depend on a reference to the DAO library. These methods expose their sources through the Database window, while the OLE DB provider offers an exclusively programmatic interface to its data sources. Whether exposure through the Database window is a benefit to you will depend on your application and the preferences of its users.

Using the TransferDatabase or TransferSpreadsheet method

The TransferDatabase and TransferSpreadsheet methods follow from macro actions that enable importing, linking, and exporting. The TransferSpreadsheet method supports a wide array of Lotus and Excel formats. The TransferDatabase method supports ODBC data sources, such as SQL Server and Oracle, as well as ISAM data sources, such as Paradox, dBase, Microsoft FoxPro, and Jet. TransferSpreadsheet and TransferDatabase tap into the ISAM and ODBC drivers that ship with Access. Good sources of documentation on these methods include the Access online documentation and a Macro window open in Design view with drop-down boxes offering options for selected arguments.

The linkXLCustomers procedure below shows the syntax for the TransferSpreadsheet method. You can compare this technique with the functionality of an OLE DB provider. This sample links to the Customers range in the Customers.xls workbook. (Customers.xls is included in this book's companion CD.) It specifies an Excel 97 file format, but also supports Excel 5, Excel 4, and Excel 3. TransferSpreadsheet also supports Lotus formats, such as WK1, WK3, and WK4. The DoCmd object creates a linked table named XLCustomers in the Database window. The next-to-last argument to TransferSpreadsheet (-1) indicates that the Excel range has field names in the first row.

Sub linkXLCustomers() Dim cnn1 As New ADODB.Connection Dim rst1 As ADODB.Recordset 'Use DoCmd to programmatically make the link.     DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97, _         "XLCustomers", "C:\Programming Access\Chap03\Customers.xls", _          -1, "Customers" 'Open and set recordset.     Set rst1 = New ADODB.Recordset     rst1.ActiveConnection = CurrentProject.Connection     rst1.CursorType = adOpenKeyset     rst1.LockType = adLockOptimistic     rst1.Open "XLCustomers", , , , adCmdTable 'Open recordset and print a test record.     Do Until rst1.EOF         Debug.Print rst1.Fields(0).Value, rst1.Fields(2)         rst1.MoveNext     Loop End Sub 

After the TransferSpreadsheet method executes, your application can use its return set similarly to one from an OLE DB provider. First, you open a recordset on the linked table. Then you programmatically manipulate the recordset to serve the requirements of your application. (linkXLCustomers merely prints several fields for each row.)

The TransferDatabase method offers a wider range of data access options than the TransferSpreadsheet command. These include its historical ISAM drivers and ODBC drivers. Since most databases support ODBC access, this method can interface with nearly any relational database management system (RDBMS) data source. Furthermore, the TransferDatabase method has a similar syntax to the TransferSpreadsheet method, which makes it easy to use.

The following linkODBCAuthors procedure uses the TransferSpreadsheet method to link to the Authors table in the SQL Server Pubs database. linkODBCAuthors forms a link to the Authors table under the name dboAuthors in the current database. If dboAuthors is already in the current database, the method silently retains the original copy and makes another copy, naming it dboAuthors1.

Sub linkODBCAuthors() Dim cnn1 As New ADODB.Connection Dim rst1 As ADODB.Recordset 'Use DoCmd to programmatically make the link.     DoCmd.TransferDatabase acLink, "ODBC Database", _         "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=pubs", _         acTable, "Authors", "dboAuthors" 'Open and set recordset.     Set rst1 = New ADODB.Recordset     rst1.ActiveConnection = CurrentProject.Connection     rst1.CursorType = adOpenKeyset     rst1.LockType = adLockOptimistic     rst1.Open "dboAuthors", , , , adCmdTable 'Open recordset and print a test record.     Do Until rst1.EOF         Debug.Print rst1.Fields(0).Value, rst1.Fields(2)         rst1.MoveNext     Loop End Sub 

The first argument to TransferSpreadsheet specifies the transfer type to the data source; the acLink argument creates a linked table in the Database window. The next two arguments specify the type of database and its name. (linkODBCAuthors specifies an ODBC data source. In fact, this sample uses the popular SQL Server Pubs database.) The final arguments designate the type of database object on which you want to base your source, its name in the remote source (Authors), and its name in your application (dboAuthors). The remainder of linkODBCAuthors performs the same actions as the other samples in this section.