Chapter 15. What Are ActiveX Data Objects, and Why Are They Important?

IN THIS CHAPTER

• Why This Chapter Is Important
• Examining the ADO Model
• Understanding ADO Recordset Types
• Working with ADO Recordset Properties and Methods
• Modifying Table Data Using ADO Code
• Creating and Modifying Database Objects Using ADO Code
• Practical Examples: Applying These Techniques to Your Application

Why This Chapter Is Important

ActiveX Data Objects (ADO) are used to create, modify, and remove Jet Engine, Access Database Engine, SQL Server, or other open database connectivity (ODBC) objects via code. They give you the flexibility to move beyond the user interface to manipulate data stored in the Jet Engine, Access Database Engine, and other formats. Some of the many tasks that you can perform with ADO include the following:

• Analyzing the structure of an existing database
• Adding or modifying tables and queries
• Creating new databases
• Changing the underlying definitions for queries by modifying the SQL on which the query is based
• Traversing through sets of records
• Administrating security
• Modifying table data

Examining the ADO Model

Figure 15.1 shows an overview of the Microsoft ADO model. Unlike the DAO model, the ADO object model is not hierarchical.

Figure 15.1. The ADO object model.

The Connection Object

The Connection object defines a session for a user for a data source. Although the ADO object model is not considered to be hierarchical, the Connection object is considered the highest-level ADO object. After you have established a Connection object, you can use it with multiple recordsets. This improves performance and greatly simplifies your programming code.

You must declare a Connection object before you use it. The declaration looks like this:

Dim cnn as ADODB.Connection

After you have declared the Connection object, you must instantiate a new Connection object. The code looks like this:

Set cnn = New ADODB.Connection

The Connection must then be opened. The Open method of the Connection object receives a connection string, and optionally a user ID, password, and options as a parameter. The following is an example of the simplest use of the Open method:

The connection string contains three pieces of information:

• The OLEDB provider that you want to use (in this case, the Access Database Engine)
• Standard ADO connection properties (for example, User ID)
• Provider-specific connection properties

If you want to programmatically manipulate an MDB file (Access 2003 and earlier), you use the Jet Engine. Your connection string will look like this:

Notice that the example uses the Jet provider and provides a connection string for an MDB file.

Table 15.1 lists the most commonly used connection string properties used by the Jet and Access Database Engine OLEDB providers.

Table 15.1. Connection String Properties Used by the Jet and Access Database Engine OLEDB Provider

The complete routine required to establish a connection appears in Listing 15.1.

Listing 15.1. Creating a Connection Object

Creating a connection in a client/server environment is similar to creating a connection with an Access database. The code appears in Listing 15.2.

Listing 15.2. Creating a SQL Server Connection Object with SQL Server Security

As you can see, the difference lies in the connection string. This example used SQL Server security to connect to the database. The example in Listing 15.3 uses NT Integrated Security.

Listing 15.3. Creating a SQL Server Connection Object with Integrated Security

Notice that this example sets integrated security equal to SSPI. This causes the Access Database Engine to use NT Integrated Security to connect to the SQL Server database.

The Recordset Object

A Recordset object is used to look at records as a group. A Recordset object refers to the set of rows returned from a request for data. As with a Connection object, to use a Recordset object, you must first declare it. The code looks like this:

Dim rst as ADODB.Recordset

After you have declared the Recordset object, you must instantiate it. The code looks like this:

Set rst = New ADODB.Recordset

As with a Connection object, you use the Open method to point the Recordset object to a set of records. The code looks like this:

rst.Open "Select * From tblClients", CurrentProject.Connection

The first parameter of the Open method is the source of the data. The source can be a table name, a SQL statement, a stored procedure name, a Command object variable name, or the filename of a persisted recordset. In the example, the source is a SQL Select statement.

The second parameter of the Open method must be either a valid connection string or the name of a Connection object. In the example, the Connection property of the CurrentProject object returns a reference to a copy of the connection associated with the current project. The reference supplies the connection for the Recordset object. The completed code appears in Listing 15.4.

Listing 15.4. Creating a Recordset Using a Connection String

