Special Edition Using Microsoft Office Access 2003

Creating ADODB.Recordset Objects

The concept of database object independence is new to Access. The best way of demonstrating this feature is to compare DAO and ADO code to create a Recordset object from an SQL statement. DAO syntax uses successive instantiation of each object in the DAO hierarchy: DBEngine, Workspace, Database, and Recordset, as in the following example:

Dim wsName As DAO.Workspace Dim dbName As DAO.Database Dim rstName As DAO.Recordset Set wsName = DBEngine.Workspaces(0) Set dbName = wsName.OpenDatabase ("DatabaseName.mdb") Set rstName = dbName.OpenRecordset ("SQL Statement")

As you descend through the hierarchy, you open new child objects with methods of the parent object.

The most common approach with ADO is to create one or more independent, reusable instances of each object in the Declarations section of a form or module:

Private cnnName As New ADODB.Connection Private cmmName As New ADODB.Command Private rstName As New ADODB.Recordset

Note

This book uses cnn as the object type prefix for Connection, cmm for Command, and rst for Recordset. The cmm prefix is used because the cmd prefix traditionally identifies a command button control and the com prefix identifies the MSComm ActiveX control (Microsoft Comm Control 6.0).

Although you're likely to find references to DAO.Recordset dynasets and snapshots in the Access documentation, these terms don't apply to ADODB.Recordset objects. See the CursorType property of the ADODB.Recordset object in the "Recordset Properties" section later in this chapter for the CursorType equivalents of dynasets and snapshots.

After the initial declarations, you set the properties of the new object instances and apply methods Open for Connections and Recordsets, or Execute for Commands to activate the object. Invoking the Open method of the ADODB.Recordset object, rather than the OpenRecordset method of the DAO.Database object, makes ADO objects independent of one another. Object independence and batch-optimistic locking, for instance, let you close the ADODB.Recordset's ADODB.Connection object, make changes to the Recordset, and then re-open the Connection to send only the changes to the underlying tables. Minimizing the number of open database connections conserves valuable server resources. The examples that follow illustrate the independence of top-level ADO members.

Designing a Form Bound to an ADODB.Recordset Object

To create a simple form that uses VBA code to bind a form to a Jet ADODB.Recordset object, follow these steps:

1. Open a new database in Access 2000 format named ADOTest.mdb or the like in your ...\Office11\Samples folder. Add a new form in design mode and save it as frmADO_Jet.

2. Click the Code button on the toolbar to open the VBA editor, and choose Tools, References to open the References dialog.

3. Clear the check box for the reference to the Microsoft ActiveX Data Objects 2.1 Library, scroll to the Microsoft ActiveX Data Objects 2.7 Library, and mark the check box. Close and reopen the References dialog to verify that the new reference has percolated to the select region of the list (see Figure 30.4). Close the References dialog.

   Figure 30.4. If you don't need backward compatibility with Access 2000 and 2002 applications, specify the latest version of ADO (2.7 for this example) as the reference.

4. Add the following code to the Declarations section of the frmADO_Jet Class Module:

   Private strSQL As String Private cnnNwind As New ADODB.Connection Private rstNwind As New ADODB.Recordset

5. Add the following code to create the Form_Load event handler:

   Private Sub Form_Load() 'Specify the OLE DB provider and open the connection With cnnNwind .Provider = "Microsoft.Jet.OLEDB.4.0" .Open CurrentProject.Path & "\Northwind.mdb", "Admin" End With strSQL = "SELECT * FROM Customers" With rstNwind Set .ActiveConnection = cnnNwind .CursorType = adOpenKeyset .CursorLocation = adUseClient .LockType = adLockOptimistic .Open strSQL End With 'Assign rstNwind as the Recordset for the form Set Me.Recordset = rstNwind End Sub

   Note

   The preceding code includes several properties that this chapter hasn't discussed yet. The objective of this and the following sections is to get you started with a quick demonstration of the Form.Recordset property. Properties and methods of the Connection and Recordset objects are the subject of the "Exploring Top-Level ADO Properties, Methods, and Events" section that follows shortly.

6. Return to Access and change to Form view to execute the preceding code. Then open the Properties window and click the Data tab. Your form appears as shown in Figure 30.5, with the first of 91 records selected by the navigation buttons.

   Figure 30.5. The frmADOTest form has its Recordset property set to an ADODB.Recordset object opened on the Northwind.mdb Customers table.

Note

