Assigning Recordsets Dynamically

You have already seen how to use SQL to build recordsets in the VBA environment. For the sake of review, remember that the recordsets are collected and contained within the Recordset object. (In actuality, the recordset contains a set of pointers to the data required by the Recordset.)

Beginning with Access 2000, you can assign these recordsets dynamically to the form while using VBA. This is a little different from building the SQL code into the form, as you did in the previous section.

Let’s assume that you now want your recordset to show only the customers who live in New York. Furthermore, you want your form (the frmCustomer form used in the previous section) to reflect that recordset. Let’s look at the following code:

Sub runFormNY()   Dim con As ADODB.Connection   Dim recSet As Recordset   Dim strFrmNm As String     Set recSet = New ADODB.Recordset   recSet.CursorType = adOpenKeyset   recSet.LockType = adLockOptimistic    Set con = New ADODB.Connection   con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\BegVBA\thecornerbookstore.mdb;"     recSet.Open "SELECT * FROM tblCustomer WHERE txtState = 'NY'", con     strFrmNm = "frmCustomer"     DoCmd.OpenForm strFrmNm   Set Application.Forms(strFrmNm).Recordset = recSet     recSet.Close   con.Close   Set recSet = Nothing   Set con = Nothing End Sub 

We built the recordset as we did in Chapter 9. However, you may have noticed two new lines that we have not examined before:

  recSet.CursorType = adOpenKeyset   recSet.LockType = adLockOptimistic

The CursorType property controls how you navigate through the recordset and is mandatory when you use the recordset with forms. Essentially, there are four main types of cursors:

• Dynamic (adOpenDynamic) This means that the changes made to the source data are incorporated dynamically into the recordset. So if others change the source data, those changes are available for you to see. You are also free to make any edits necessary to the recordset and to move through it in any direction necessary.

• Keyset (adOpenKeyset) This does not allow the recordset to dynamically incorporate changes made to the source data by others. However, you can make any edits to the recordset that you want.

• Static (adOpenStatic) This does not allow for any changes to the recordset. You can look through it both forward and backward.

• Forward-only (adOpenForwardOnly) This is just as it says. You cannot edit or move backward through the set. You can only move forward.

The preceding list is arranged in order of performance. The more interaction with the recordset, the slower it runs. Dynamic is the slowest and Forward-only the fastest. If you are working with a large amount of data, you may need to make some decisions about which CursorType will work best and still be efficient.

The other new line uses the LockType property. This works with the CursorType property and controls when the record will be locked from use by others. Locking could be used to resolve editing conflicts in a multiuser environment.

The four types of locks are

• adLockReadOnly This locks the whole recordset while editing is taking place.

• adLockPessimistic This locks others out of that specific record while it is being edited.

• adLockOptimistic This prevents conflicts by locking the record while it is being saved. This prevents two people from saving an edited record at the same time.

• adLockBatchOptimistic This locks others out while records are being updated in a batch mode.

I have already talked about error handling. An error will occur if the recordset or record cannot be locked. You would receive one of three errors: 3218, 3260, or 3197.

Here is an interesting question you might want to ponder: In this example, we set a location for the database connection. Let’s assume we are in database A, which contains a tblCustomer and a frmCustomer. Let’s further assume that we set the connection for database B, which also contains a tblCustomer and a frmCustomer. Which tblCustomer and which frmCustomer will we be using when we run the procedure?

Give up?

We will be using the data from the tblCustomer in database B in the frmCustomer of database A. When you establish a connection, you are only establishing it with the data, not all of the objects of the database container. This has enormous implications from a design standpoint. This means that the forms can be in one place and the data in another. Also, you can bring data in from a variety of data sources.