Notice that after you open the recordset, the code prints the result of the GetString method of the Recordset object to the Immediate window. The GetString method of the Recordset object builds a string based on the data contained in the recordset. For now, this is a simple way of verifying that your code works as expected. Also note that the code uses the Close method of the Recordset object to close the recordset. The Close method, when applied to either a Connection object or to a Recordset object, has the effect of freeing the associated system resources. The Close method does not eliminate the object from memory. Setting the Recordset object equal to Nothing eliminates the object from memory.

Although this syntax works quite well, I prefer to set the parameters of the Open method as properties of the Recordset object, before the Open method is issued. You will see that this makes your code much more readable as you add parameters to the Open method. The code appears in Listing 15.5.

Listing 15.5. Creating a Recordset Using the ActiveConnection Property

Finally, you can use a Connection object, rather than a copy of the Connection object associated with the CurrentProject object, to provide a connection for the recordset. In fact, you can use the same Connection object for multiple recordsets. The code appears in Listing 15.6.

Listing 15.6. Creating a Recordset Using a Connection Object

Notice that both rst1 and rst2 use the same Connection object.

The Command Object

The ADO Command object represents a query, SQL statement, or stored procedure that is executed against a data source. Although not always necessary, a Command object is particularly useful when executing parameterized queries and stored procedures. Just as with the Connection object and the Recordset object, you must declare a Command object before you use it:

Dim cmd as ADODB.Command

Next, you must instantiate the Command object:

Set cmd = New ADODB.Command

After you instantiate the Command object, you must set its ActiveConnection property and its CommandText property. As with a Recordset object, the ActiveConnection property can be either a connection string or a reference to a Connection object. The CommandText property is the SQL statement or stored procedure used by the Command object. The ActiveConnection and the CommandText properties look like this:

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "tblClients"

The completed code appears in Listing 15.7.

Listing 15.7. Using a Command Object

This example instantiates the Command object. It sets the CommandText property to a SQL Select statement and points the ActiveConnection property to the connection associated with the current database. It uses the Execute method of the Command object to return the results of the SQL statement into the Recordset object.

Understanding ADO Recordset Types

Three parameters of the Open method of a Recordset object affect the type of recordset that is created. They are the CursorType, the LockType, and the Options parameters. These parameters combine to determine the types of movements that you can execute within a recordset, when changes that other users make to data underlying the recordset will be seen, and whether the recordset’s data can be updated.

The CursorType Parameter

By default, when you open a recordset, the CursorType parameter is set to adOpenForwardOnly. This means that you can only move forward through the records in the recordset. You will not see additions, edits, or deletions that other users make. Furthermore, many properties and methods, such as the RecordCount property and the MovePrevious method, are unavailable. Listing 15.8 illustrates this.

Listing 15.8. The RecordCount Property Is Not Supported with a Forward-Only Recordset

The value -1 displays in the Immediate window because the forward-only recordset does not support the RecordCount property. Because you did not explicitly designate the cursor type, a forward-only recordset was created.

Three other values are available for the CursorType. They are adOpenStatic, adOpenKeyset, and adOpenDynamic. The adOpenStatic option allows forward and backward movement through the records in the recordset, but changes that other users make to the underlying data are not seen by the recordset. The adOpenKeyset option offers everything from the adOpenStatic option, but in addition, edits that other users make are seen by the recordset. Finally, with the adOpenDynamic option, additions, edits, and deletions made by other users are seen by the recordset. Table 15.2 illustrates each of these options in further detail.

Table 15.2. Valid Choices for the CursorType Parameter

You can set the CursorType property of the recordset in one of two ways. You can set it as a parameter of the Open method or as a property of the Recordset object. Listing 15.9 illustrates the first method.

Listing 15.9. Supplying the CursorType as a Parameter of the Open Method

Notice that, in Listing 15.9, the CursorType appears as a parameter of the Open method. Contrast Listing 15.9 with Listing 15.10.

Listing 15.10. Supplying the CursorType as a Property of the Recordset Object

In Listing 15.10, the CursorType is set as a property of the Recordset object, prior to the execution of the Open method. Separating the properties from the Open method improves the readability of the code.

The LockType Parameter

