Using ADODB to Work with Data

In this section, you will explore the ADO object model and how to use the objects therein to work with data in underlying databases.

The ADO Object Model

Figure 5-1 provides an overview of the ADO object model. At the highest level of the object model, several collections exist: Connection, Recordset, Command, Record, and Stream.

Figure 5-1

The ADO object library is sometimes referred to as the ADODB library. In order to use the features of ADO, you must have a reference to one of the ADO library versions in your project. You can add a reference to an ADO library by selecting Tools References from Visual Basic Editor and choosing a library from the list. Figure 5-2 shows an example with a reference set to version ADO version 2.7.

Figure 5-2

Several versions of ADO may exist on your computer, such as 2.1, 2.5, 2.7, and 2.8. The version you choose for your project depends on the version of Access that others in your organization are using.

If everyone is using Access 2007 or higher, you should use version 2.8. If everyone is using Access 2002 or higher, you should use version 2.7. If some people are using Access 2000, then you may want to use ADO version 2.5. If some are using Access 97, you may want to use ADO version 2.1.

Tip 

The examples in this book use ADO 2.8 and earlier. There may be other releases of ADO that come with later versions of Windows and/or Office in the near future. The code examples used herein should still work in the same or similar fashion if you are using one of these later releases.

The Connection Object

The Connection object is the highest level of the ADO object model. The Connection object allows you to establish a connection to a data source. You must declare a Connection object before you can use it, as shown here:

  Dim cnConnection as ADODB.Connection 

After declaring a Connection object, you must then instantiate a new Connection object, as follows:

  Set cnConnection = New ADODB.Connection 

Once instantiated, a Connection object can be opened to connect to Access and other databases. You open a connection by using the Open method of the Connection object. The Open method accepts various parameters, including a connection string, user id, password, and options.

Connecting to Access Databases

You specify the data source for the connection using a connection string. The connection string is then used in the Open method to open the data source. In the following example, the connection string connects to the Microsoft Access database for the current project called Ch5CodeExamples.mdb:

  Dim strConnection As String strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _                 "Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.accdb;" cnConnection.Open strConnection 

Connecting to Other Databases

Access can be used as a front end to various other databases, such as SQL Server. The example that follows shows a connection string that you might use to connect to a SQL Server database called Pubs on an instance named SQLServerName using integrated Windows security:

  Dim strConnection As String strConnection = "Provider=sqloledb;Data Source=SQLServerName;" & _                 "Integrated Security=SSPI;Initial Catalog=Pubs"                  cnConnection.Open strConnection 

The Command Object

The Command object represents a SQL statement, query, or stored procedure that is executed against a data source. Just like a Connection object, you must declare and instantiate a Command object before you can use it:

  Dim cmdCommand As ADODB.Command Set cmdCommand = New ADODB.Command 

After a new Connection object is instantiated, you can assign the Command object to an open connection and call the Execute method to execute a SQL statement, query, or stored procedure against the data source. This concept will be illustrated in more detail later in this chapter when you create SQL statements and use ADO to execute them. In addition, the comprehensive case study in Chapter 14 that deals with SQL Server will illustrate how to use the Command object to pass parameters to SQL Server stored procedures.

The Recordset Object

The Recordset object allows you to retrieve a set of records from a data source based on specified criteria. Recordsets can either be connected or disconnected. With connected recordsets, a connection to the database is maintained, and changes to the recordset are made to the underlying data source. With disconnected recordsets, the connection from the database is closed, and you work with a local copy of the information. You can use both connected and disconnected recordsets to display data to a user on a form, for example. Examples later in this chapter will illustrate the difference between connected and disconnected recordsets.

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. These parameters must be set prior to opening the recordset for them to be effective.

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.

Open table as spreadsheet

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.

Open table as spreadsheet

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:

  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 Office Button New Blank Database and specifying Ch5CodeExamples as the file name.

2. Create a new table in the database by selecting the Create ribbon on the toolbar and then choosing Table from the Tables grouping. 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.

   
   Figure 5-3

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