The form's Record Source property value is the SQL statement specified as the argument of the Recordset object's Open method. The Recordset Type property value appears as Dynaset, which isn't a valid ADODB.Recordset type. The enabled Add New Record navigation button confirms that the form is updatable.

Binding Controls to a Recordset Object with Code

Adding the equivalent of bound controls to a form whose Record Source is an ADODB.Recordset object requires that you first add unbound controls and then bind the controls to the form's underlying Recordset with code. To create a simple data display form for the Customers table, do the following:

1. Return to Design view, display the Toolbox, and add seven unbound text boxes to the form. Name the text boxes txtCustomerID, txtCompanyName, txtAddress, txtCity, txtRegion, txtPostalCode, and txtCountry. Change the width of the text boxes to reflect approximately the number of characters in each of the Customer table's fields.

2. Change the label captions to CustID:, Name:, Address:, City:, Region:, Postal Code:, and Country:, respectively. Apply the Bold attribute to the labels for readability (see Figure 30.6).

   Figure 30.6. Add seven unbound text boxes to the frmADO_Jet form.

3. To bind the Control Source property of each text box to the appropriate field of the Customers table, click the Code button and add the following lines of code immediately after the Set Me.Recordset = rstNwind line:

   Me.txtCustomerID.ControlSource = "CustomerID" Me.txtCompanyName.ControlSource = "CompanyName" Me.txtAddress.ControlSource = "Address" Me.txtCity.ControlSource = "City" Me.txtRegion.ControlSource = "Region" Me.txtPostalCode.ControlSource = "PostalCode" Me.txtCountry.ControlSource = "Country"

4. Choose Form view and navigate the Recordset (see Figure 30.7). The Control Source property value of the text boxes displays the field name you specified in the preceding code.

   Figure 30.7. Form view displays field values in the unbound text boxes. The Data page of the Properties window of the txtCustomerID text box shows CustomerID as the Control Source property value.

5. Choose Datasheet view. The seven fields of the text boxes provide data to the columns of the datasheet, and the label captions serve as column headers.

6. Edit one of the CustID values; for example, change BLONP to BONX. If Cascade Update Related Fields for the join between the Customers and Orders tables isn't enabled, a message box states that you can't edit the field (see Figure 30.8).

   Figure 30.8. Datasheet view of the form displays only the fields of the Customers table that have associated text boxes. Changing the value of the primary key without cascading updates displays the error message shown here.

Tip

To emulate a table Datasheet view with code, add to the form text boxes for every field of the table. To open a table-type ADODB.Recordset object, substitute the table name for the SQL statement as the argument of the rstName.Open statement. You also can specify the name of an SQL Server view or Jet QueryDef object.

Connecting to the NorthwindCS MSDE Database

Creating an ADODB.Recordset object with VBA code lets you connect to SQL Server and other client/server RDBMSs in a Jet database or Access project. To substitute the MSDE version of the Northwind sample database for Northwind.mdb, do the following:

1. Start your local MSDE2000 server if it isn't already running.

2. Make a copy of frmADO_Jet as frmADO_MSDE, open frmADO_MSDE in Design view, and open the VBA Editor for frmADO_MSDE.

3. Delete the .Provider = "Microsoft.Jet.OLEDB.4.0" line. For this example, the Open method's argument specifies the OLE DB data provider

4. MSDE uses integrated Windows security by default, so change the .Open CurrentProject.Path & "\Northwind.mdb, Admin line to

   .Open "Provider=SQLOLEDB.1;Data Source=(local);" & _ "Integrated Security=SSPI;Initial Catalog=NorthwindCS"

   (SSPI is an abbreviation for Security Support Provider, Integrated.)

5. Add the following statement after the Set Me.Recordset = rstNwind line:

   Me.UniqueTable = "Customers"

   Tip

   Even if your query returns data from a single table only, you should specify the table as unique. For updatable result sets from Transact-SQL (T-SQL) queries with joins, you must set the UniqueTable property value to specify the "most-many" table. As an example, if your query returns values from one-to-many joins between the Customers, Orders, and Order Details table, Order Details is the "most-many" table. Fields of the Order Details table contribute the uniqueness to the rows of the query result set.

6. Run frmADO_MSDE in Datasheet view and verify that the form is updatable by temporarily editing any cell except primary-key values of the CustID field. You receive a constraint conflict error if you attempt to change a CustomerID value (see Figure 30.9).

   Figure 30.9. When you attempt to edit a primary-key value on which other records depend, you receive the SQL Server message shown here.

Note