Although the CursorType property of a Recordset object determines how movements can occur within the recordset and whether other users’ changes are seen, the CursorType in no way affects the updateability of the recordset’s data. In fact, when you open a recordset, you open it as read-only by default. It is only by changing the LockType property that you can make the recordset updateable.

The options for lock type are adLockReadOnly, adLockPessimistic, adLockOptimistic, and adLockBatchOptimistic. The default, adLockReadOnly, does not allow changes to the recordset. The other options all provide updateability for the recordset’s data. The difference is when the records are locked. With the adLockPessimistic option, locking occurs as soon as the editing process begins. With the adLockOptimistic option, the record is locked when you issue the Update method. Finally, with the adLockBatchOptimistic option, you can postpone locking until you update a batch of records. All these options are discussed in extensive detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

As with the CursorType property, you can set the LockType property as a parameter of the Open method or as a property of the Recordset object. Listing 15.11 shows the configuration of the lock type as a property of the Recordset object.

Listing 15.11. Configuration of LockType as a Property of the Recordset Object

In Listing 15.11, the LockType property is set to adLockOptimistic. The record is locked when the Update method of the Recordset object is issued.

The Options Parameter

The Options parameter determines how the provider should evaluate the source argument. The valid choices are illustrated in Table 15.3.

Table 15.3. Valid Choices for the Options Parameter

The default for the Options parameter is adCmdUnknown. If you do not explicitly specify the Options parameter, the provider attempts to evaluate it while the code is running, which degrades performance. It is therefore important to specify the parameter. Listing 15.12 illustrates the use of the Options parameter of the Open method.

Listing 15.12. The Options Parameter of the Open Method

In Listing 15.12, the Options parameter is set to adCmdText. This causes the source to be evaluated as a SQL command.

Consistent Versus Inconsistent Updates

When a recordset is based on data from more than one table, the Access Database Engine automatically allows you to make changes to the foreign key field. For example, if a recordset is based on data from the Customers table and the Orders table, you are able to make changes to the CustomerID in the Orders table. This is referred to as a consistent update. At times, you might want to make changes to the primary key field. This could result in a violation of referential integrity and is therefore referred to as an inconsistent update.

If you’ve established referential integrity and have designated that you want to cascade updates, consistent and inconsistent updates yield the same results. On the other hand, without cascade updates activated, a change to the primary key field causes referential integrity to be violated.

Listing 15.13 shows you how to open a recordset with inconsistent updates.

Listing 15.13. Opening a Recordset with Inconsistent Updates

Notice that this code sets the Jet OLEDB:Inconsistent property prior to the Open method of the recordset. This causes the recordset to be opened so that you can use inconsistent updates if you want.

Selecting a Cursor Location

A cursor refers to the set of rows or row pointers that are returned when you open a recordset. With DAO, the location of the cursor was not an issue. On the other hand, ADO supports two cursor locations. As its name implies, the client manages a client-side cursor. The server manages a server-side cursor.

If you are using the Jet Engine or the Access Database Engine, the client machine always manages the cursor because the Jet Engine and the Access Database Engine run only on the client machine. You might think this means that you should always designate a client-side cursor when working with the Jet Engine and the Access Database Engine. Actually, the opposite is true. If you designate a client-side cursor when working with the Jet Engine or the Access Database Engine, the data is cached twice on the client machine. When a client-side cursor is specified, the Microsoft Cursor Service for OLEDB requests all the data from the OLEDB provider and then caches it and presents it to the application as a static recordset. For this reason, when working with the Jet Engine or the Access Database Engine, you should designate a client-side cursor only when you want to take advantage of functionality provided only by a client-side cursor.

Listing 15.14 illustrates how to designate the cursor location.

Listing 15.14. Designating the Cursor Location

This example designates a server-side cursor.

Working with the Supports Method

Depending on which CursorType, LockType, CursorLocation, and Provider are used to open a recordset, the functionality of the recordset varies. The Supports method of a recordset determines which features a particular recordset supports. It returns a Boolean value designating whether the selected feature is supported. Listing 15.15 provides an example.

Listing 15.15. The Supports Method of the Recordset Object

Working with ADO Recordset Properties and Methods

