HandsOn Finding the Record Position

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

2. In the module's Code window, type the FindRecordPosition procedure shown below.

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

Sub FindRecordPosition()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strConn As String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

Set conn = New ADODB.Connection conn.Open strConn

Set rst = New ADODB.Recordset With rst

.Open "Select * from Employees", conn, adOpenKeyset, _ adLockOptimistic, adCmdText Debug.Print .AbsolutePosition

.Move 3 ' move forward 3 records Debug.Print .AbsolutePosition .MoveLast ' move to the last record Debug.Print .AbsolutePosition Debug.Print .RecordCount .Close End With

Set rst = Nothing conn.Close Set conn = Nothing End Sub

Notice that at the beginning of the recordset, the record number is 1. Next, the FindRecordPosition procedure uses the Move method to move the cursor three rows ahead, after which the AbsolutePosition property returns 4(1 + 3) as the current record position. Finally, the MoveLast method is used to move the cursor to the end of the recordset. The AbsolutePosition property now determines that this is the ninth record (9). The RecordCount property of the Recordset object returns the total number of records (9).

0 0

Post a comment