4. Create a new Blank 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.

   
   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. Make sure that you also add the reference to ADO from the Tools, References menu option in the VB Editor.

     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.ACE.OLEDB.12.0;" & _                 "Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.accdb;" '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.

   
   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-6

How It Works

This example binds the frmContactsBound form to an ADO recordset. Now I will show you how it works. All the code to make this application work is in the Form_Load event.

 Private Sub Form_Load()

First, you declared new Connection and Recordset objects, along with a variable to store the connection string:

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

Next, you specified a value for the connection string that points to the current Access database:

 'specify the connection string for connecting to the database strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _                 "Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.accdb;"

At this point, you created a new connection instance and opened the connection:

 'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection

The next set of code instantiated a new Recordset object and sets various properties that affect how the Recordset is populated:

 '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

After you set the CursorType, CursorLocation, and LockType properties, the recordset was opened with the contents of the tblContacts table, using the existing connection. If the recordset did not contain any records, a message would be displayed.

 '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 was not empty, the next set of code bound the Recordset property of the form to the ADO recordset called rsContacts:

 '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

When you bound the recordset to the form, all the fields in the recordset became available for binding to the appropriate controls. However, the form does not know which controls should be bound to which recordset fields until you set the ControlSource property of the applicable controls. The next set of code assigned the ControlSource property of each text box to the respective field in the recordset, which was generated from the underlying tblContacts table:

 '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

Next, you opened the form in View mode and updated and added records. If you open the underlying tblContacts table, you will see that the changes you made were correctly saved, as shown in Figure 5-7.

Figure 5-7

Did you notice that you did not have to write any of the code for the navigation and update features? Access handled this code for you automatically because the form was bound to the recordset. Binding a form to a recordset is a very quick and easy way to implement a basic user interface and does not require you to write code for navigation and database updates.

However, using bound forms presents some serious drawbacks. One drawback is that a database connection remains open the entire time you are working with the application. In applications with multiple users but limited licenses, this can be a problem. Another drawback is that records may remain locked longer than necessary because you are always maintaining the open database connection. If you walk away from your desk with the program open to a particular record, you might keep someone else from updating that same record because you have it locked. In a later example, you will explore how to create a disconnected recordset to help eliminate some of these problems.

You also need to make sure to close the table and form views before working on other portions of code because those views hold an exclusive lock on the database. You will receive an Access Denied error if you try to perform an operation needing to access the database when the database is locked by another operation.

Counting Records in a Recordset

The RecordCount property of the Recordset object returns the number of records in the recordset. Suppose that you have the following procedure:

  Sub TestRecordCount() 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.ACE.OLEDB.12.0;" & _                 "Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.accdb;" '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     .CursorType = adOpenStatic     'open the recordset based on tblContacts table using the existing connection     .Open "tblContacts", cnCh5 End With 'print the number of records to the Immediate Window Debug.Print "The total number of records is: " & rsContacts.RecordCount 'close the database connection cnCh5.Close 'set the recordset and connection to nothing Set rsContacts = Nothing Set cnCh5 = Nothing End Sub 

After the recordset is created, the code determines how many records are in the recordset using the RecordCount property. The number of records is then displayed in the Immediate Window, as shown in Figure 5-8.

Figure 5-8

Tip 

If you remove the line specifying the CursorType, then a -1 will be returned as the record count since the recordset will default to a forward-only recordset.

Navigating through a Recordset

The Recordset object has various methods that allow you to navigate among the records, assuming of course that the recordset is set to a CursorType that can be navigated. The following list contains four methods for navigation that can be used:

  rsRecordset.MoveFirst rsRecordset.MoveLast rsRecordset.MoveNext rsRecordset.MovePrevious 

Before using the preceding record navigation methods, check the BOF and EOF properties of the Recordset. If the BOF property of the recordset is True, no current record exists and the current position is one prior to the first record. If the EOF property is True, no current record exists and the current position is one past the last record. If both BOF and EOF are True, the recordset is completely empty. You have to make sure the recordset is not at BOF or EOF before attempting to access the recordset contents, such as the recordset navigation, or you will get an error. The following shows you how to avoid such errors:

  If NOT rsRecordset.BOF and NOT rsRecordset.EOF Then rsRecordset.MoveFirst End If 

