Creating a Recordset

As you did with a Connection object, you have to declare and instantiate a Recordset object before you can use it.

Dim

rsRecordset

As ADODB.Recordset

Set

rsRecordset

= New ADODB.Recordset

Next, you use the Open method to populate the recordset with a particular set of records.

rsRecordset.Open "SELECT * FROM tblSupplier", cnConnection

The Open method has various parameters that determine how the recordset will be created, such as CursorType, CursorLocation, LockType, and Options.

CursorType Property

The CursorType property of the Recordset object indicates what type of movement you can take within a recordset. By default, the CursorType property is set to adOpenForwardOnly, which means that you can only move forward through the records. A forward-only cursor does not allow you to count how many records are in the recordset or navigate back and forth through the recordset. The following code is an example of setting the CursorType property.

rsRecordset.CursorType = adOpenStatic

The following table summarizes the four possible choices for the CursorType property.

Value

Explanation

adOpenForwardOnly

This cursor is read-only and is the fastest type of cursor. However, it allows only for forward movement through the records.

adOpenStatic

This cursor allows forward and backward movement through the records and also allows bookmarks. It doesn't show changes made by other users. This is a client-side cursor.

adOpenKeyset

This cursor allows forward and backward movement through the records and also allows bookmarks. It does not show new or deleted records. It points back to the original data.

adOpenDynamic

This cursor provides complete access to a set of records by showing additions and deletions. It also allows for forward and backward movement through the records.

The CursorLocation Property

The CursorLocation property of the Recordset object specifies where the set of records or record pointers are returned when you open a recordset. ADO supports two values for the CursorLocation property: adUseServer and adUseClient. These return records to the server and client, respectively. You should generally use server cursors except when client cursors are specifically needed. An example of the syntax of this property is shown below.

rsRecordset.CursorLocation = adUseServer

The LockType Property

The LockType property of the Recordset object is what determines whether a recordset is updatable. The following table summarizes the four possible choices for the LockType property.

Value

Explanation

adLockReadOnly

The recordset is read-only and no changes are allowed.

adLockPessimistic

The record in the recordset will be locked as soon as editing begins.

adLockOptimistic

The record in the recordset will be locked when the Update method is issued.

adLockBatchOptimistic

The records will not be locked until a batch update of all records is performed.

An example of the syntax of this property is shown in the following code.

An example of the syntax of this property is shown in the following code.

rsRecordset.LockType = adLockReadOnly

The Options Parameter

The Options parameter of the Recordset object allows you to specify how the provider should evaluate the source argument. If the Options property is not specified, it will be determined at runtime, which is slower. Here is an example showing how to specify the Options parameter.

rsRecordset.Open "SELECT * FROM tblSupplier", cnConnection, Options:=adCmdText

Try It Out Building a Contacts Form Bound to a Recordset

Now that you have a basic idea of various ADO settings, you can put the concepts into practice by building a contacts form that connects to and displays data from a recordset. Assume that the contacts form will be used by a U.S. company that does not deal with foreign addresses.

1. Create a new database by selecting File C> New C> Blank Database and specifying Ch5CodeExamples as the filename.

2. Create a new table in the database by selecting Tables from the Database Window and clicking the Design button. Alternatively, you can select the New button and then choose Design View from the list. The table should be named tblContacts and should have the fields illustrated in Figure 5.3. Note that the field sizes are listed as part of the description for convenience purposes only, so that, when creating the table, you can see what size to set for each field.

3. Open the table from the Database Window and add at least one record to the table.

4. Create a new form named frmContactsBound. Use the toolbox to drag and drop 12 text box controls onto the form. Modify the Name property for each text box to the following: txtLastName, txtFirstName, txtMiddleName, txtTitle, txtAddress1, txtAddress2, txtCity, txtState, txtZip, txtWorkPhone, txtHomePhone, and txtCellPhone, respectively. Also rename the Caption property for the corresponding label of each text box, as shown in Figure 5.4.

5. Add the following code to the Form_Load procedure of the frmContactsBound form. You can do so by selecting the form in the Designer Window, viewing the Properties dialog box for the form, selecting the Events tab, and then selecting the Code Builder option from the On Load event.

E(sjP

Field Name

Data Type

Description

intContactld

AutoNumber

Un-que Id for the contact

rr

titLastf-ianne

Text

Last Name field size 50)

L

titFirstNanne

Text

First Name ¡"Field size 50)

txtMiddleName

Text

Middle Name ¡Field size 50)

bitTitie

Text

Title field size 30)

titAddress 1

Text

Address 1 ¡Field size 100)

titAddress2

Text

Address 2 (Field size 100)

tiitCity

Text

City ¡Fieid size 50)

titState

Text

State (Field size 2)

titZip

Text

Zip {Field size 5)

txtVv'orkPnone

Text

Work Phone ¡Field size 12)

txtHonnePhone

Text

Home Phone ¡Fieid size 12)

txtCellPhone

Text

Celi Phone (Field size 12)

rrr

Figure 5.3

Figure 5.4

Private Sub Form_Load()

Dim cnCh5 As ADODB.Connection Dim rsContacts As ADODB.Recordset Dim strConnection As String

'specify the connection string for connecting to the database

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & CurrentProject.Path &

"\Ch5CodeExamples.mdb;"

'create a new connection instance and open it using the

connection string

Set cnCh5 = New ADODB.Connection

cnCh5.Open strConnection

'create a new instance of a recordset

Set rsContacts = New ADODB.Recordset

'set various properties of the recordset

With rsContacts

'specify a cursortype and lock type that will allow

updates

.CursorType = adOpenKeyset

.CursorLocation = adUseClient

.LockType = adLockOptimistic

'open the recordset based on tblContacts table using

the existing connection

.Open "tblContacts", cnCh5

End With

'if the recordset is empty

If rsContacts.BOF And rsContacts.EOF Then

MsgBox "There are no records in the database."

Exit Sub

'if the recordset is not empty, then bind the

'recordset property of the form to the rsContacts recordset

Else

Set Me.Recordset = rsContacts

End If

'bind the controls of the form to the proper field in the recordset (which has

'the same field names as the tblContacts table from which it was generated)

Me.txtLastName.ControlSource = "txtLastName"

Me.txtFirstName.ControlSource = "txtFirstName"

Me.txtMiddleName.ControlSource = "txtMiddleName"

Me.txtTitle.ControlSource = "txtTitle"

Me.txtAddress1.ControlSource = "txtAddress1"

Me.txtAddress2.ControlSource = "txtAddress2"

Me.txtCity.ControlSource = "txtCity"

Me.txtState.ControlSource = "txtState"

Me.txtZip.ControlSource = "txtZip"

Me.txtWorkPhone.ControlSource = "txtWorkPhone"

Me.txtHomePhone.ControlSource = "txtHomePhone"

Me.txtCellPhone.ControlSource = "txtCellPhone"

End Sub

6. Save the VBA code from the Visual Basic Editor by selecting the Save button from the toolbar.

7. Save the form from the Form Designer by selecting the Save button from the toolbar.

8. Open the form. You should see a screen similar to Figure 5.5.

9. Modify one of the existing records.

10. Click the Add New navigation button (right arrow with asterisk) to add a new record similar to the one shown in Figure 5.6.

Figure 5.5
Figure 5.6
0 0

Post a comment