HandsOn Opening a Recordset Based on a Table or Query

1. In the Database window, press Alt+F11 to switch to the Visual Basic Editor window.

2. In the Visual Basic Editor window, choose Insert | Module.

3. In the module's Code window, type the OpenRst_TableOrQuery procedure shown below.

Part II

4. Choose Run | Run Sub/UserForm to execute the procedure.

Sub OpenRst_TableOrQuery() Dim conn As ADODB.Connection Dim rst As ADODB.Recordset

Set conn = CurrentProject.Connection Set rst = New ADODB.Recordset rst.Open "Employees", conn

Debug.Print "CursorType: " & rst.CursorType & vbCr _ & "LockType: " & rst.LockType & vbCr _ & "Cursor Location: " & rst.CursorLocation

Do Until rst.EOF

Debug.Print rst.Fields(1) rst.MoveNext Loop rst.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

After opening the recordset, it's a good idea to check what type of recordset was created. Notice that the procedure above uses the CursorType, LockType, and CursorLocation properties to retrieve this information. After the procedure is run, the Immediate window displays:

CursorType: 0 LockType: 1 Cursor Location: 2

Notice that because you did not specify any parameters in the recordset's Open method, you obtained a default recordset. This recordset is forward-only (0), read-only (1), and server-side (2). (For more information, refer to the section titled "Opening a Recordset" earlier in this chapter.)

To create a different type of recordset, pass the appropriate parameters to the recordset's Open method. For example, if you open your recordset like this:

rst.Open "Employees", conn, adUseClient, adLockReadOnly you will get the static (3), read-only (1), and client-side (2) recordset. In this recordset, you can easily find out the number of records by using the recordset's RecordCount property:

Debug.Print rst.RecordCount

Next, the procedure above uses the MoveNext method to iterate through all the records in the recordset until the end of file (EOF) is reached. The recordset's EOF property combined with a looping structure allows you to iterate through the recordset until the end of the file is reached.

Creating and Manipulating Databases with ADO

0 0

Post a comment