Adding, Editing, and Deleting Records in a Recordset

After you create a recordset, you can add, edit, and delete records. You already learned in the prior example that if the recordset is bound to the Recordset property and corresponding controls on a form, the moment you change the record on the form, it is updated in the underlying database. You did not have to write the update code to make this work.

ADO also has several methods that allow you to perform adds, edits, and deletions from within your code with the AddNew, Update, and Delete methods of the Recordset object. I will briefly introduce each of these methods, and then I will explain each in detail by using a hands-on example.

Adding a New Record with the AddNew Method

The AddNew method of the Recordset object adds an empty record to the recordset that can then be filled in with additional information:

  rsRecordset.AddNew 

If the recordset is connected to a data source, then the record is added to the underlying data source. Otherwise, the new record is saved in the local recordset and must be updated with the underlying data source or the new record will be lost. This is called working with a disconnected recordset and is explained in detail in an upcoming example.

Updating an Existing Record with the Update Method

The Update method of the Recordset object updates the current record. If the Update method is used in conjunction with the AddNew method, the information from the empty record is moved to the end of the recordset.

  rsRecordset.Update 

If the recordset is connected to a data source, the changes are saved in the underlying data source. If the recordset is disconnected from the data source, the changes are saved in the local recordset and must be updated in Batch or other mode with the underlying data source or the changes will be lost.

Deleting an Existing Record with the Delete Method

The Delete method of the Recordset object deletes the current record:

  rsRecordset.Delete 

Again, if the recordset is connected to a data source, then the deletion occurs on the underlying data source. If the recordset is disconnected from the data source, the record is deleted from the local recordset and must later be removed from the underlying data source.

Try It Out-Building an Unbound Contacts Form Using a Disconnected Recordset

Now, it’s time to build a contacts form that is based on a disconnected recordset.

1. Select the frmContactsBound form you created previously, right-click, and select Copy from pop-up menu. Then, click Paste and name the new form frmContactsUnbound.

2. Open frmContactsUnbound. Delete the current code in the On Load event of the Form from the Event tab of the Properties dialog box or from the Visual Basic Editor.

3. Set the Record Selectors, Navigation Buttons, and Dividing Lines properties of the form to No, as shown in Figure 5-9.

   
   Figure 5-9

4. Add seven command buttons to the form. Set the Name properties of each command button to cmdMoveFirst, cmdMovePrevious, cmdMoveNext, cmdMoveLast, cmdAddNew, cmdDelete, and cmdSaveAll, respectively. Change the Caption property of each command button to Move First, Move Previous, Move Next, Move Last, Add New, Delete, and Save Changes To Database, respectively. Change the Picture property of the four navigation buttons to existing pictures called Go To First 1, Go Previous, Go To Next 1, and Go To Last 1, respectively. Instead of the picture buttons, you can just use text as the caption if desired. The form should look similar to the form illustrated in Figure 5-10.

   
   Figure 5-10

5. Add the following code to the General Declarations section of the form:

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

6. Add the following code to the Form_Load event of the form:

     Private Sub Form_Load() strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _                 "Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.accdb;" '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 = adLockBatchOptimistic     'open the recordset based on tblContacts table using the existing connection     .Open "tblContacts", cnCh5     'disconnect the recordset     .ActiveConnection = Nothing End With 'if the recordset is empty If rsContacts.BOF And rsContacts.EOF Then     Exit Sub Else     'move to the first record     rsContacts.MoveFirst     'populate the controls on the form     Call PopulateControlsOnForm End If 'close the database connection and release it from memory cnCh5.Close Set cnCh5 = Nothing End Sub 