The ADO Recordset object is rich with properties and methods. These properties and methods allow you to move through a recordset; sort, filter, and find data; as well as update data contained with the recordset. The sections that follow cover the most commonly used properties and methods.

Examining Record-Movement Methods

When you have a Recordset object variable set, you probably want to manipulate the data in the recordset. Table 15.4 shows several methods you can use to traverse through the records in a recordset.

Table 15.4. Methods for Moving Through the Records in a Recordset

Listing 15.16 shows an example of using the record-movement methods on a Recordset object.

Listing 15.16. Using the RecordsetMovements() Methods on a Recordset Object

This code opens a recordset based on the tblProjects table. When the recordset is open, the ProjectID of the first record is printed to the Immediate window. The MoveNext method of the Recordset object is used to move to the next record in the recordset. The ProjectID of the record is printed. The MoveLast method of the Recordset object is used to move to the last record in the recordset. Once again, the ProjectID is printed. The MovePrevious method moves the record pointer back one record, and the ProjectID is printed again. Finally, the MoveFirst method moves the record pointer to the first record, and the ProjectID is printed. The code closes the recordset and destroys the Recordset object.

Detecting the Limits of a Recordset

Before you begin to traverse through recordsets, you must understand two recordset properties: BOF and EOF. The names of these properties are outdated acronyms that stand for beginning of file and end of file, respectively. They determine whether you have reached the limits of your recordset. The BOF property is True when the record pointer is before the first record, and the EOF property is True when the record pointer is after the last record.

You commonly will use the EOF property when moving forward through your recordset with the MoveNext method. This property becomes True when your most recent MoveNext has moved you beyond the bounds of the recordset. Similarly, BOF is most useful when using the MovePrevious method.

You must keep in mind some important characteristics of the BOF and EOF properties:

• If a recordset contains no records, both the BOF and EOF properties evaluate to True.
• When you open a recordset containing at least one record, the BOF and EOF properties are set to False.
• If the record pointer is on the first record in the recordset and you issue the MovePrevious method, the BOF property is set to True. If you attempt to use MovePrevious again, a runtime error occurs.
• If the record pointer is on the last record in the recordset and you issue the MoveNext method, the EOF property is set to True. If you attempt to use MoveNext again, a runtime error occurs.
• When the BOF and EOF properties are set to True, they remain True until you move to a valid record.
• When the only record in a recordset is deleted, the BOF and EOF properties remain False until you attempt to move to another record.

Listing 15.17 shows an example of using the EOF property to determine the bounds of a recordset.

Listing 15.17. Using the EOF Property to Determine the Bounds of a Recordset

In Listing 15.17, a recordset is opened based on tblProjects. The EOF property is evaluated. As long as the EOF property equals False, the code prints the contents of the ClientID field and advances the record pointer to the next record in the recordset.

Counting the Number of Records in a Recordset

The RecordCount property returns the number of rows in the recordset. Not all types of recordsets and providers support the RecordCount property. If the RecordCount property is not supported, no error occurs. Instead, the RecordCount is -1. Listing 15.18 provides an example.

Listing 15.18. A Recordset That Does Not Support the RecordCount Property

Because the default CursorType is adOpenForwardOnly, and a forward-only cursor does not support the RecordCount property, -1 prints to the Immediate window. Listing 15.19 rectifies this problem.

Listing 15.19. A Recordset That Supports the RecordCount Property

Notice that the CursorType is set to adOpenStatic. Because the RecordCount property is supported with static cursors, the correct number of records is printed to the Immediate window.

One of the important uses of the RecordCount property is to determine whether a recordset contains any rows. Listing 15.20 illustrates this important use of the RecordCount property.

Listing 15.20. Checking to See Whether Records Are Returned in a Recordset

The CheckARecordset routine opens a recordset based on a table called tblEmpty, which contains no data. The CheckARecordset routine calls the AreThereRecords function, passing a reference to the recordset. The AreThereRecords function evaluates the RecordCount property of the recordset that it is passed. It returns False if the RecordCount is zero and True if the RecordCount is nonzero.

Sorting, Filtering, and Finding Records

Sometimes you need to sort, filter, or find data within an existing recordset. The Sort property, Filter property, and Find method of the Recordset object allow you to accomplish these tasks. The sections that follow cover these properties and this method.