7. Add the following event procedures to the class module for the Form.

     Private Sub cmdAddNew_Click() 'add a new record to the local disconnected recordset Call AddRecord End Sub Private Sub cmdDelete_Click() 'delete the current record from the local disconnected recordset Call DeleteRecord End Sub Private Sub cmdMoveFirst_Click() 'move to the first record in the local disconnected recordset Call MoveToFirstRecord End Sub Private Sub cmdMoveLast_Click() 'move to the last record in the local disconnected recordset Call MoveToLastRecord End Sub Private Sub cmdMoveNext_Click() 'move to the next record in the local disconnected recordset Call MoveToNextRecord End Sub Private Sub cmdMovePrevious_Click() 'move to the previous record in the local disconnected recordset Call MoveToPreviousRecord End Sub Private Sub cmdSaveAll_Click() 'save all changes made to the local disconnected recordset 'back to the database Call SaveAllRecords End Sub 

8. Add the following procedures to the class module for the form:

     Sub AddRecord() 'add a new record to the local disconnected recordset rsContacts.AddNew 'commit the new empty record to the local disconnected recordset rsContacts.Update 'clear the current controls on the form so the 'user can fill in values for the new record Call ClearControlsOnForm End Sub Sub DeleteRecord() 'delete the record from the local disconnected recordset rsContacts.Delete 'commit the deletion to the local disconnected recordset rsContacts.Update 'move to the first record since the current one has been deleted rsContacts.MoveFirst 'populate the controls on the form Call PopulateControlsOnForm End Sub 

9. Add the following navigation procedures to the class module for the form:

     Sub MoveToFirstRecord() 'before performing move operation, save the current record Call SaveCurrentRecord 'move to the first record in the local disconnected recordset If Not rsContacts.BOF And Not rsContacts.EOF Then     rsContacts.MoveFirst     'populate the controls on the form with the current record     Call PopulateControlsOnForm End If End Sub Sub MoveToLastRecord() 'before performing move operation, save the current record Call SaveCurrentRecord 'move to the last record in the local disconnected recordset If Not rsContacts.BOF And Not rsContacts.EOF Then     rsContacts.MoveLast     'populate the controls on the form with the current record     Call PopulateControlsOnForm End If End Sub Sub MoveToPreviousRecord() 'before performing move operation, save the current record Call SaveCurrentRecord 'move to the previous record in the local disconnected recordset 'if not already at the beginning If Not rsContacts.BOF Then     rsContacts.MovePrevious     'populate the controls on the form with the current record     Call PopulateControlsOnForm End If End Sub Sub MoveToNextRecord() 'before performing move operation, save the current record Call SaveCurrentRecord 'move to the next record in the local disconnected recordset 'if not already at the end If Not rsContacts.EOF Then     rsContacts.MoveNext     'populate the controls on the form with the current record     Call PopulateControlsOnForm End If End Sub 

10. Add the following procedures to the class module for the form:

      Sub PopulateControlsOnForm()     'Populate the controls on the form with the values of the     'current record in the local disconnected recordset.     'Use the same field names as the tblContacts table from     'which it was generated.     If Not rsContacts.BOF And Not rsContacts.EOF Then         Me.txtLastName = rsContacts!txtLastName         Me.txtFirstName = rsContacts!txtFirstName         Me.txtMiddleName = rsContacts!txtMiddleName         Me.txtTitle = rsContacts!txtTitle         Me.txtAddress1 = rsContacts!txtAddress1         Me.txtAddress2 = rsContacts!txtAddress2         Me.txtCity = rsContacts!txtCity         Me.txtState = rsContacts!txtState         Me.txtZip = rsContacts!txtZip         Me.txtWorkPhone = rsContacts!txtWorkPhone         Me.txtHomePhone = rsContacts!txtHomePhone         Me.txtCellPhone = rsContacts!txtCellPhone     ElseIf rsContacts.BOF Then         'past beginning of recordset so move to next record         rsContacts.MoveNext     ElseIf rsContacts.EOF Then         'past end of recordset so move back to previous record         rsContacts.MovePrevious     End If End Sub Sub ClearControlsOnForm() 'clear the values in the controls on the form Me.txtLastName = "" Me.txtFirstName = "" Me.txtMiddleName = "" Me.txtTitle = "" Me.txtAddress1 = "" Me.txtAddress2 = "" Me.txtCity = "" Me.txtState = "" Me.txtZip = "" Me.txtWorkPhone = "" Me.txtHomePhone = "" Me.txtCellPhone = "" End Sub Sub SaveCurrentRecord() 'save the values in the controls on the form to the current record 'in the local disconnected recordset. If Not rsContacts.BOF And Not rsContacts.EOF Then     rsContacts!txtLastName = Me.txtLastName     rsContacts!txtFirstName = Me.txtFirstName     rsContacts!txtMiddleName = Me.txtMiddleName     rsContacts!txtTitle = Me.txtTitle     rsContacts!txtAddress1 = Me.txtAddress1     rsContacts!txtAddress2 = Me.txtAddress2     rsContacts!txtCity = Me.txtCity     rsContacts!txtState = Me.txtState     rsContacts!txtZip = Me.txtZip     rsContacts!txtWorkPhone = Me.txtWorkPhone     rsContacts!txtHomePhone = Me.txtHomePhone     rsContacts!txtCellPhone = Me.txtCellPhone End If End Sub Sub SaveAllRecords() 'Save current record to local disconnected recordset Call SaveCurrentRecord 'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection 'set the disconnected recordset to the reopened connection Set rsContacts.ActiveConnection = cnCh5 'save all changes in the local disconnected recordset back 'to the database rsContacts.UpdateBatch 'disconnect the recordset again Set rsContacts.ActiveConnection = Nothing 'close the database connection and release it from memory cnCh5.Close Set cnCh5 = Nothing End Sub 

11. Add the following event procedure to the form:

      Private Sub Form_Unload(Cancel As Integer) Dim intResponse As Integer 'prompt the user to save changes intResponse = MsgBox("Save All Changes To Database?", vbYesNo) If intResponse = vbYes Then     'save all local records in disconnected recordset back     'to the database in a batch update     Call SaveAllRecords ElseIf intResponse = vbNo Then     MsgBox "Unsaved changes were discarded."     End If 'release the recordset from memory Set rsContacts = Nothing End Sub 

12. Save all changes in the Visual Basic Editor by selecting Save from the toolbar.

13. Save all changes in the form from design view by selecting Save from the toolbar.

14. Open the form in View mode so that a screen like that in Figure 5-11 appears and displays some data.

    
    Figure 5-11

15. Make changes to the values on the screen and then navigate to another record. When you return to the record, note whether the value is still changed.

16. Close the form without saving changes. See if the changes you previously made are present.

17. Make changes to the values on the screen and then select the Save All Changes To Database option.

How It Works

First, you made a copy of the existing frmContactsBound and renamed it to frmContactsUnbound. You then added some additional controls, namely seven command buttons to the form. The command buttons were labeled for record navigation, add, delete, and save functionality.

Next, you added code to the General Declarations section of the form for a Recordset object, Connection object, and connection string variable. These were placed in the General Declarations section because they need to remain in scope as long as the form is open.

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

Next, you added the Form_Load event of the form where the connection was opened and a new recordset instantiated:

 Private Sub Form_Load() strConnection = "ProviderProvider=Microsoft.ACE.OLEDB.12.0;" & _                 "Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.accdb;" '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

You then set various properties of the Recordset object, such as the CursorType to specify that the cursor should be client side and the LockType to specify the recordset should be batch optimistic. The recordset was then opened and disconnected from the data source by setting the ActiveConnection property to nothing.

 'set various properties of the recordset With rsContacts     'specify a cursortype and lock type that will allow updates     .CursorType = adOpenKeyset     .CursorLocation = adUseClient     .LockType = adLockBatchOptimistic     'open the recordset based on tblContacts table using the existing      connection     .Open "tblContacts", cnCh5     'disconnect the recordset     .ActiveConnection = Nothing End With

With the recordset disconnected from the data source, the recordset is simply stored in local memory. Any changes made to a disconnected recordset are not automatically saved in the original data source.

Next, you added code to call a procedure to populate the controls on the form with the values in the recordset:

 'if the recordset is empty If rsContacts.BOF And rsContacts.EOF Then     Exit Sub Else     'move to the first record     rsContacts.MoveFirst     'populate the controls on the form     Call PopulateControlsOnForm End If