Sorting a Recordset

The Sort property of the Recordset object allows you to sort data in an existing recordset. Listing 15.21 illustrates its use.

Listing 15.21. The Sort Property of the Recordset Object

This code begins by opening a recordset based on the tblTimeCardHours table. The code prints the records in the recordset in their “natural” order. Next, the Sort property of the Recordset object sorts the data by the DateWorked field. Notice that the Sort property is set equal to a field. If you want to sort by more than one field, you must separate the field names with commas. When the records are once again printed, they appear in order by the DateWorked field.

Filtering a Recordset

Sometimes you might want to select a subset of the data returned in a recordset. The Filter property helps you to accomplish this task. Its use is illustrated in Listing 15.22.

Listing 15.22. The Filter Property of the Recordset Object

This example opens a recordset based on tblTimeCardHours. The code prints the records without a filter applied. The Filter property is then set to limit the data to only those records with a DateWorked value between 1/1/2007 and 1/5/2007. The code prints the records in the recordset again.

Finding a Specific Record in a Recordset

The Find method allows you to locate a particular record in the recordset. It is different from the Filter property in that all records in the recordset remain available. Listing 15.23 illustrates the use of the Find method.

Listing 15.23. The Find Method of a Recordset Object

Listing 15.23 opens a recordset based on all the records in the tblProjects table. The Find method is used to locate the first record where the ProjectID is equal to a specific value. If the record is not found, the EOF property of the Recordset object is True.

Working with Variables in Strings

When using the Find method or when building a SQL statement in code, you must be cognizant of the delimiters to use. No delimiters are necessary when working with numeric values. For example:

Select * FROM tblClients WHERE ClientID = 1