The database connection was then closed and released from memory:

 'close the database connection and release it from memory cnCh5.Close Set cnCh5 = Nothing End Sub

You then added various event procedures to call the rest of the code when the user selects different options on the form. For example, the cmdAddNew_Click event was added to call the AddRecord procedure when the user clicks the Add New button on the form. A similar procedure was added for Delete.

 Private Sub cmdAddNew_Click() 'add a new record to the local disconnected recordset Call AddRecord End Sub

Various event procedures were added to handle navigation through the recordset. For example, the cmdMoveFirst_Click event was added to call the procedure to move to the first record:

 Private Sub cmdMoveFirst_Click() 'move to the first record in the local disconnected recordset Call MoveToFirstRecord End Sub

Other similar procedures were added for Move Last, Move Next, and Move Previous. An event procedure was also added for Save to cause the SaveAllRecords procedure to execute when the Save button on the form is clicked:

 Private Sub cmdSaveAll_Click() 'save all changes made to the local disconnected recordset 'back to the database Call SaveAllRecords End Sub

Next, you added the procedures that really implement the heart of the functionality for the unbound form. The AddRecord procedure is called from the button’s OnClick event after the user clicks the Add New button on the form. The AddRecord procedure uses the AddNew method to add a new record to the local disconnected recordset and then commits the new empty record to the local disconnected recordset using the Update method.

 Sub AddRecord() 'add a new record to the local disconnected recordset rsContacts.AddNew 'commit the new empty record to the local disconnected recordset rsContacts.Update

The controls on the form are then cleared so that the user can enter values for the new record:

 'clear the current controls on the form so the 'user can fill in values for the new record Call ClearControlsOnForm End Sub

The DeleteRecord procedure is called when the Click event of the cmdDelete button runs. This procedure deletes the current record in the local recordset using the Delete method and then commits the change to the local recordset using the Update method.

 Sub DeleteRecord() 'delete the record from the local disconnected recordset

 rsContacts.Delete 'commit the deletion to the local disconnected recordset rsContacts.Update

The procedure then repositions the recordset to the first record and displays that record on the form to the user:

 'move to the first record since the current one has been deleted rsContacts.MoveFirst 'populate the controls on the form Call PopulateControlsOnForm End Sub

Various navigation procedures were then added to handle moving through the disconnected recordset. For example, the MoveToFirstRecord gets called when the Click event of the cmdMoveFirst button is fired. This procedure saves the current record in the local recordset before moving to the first record.

 Sub MoveToFirstRecord() 'before performing move operation, save the current record Call SaveCurrentRecord 'move to the first record in the local disconnected recordset If Not rsContacts.BOF And Not rsContacts.EOF Then     rsContacts.MoveFirst     'populate the controls on the form with the current record     Call PopulateControlsOnForm End If End Sub

Again, remember that the saves so far are just updating the local copy of the recordset but not the underlying database table that the records came from. You will see in a moment how the updates can be saved back to the original data source.

The PopulateControlsOnForm procedure set the text box controls on the form to the current values in the recordset. In other words, this code displays the values in the recordset to the user on the form:

 Sub PopulateControlsOnForm()     'Populate the controls on the form with the values of the     'current record in the local disconnected recordset.     'Use the same field names as the tblContacts table from     'which it was generated.     If Not rsContacts.BOF And Not rsContacts.EOF Then         Me.txtLastName = rsContacts!txtLastName         Me.txtFirstName = rsContacts!txtFirstName         Me.txtMiddleName = rsContacts!txtMiddleName         Me.txtTitle = rsContacts!txtTitle         Me.txtAddress1 = rsContacts!txtAddress1         Me.txtAddress2 = rsContacts!txtAddress2         Me.txtCity = rsContacts!txtCity         Me.txtState = rsContacts!txtState         Me.txtZip = rsContacts!txtZip         Me.txtWorkPhone = rsContacts!txtWorkPhone         Me.txtHomePhone = rsContacts!txtHomePhone         Me.txtCellPhone = rsContacts!txtCellPhone     ElseIf rsContacts.BOF Then         'past beginning of recordset so move to next record         rsContacts.MoveNext     ElseIf rsContacts.EOF Then         'past end of recordset so move back to previous record         rsContacts.MovePrevious     End If End Sub