You must use a pound symbol (#) when delimiting dates for Microsoft Access, like this:

Select * FROM tblClients WHERE IntroDate = #12/31/2007#

The process of delimiting strings is somewhat more difficult than it initially seems. The basic process is to surround the string with apostrophes:

Select * FROM tblClients WHERE City = 'Oak Park'

This approach works unless there is an apostrophe in the string. Listing 15.24 provides the solution.

Listing 15.24. Handling Apostrophes Within Strings

This code passes the string to a user-defined function called ReplaceApostrophe, which surrounds the string with apostrophes. If any apostrophes are found within the string, they are replaced with two apostrophes.

Using the AbsolutePosition Property

The AbsolutePosition property of the Recordset object sets or returns the ordinal position of the current row in the recordset. Its use is illustrated in Listing 15.25.

Listing 15.25. The AbsolutePosition Property of a Recordset Object

In this example, the Find method is used to locate a project with a specific ProjectID. If the project is found, the ordinal position of the record that is located is printed to the Immediate window.

Using the Bookmark Property

The Bookmark property of a Recordset object returns a variant variable that acts as a unique identifier for that particular record in the recordset. You can use the Bookmark property to save the current position and then quickly and easily return to it at any time. Listing 15.26 illustrates the use of a bookmark.

Listing 15.26. The Bookmark Property of a Recordset Object

In this example, a unique identifier to the current record is stored in a variant variable. The code then loops through the remainder of the records in the recordset. When it is done, it sets the Bookmark property of the Recordset object equal to the unique identifier stored in the variant variable.

Running Parameter Queries

You will not always know the criteria for a recordset at design time. Fortunately, ADO allows you to supply parameters to the CommandText property of the Command object. Listing 15.27 provides an example.

Listing 15.27. Running a Parameter Query

Notice that in this example, the CommandText property contains two question marks. Each of these is considered a parameter. The parameters are supplied when the Execute method of the Command object is used. Notice that the Parameters argument of the Execute method receives an array containing the parameter values. Note that unless you specify basADORecordsets.RunParameterQuery, you get an “ambiguous name detected” error.

Refreshing Recordset Data

You can use two methods to refresh the data in a recordset: Requery and Resync. The Requery method is roughly equivalent to once again opening the recordset. The Requery method forces the OLEDB provider to perform all the steps it performed when first creating the recordset. New rows are added to the recordset, changes to data made by other users are reflected in the recordset, and deleted rows are removed from the recordset. The Requery method requires significant resources to execute. The Resync method is much more efficient. It updates the recordset to reflect changes made by other users. It does not show added rows or remove deleted rows from the recordset.

Working with Persisting Recordsets

Using ADO, recordsets can not only exist in memory, but they can also be written to disk. A recordset written to disk is referred to as a persisted recordset. Listing 15.28 illustrates how to persist a recordset to disk.

Listing 15.28. Persisting a Recordset

Notice that the Save method of the Recordset object is used to persist the recordset to disk. The Format parameter of the Save method allows you to designate whether you want to save the recordset in the Microsoft proprietary Advanced Data Tablegram (ADTG) format or whether you want to save the recordset as XML. Listing 15.29 shows you how to read a persisted recordset.

Listing 15.29. Reading a Persisted Recordset

After prompting the user for a filename, the code ensures that the designated file is found. It then opens a recordset, using the file as the source argument. The adCmdFile constant is used for the Options parameter of the Open method. The adCmdFile value notifies ADO that the source is a persisted recordset.

Modifying Table Data Using ADO Code

So far, this chapter has covered only the process of retrieving data from a recordset. It is common that you might need to update the data in a recordset. The sections that follow show you how to change data one record at a time, update a batch of records, delete records, and add records.

Changing Record Data One Record at a Time

You can loop through a recordset, modifying all the records in the recordset. Listing 15.30 shows this technique.

Listing 15.30. Modifying One Record at a Time

The code in Listing 15.30 opens a recordset based on all the records in the tblProjectsChange table. It locates the first record where the ProjectTotalEstimate is less than 30,000. The ProjectTotalEstimate is increased by 10%, and the record is updated. The code locates the next record that meets the specified criteria. The code repeats the process until it locates all records meeting the specified criteria.

This code is very inefficient from several standpoints. The first problem is that it opens a recordset based on all the records in the tblProjectsChange table, when only those with a ProjectTotalEstimate less than 30,000 needed to be updated. A more efficient approach is to open a recordset containing only those records that you need to update. Listing 15.31 illustrates this technique.

Listing 15.31. Improving the Process of Modifying One Record at a Time

Furthermore, it would be more efficient to simply execute an action query that performs the update. This technique is covered in the section that follows.

Performing Batch Updates

If you use a client-side cursor, along with a static or keyset cursor, you can take advantage of batch updates. Using batch updates, all changes you make to a recordset are sent to the underlying OLEDB provider as a batch. The process is illustrated in Listing 15.32.

Listing 15.32. Performing Batch Updates

In this example, the CursorLocation property of the recordset is set to adUseClient, the CursorType is set to adOpenKeyset, and the LockType is set to adLockBatchOptimistic. Notice that the Update method is not included in the Do Until loop. Instead, the UpdateBatch method is used to send all the changes to the server at once.

Making Bulk Changes

As mentioned in the preceding section, it is inefficient to open a recordset and then update each record individually. A much more efficient approach is to execute an action query. Listing 15.33 illustrates this process.

Listing 15.33. Making Bulk Changes to the Records in a Recordset

In Listing 15.33, the Execute method of the Connection object executes a stored query called qryIncreaseTotalEstimate. Any criteria contained within the query are applied.

You might be wondering how you can update data stored in a SQL Server database. One method is to call a stored procedure located in the SQL Server database. The code in Listing 15.34 illustrates an example.

Listing 15.34. Using a Stored Procedure to Make Bulk Changes to Data in a SQL Server Database

The example in Listing 15.34 executes a SQL Server procedure called procIncreaseTotalEstimate. Notice that the example does not receive parameters. The example in Listing 15.35 uses a Command object to execute a SQL statement containing parameters.

Listing 15.35. Executing a SQL Statement Containing Parameters

The example begins by building a connection string. It then instantiates and opens a connection. It instantiates a Command object and sets its CommandText property to an UPDATE statement. Next it sets the CommandType and ActiveConnection properties of the command object. It appends four parameters to the Command object, and finally uses the Execute method of the Command object to update data in the Orders table. Although the example sets the parameter values to fixed values, it could instead take the values from variables or from text boxes on a form. In addition, the example could execute a stored procedure stored in the SQL Server database. This alternative code appears in Listing 15.36.

Listing 15.36. Executing a Stored Procedure Containing Parameters

This example is quite similar to the example in Listing 15.35. There are two main differences between the two examples. The first difference is the value of the CommandType property designated in each example. Whereas Listing 15.35 sets the CommandType property of the Command object to acCmdText, Listing 15.36 sets it to acCmdStoredProc, indicating that the text in the CommandText property is the name of a stored procedure. The second difference is that the text in the CommandText property is a SQL statement in Listing 15.35, but it is the name of a stored procedure in Listing 15.36. The stored procedure looks like this:

Notice that it declares four parameters and then uses the parameter values passed to it to update the OrderDate, ShipVisa, and Freight fields.

Deleting an Existing Record

You can use ADO code to delete a record in a recordset. The code appears in Listing 15.37. Note that it must be called using basADORecordset.DeleteCusts.

Listing 15.37. Deleting an Existing Record

In Listing 15.37, a recordset is opened, based on all the records in the tblProjectsChange table. The code loops through all the records in the recordset. If the ProjectTotalEstimate is less than the value passed as a parameter to the routine, the Delete method of the Recordset object removes the record from the recordset.

As previously discussed, this example is very inefficient. You should either build a recordset containing only the records you want to delete or use an action query to accomplish the task.

Adding a New Record

You can not only edit and delete data using ADO, but also add records as well. Listing 15.38 illustrates this process.

Listing 15.38. Adding a New Record to a Recordset

This code, an event procedure for a command button on frmUnbound, begins by setting the CursorType property of the recordset to adOpenKeyset and the LockType property to adLockOptimistic. The AddNew method creates a buffer for a new record. All the field values are assigned, based on values in the text boxes on the form. The Update method writes the data to disk. Because the ProjectID field is an Autonumber field, the txtProjectID text box must be updated to reflect the Autonumber value that was assigned.

Creating and Modifying Database Objects Using ADO Code

Although most of the time you will design your database structure before you deploy your application, sometimes you will need to design or modify database objects at runtime. Fortunately, you can accomplish these tasks using ADO code. The following sections cover adding and removing tables, modifying relationships, and building queries, all using ADO code. These are only a few of the tasks that you can accomplish.

Adding a Table Using Code

Adding a table using ADO code is relatively easy. Listing 15.39 provides an example.

Listing 15.39. Adding a Table

Listing 15.39 begins by instantiating an ADOX table object. It sets the Name and ParentCatalog properties of the Table object. Then it uses the Append method of the Columns collection of the table to append each field to the table. After all the columns are appended, it uses the Append method of the Tables collection of the Catalog object to append the Table object to the database.

After the table is appended to the Catalog, you can add indexes to the table. An Index object is instantiated. The Name property of the index is set. Next, the Append method of the Columns object of the Index adds a column to the Index. The PrimaryKey and Unique properties of the index are both set to True. Finally, the Index object is appended to the Indexes collection of the Table object.

Removing a Table Using Code

Sometimes you need to remove a table from a database. Fortunately, this task is easily accomplished using ADO code. Listing 15.40 illustrates the process.

Listing 15.40. Removing a Table

First, this code declares and instantiates a Catalog object. Then it uses the Delete method of the Tables collection of the Catalog object to remove the table from the database.

Establishing Relationships Using Code

If your application adds new tables to a database, you might need to establish relationships between those tables, as demonstrated in Listing 15.41.

Listing 15.41. Establishing a Relationship

This code begins by pointing a Table object at the foreign key table in the relationship. The code instantiates a Key object. It sets the Name property of the Key object. Next, it establishes the Type property of the Key object. It sets the RelatedTable property equal to the name of the primary key table involved in the relationship. The Append method of the Columns collection of the Key object appends the foreign key field to the Key object. Then the RelatedColumn property of the column is set equal to the name of the primary key field. Finally, the code appends the Key object to the Keys collection of the Table object.

Creating a Query Using Code

At times, you will want to build a query on the fly and permanently store it in the database. Listing 15.42 illustrates this process.

Listing 15.42. Creating a Query

This code begins by creating and instantiating a Catalog object and a Command object. It sets the CommandText property of the Command object equal to the SQL statement that underlies the query. The Append method of the Views collection of the Catalog object appends the Command object to a query with the specified name. Finally, the code refreshes the Views collection of the Catalog object.

Practical Examples: Applying These Techniques to Your Application

The potential applications for the methodologies learned in this chapter are endless. The sections that follow explore just a few of the ways you can apply these techniques. The examples here are located in Chap15Ex.accdb.

Using Recordset Methods on a Data-Entry Form

At times, you might want to disable the default record movement and add, edit, or delete functionality from a form and code all the functionality yourself. You might want to perform these actions if you are going against client/server data and want to execute additional control over the data-entry environment. You also might want to use these techniques when you are developing applications for both the Access and Visual Basic environments and are striving for maximum code compatibility. Regardless of your reasons for using the following techniques, it is a good idea to know how to assign a Recordset object to a form and then use the form’s underlying recordset to display and modify data.

Figure 15.2 shows a form in which the navigation buttons and record selectors have been removed. The form contains six command buttons: Move Previous (<), Move Next (>), Add, Delete, Find, and Exit. All the buttons use the recordset underlying the form to move from record to record in the form and modify the data contained within the form.

Figure 15.2. The frmRecordsets form.

The RecordSource property of the form is not set. The Load event of the form is responsible for assigning a Recordset object to the form. Listing 15.43 shows the Load event of the form.

Listing 15.43. The Load Event Assigning a Recordset Object to the Form

The code begins by declaring and instantiating an ADODB Recordset object. It then sets four properties of the Recordset object: the ActiveConnection, the CursorType, the Cursor Location, and the LockType. The Open method is used to open a recordset, based on the tblClients table. Finally, a Set statement is used to assign the recordset to the recordset underlying the form.

Listing 15.44 shows the code for the Move Previous button.

Listing 15.44. Code for the Move Previous Button

This routine performs the MovePrevious method on the Recordset property of the form. If the BOF property becomes True, indicating that the record pointer is before the first valid record, the MoveNext method is performed on the Recordset property of the form to return the record pointer to the first record in the recordset. Finally, the bookmark of the form is synchronized with the bookmark of the Recordset property. Listing 15.45 shows the code for the Move Next button.

Listing 15.45. Code for the Move Next Button

The code for the Add button is a little tricky, as Listing 15.46 shows.

Listing 15.46. Code for the Add Button

The AddNew method is performed on the Recordset property of the form. This method creates a buffer in memory that is ready to accept the new data. When the Update method is issued, the record pointer is moved to the new record. Because the CompanyName field is a required field, you must populate it with data before issuing the Update method on the Recordset property.

By setting the bookmark of the form to the Bookmark property of the recordset, you synchronize the form with the new record. In a production environment, you would want to clear out all the text boxes and force the user to save or cancel before the AddNew or Update methods are issued.

The process of deleting a record is quite simple, as Listing 15.47 shows.

Listing 15.47. Deleting a Record

This code verifies that the user actually wants to delete the record and then issues the Delete method on the Recordset property of the form. Because the current record no longer is valid, the code calls the Click event of the cmdNext button.

The last piece of code involved in the form is the code for the Find button, as shown in Listing 15.48.

Listing 15.48. Code for the Find Button

This routine begins by storing the bookmark of the current record to a Variant variable. Users are prompted for the client ID they want to locate, and then the Find method is issued on the Recordset property of the form. If the EOF property is True, the user is warned, and the bookmark of the recordset is set to the value within the Variant variable, returning the record pointer to the position it was in prior to the search. If the client ID is found, the bookmark of the form is synchronized with the bookmark of the Recordset property.

Summary

In this chapter, you learned how to manipulate recordsets via code. The chapter began by contrasting ActiveX Data Objects with Data Access Objects. It continued by introducing you to the ADO model. It explored the different types of ADO recordsets available, highlighting why you would want to use each type.

Next, you learned how to manipulate recordsets using code. The capability to manipulate recordsets behind the scenes is an important aspect of the VBA language. It frees you from the user interface and enables you to control what is going on programmatically. Finally, you learned how to create and modify database objects using code. This is important if the application you are creating requires you to create or modify tables, queries, or other objects at runtime.