The ClearControlsOnForm empties out the data entry controls to allow a user to add a new record:

 Sub ClearControlsOnForm() 'clear the values in the controls on the form Me.txtLastName = "" Me.txtFirstName = "" Me.txtMiddleName = "" Me.txtTitle = "" Me.txtAddress1 = "" Me.txtAddress2 = "" Me.txtCity = "" Me.txtState = "" Me.txtZip = "" Me.txtWorkPhone = "" Me.txtHomePhone = "" Me.txtCellPhone = "" End Sub

The SaveCurrentRecord procedure saves the values that are in the controls on the form into the current record in the local disconnected recordset.

 Sub SaveCurrentRecord() 'save the values in the controls on the form to the current record 'in the local disconnected recordset. If Not rsContacts.BOF And Not rsContacts.EOF Then     rsContacts!txtLastName = Me.txtLastName     rsContacts!txtFirstName = Me.txtFirstName     rsContacts!txtMiddleName = Me.txtMiddleName     rsContacts!txtTitle = Me.txtTitle     rsContacts!txtAddress1 = Me.txtAddress1     rsContacts!txtAddress2 = Me.txtAddress2     rsContacts!txtCity = Me.txtCity     rsContacts!txtState = Me.txtState     rsContacts!txtZip = Me.txtZip     rsContacts!txtWorkPhone = Me.txtWorkPhone     rsContacts!txtHomePhone = Me.txtHomePhone     rsContacts!txtCellPhone = Me.txtCellPhone End If End Sub

The SaveAllRecords procedure is executed from the Click event of the cmdSaveAll button on the form. This procedure is responsible for updating the database with the current version of all records in the disconnected recordset. First, changes are saved to the current record in the local recordset, and a connection is reestablished with the database:

 Sub SaveAllRecords() 'Save current record to local disconnected recordset Call SaveCurrentRecord 'create a new connection instance and open it using the connection string Set cnCh5 = New ADODB.Connection cnCh5.Open strConnection

To reconnect the recordset to the underlying data source, the ActiveConnection property is assigned to the current open connection:

 'set the disconnected recordset to the reopened connection Set rsContacts.ActiveConnection = cnCh5

After it is reconnected to the underlying data source, the UpdateBatch method is executed to cause all the local changes to be updated in the original data source:

 'save all changes in the local disconnected recordset back 'to the database rsContacts.UpdateBatch

With the updates completed, the recordset is again disconnected to save an open connection and the database connection is again released:

 'disconnect the recordset again Set rsContacts.ActiveConnection = Nothing 'close the database connection and release it from memory cnCh5.Close Set cnCh5 = Nothing End Sub

As an additional feature, code was placed under the Form_Unload event to make sure that the reader knows to save changes before closing the form:

 Private Sub Form_Unload(Cancel As Integer) Dim intResponse As Integer 'prompt the user to save changes intResponse = MsgBox("Save All Changes To Database?", vbYesNo) If intResponse = vbYes Then     'save all local records in disconnected recordset back     'to the database in a batch update     Call SaveAllRecords ElseIf intResponse = vbNo Then     MsgBox "Unsaved changes were discarded."     End If 'release the recordset from memory Set rsContacts = Nothing End Sub

Notice how in this instance, you had to write the code to perform record navigation and record updates because the recordset was disconnected from the underlying data source. This is certainly more time-consuming than the prior bound recordset example, which handled record navigation and record updates automatically. However, I recommend that you use disconnected recordsets and handle data interaction without keeping a connection open to the database. This helps you write an application that can support more users and that can more easily migrate to more advanced databases like SQL Server. Later in this chapter, you will see another example of using a disconnected recordset as well as SQL statements to handle unbound forms.

Sorting, Finding, and Filtering Records in a Recordset

The Recordset object has various properties and methods that allow you to sort, filter, and find records. Some examples of these properties will now be discussed.

The Sort Property

The Sort property allows you to modify the order in which the records appear in the recordset.

Try It Out-Sorting Records for the Unbound Contacts Form

Look at an example of how to sort the rsContacts recordset that you used previously.

1. In the code for the Form_Load event procedure of frmContactsUnbound, immediately following the line setting the ActiveConnection property, add the following Sort command:

    With rsContacts     'specify a cursortype and lock type that will allow updates     .CursorType = adOpenKeyset     .CursorLocation = adUseClient     .LockType = adLockBatchOptimistic     'open the recordset based on tblContacts table using the existing connection     .Open "tblContacts", cnCh5     'disconnect the recordset     .ActiveConnection = Nothing         'sort the recordset         .Sort = "txtLastName, txtFirstName, txtMiddleName" End With 

2. Now reopen the form and navigate through the records.

How It Works

The Sort method of the Recordset object allows you to specify one or more fields by which to sort the recordset. The preceding example sorts the contacts by last name, first name, and middle name. I hope you noticed that when you ran the form again, the order of the records was now alphabetical based on those three fields.

The Find Method

The Find method of the Recordset object allows you to navigate to a particular record that matches certain criteria. Here is a sample procedure that uses the Find method to locate a record that has a value for intContactId of 2.

 Sub TestFind() Dim rsContacts As ADODB.Recordset 'create a new instance of a recordset Set rsContacts = New ADODB.Recordset 'set various properties of the recordset With rsContacts     .CursorType = adOpenStatic     'open the recordset based on tblContacts table using the existing connection     .Open "tblContacts", CurrentProject.Connection End With 'find a contact with the intContactId value of 2 rsContacts.Find "[intContactId] = 2" 'output a message to the Immediate Window regarding find results If rsContacts.EOF Then     Debug.Print "Specified record not found" Else 'record was found - display some info     Debug.Print "Contact Id: " & rsContacts!intContactId & _             " Last Name: " & rsContacts!txtLastName & _             " First Name: " & rsContacts!txtFirstName End If 'close the recordset rsContacts.Close 'set the recordset and connection to nothing Set rsContacts = Nothing End Sub 

The Find method does not remove any records from the database, but instead just navigates you to a different record. Notice how the preceding example does not use a separate connection object but uses the existing connection for the project to create the recordset. This is an acceptable approach if you will always be working with the same database of your current project.

When you run the preceding procedure from the Immediate Window, you see results similar to those shown in Figure 5-12.

Figure 5-12

The Filter Property

The Filter property allows you to filter the recordset to a smaller subset that meets a certain criteria. For example, you might want to filter your recordset to last names starting with a certain letter.

 Sub TestFilter() Dim rsContacts As ADODB.Recordset 'create a new instance of a recordset Set rsContacts = New ADODB.Recordset 'set various properties of the recordset With rsContacts     .CursorType = adOpenStatic     'open the recordset based on tblContacts table using the existing connection     .Open "tblContacts", CurrentProject.Connection End With 'filter the recordset to contain only records with 'last names starting with D rsContacts.Filter = "txtLastName Like 'D*'" 'output a message to the Immediate Window regarding find results If rsContacts.EOF Then    Debug.Print "No records met that criteria." Else 'record was found - display some info for each record     Do Until rsContacts.EOF         Debug.Print "Contact Id: " & rsContacts!intContactId & _             " Last Name: " & rsContacts!txtLastName & _             " First Name: " & rsContacts!txtFirstName         rsContacts.MoveNext     Loop End If 'close the recordset rsContacts.Close 'set the recordset and connection to nothing Set rsContacts = Nothing End Sub

The preceding example uses the Filter property to filter the recordset down to records that start with D. To prove the filter worked, all the records remaining are displayed in the Immediate Window, as shown in Figure 5-13.

Figure 5-13

Using Bookmarks to Mark and Locate Data

Bookmarks allow you to mark a location in a recordset and return to that location later. For example, suppose that you have a variable called varPosition that has been declared as a variant. You can save the location of the current record in a bookmark:

 varPosition = rsContacts.Bookmark

And then return to it later by setting the Bookmark property equal to the saved position:

 rsContacts.BookMark = varPosition