Page 1
Page 2
Having added the insert rows changes, replace the current cmdListRows_Click() subroutine with
the following code :
Private Sub cmdListRows_Click()
Dim rs As ADOCE.Recordset
Dim cnt As Integer
Dim strDisp As String
Dim arb2 As Integer
Dim bookmarkvar ' Must be variant to accept the bookmark type.
Dim bookmarkSet As Boolean
' bookmarkSet-stores whether the bookmark property is supported
' by this recordset.
' Assume that the bookmark property is not supported :
bookmarkset = False
If connOpen = True Then
List1.Clear
Set rs = CreateObject("ADOCE.Recordset.3.0")
On Error Resume Next
' The previous tutorial used a forward-only cursor, since the
' data was just being read and displayed :
' rs.Open "select * from CustomerTable", conn, adOpenForwardOnly, adLockReadOnly
rs.Open "select * from CustomerTable", conn, adOpenKeyset, adLockReadOnly
Do While Not rs.EOF
strDisp = ""
For arb2 = 0 To (rs.Fields.Count - 1)
strDisp = strDisp & rs.Fields(arb2).Value & " : "
Next
List1.AddItem strDisp
If rs.Supports(8192) Then
If rs.Fields(0).Value = "Customer3" Then
bookmarkvar = rs.Bookmark
MsgBox "Bookmark for Customer3 is " & CStr(bookmarkvar), vbOKOnly
bookmarkset = True
End If
Else
MsgBox "Bookmarks are not supported by this ADOCE recordset.", vbOKOnly
End If
rs.MoveNext
Loop
If bookmarkset Then
MsgBox "Before closing recordset let's go back to our bookmarked row", vbOKOnly
rs.Bookmark = bookmarkvar
MsgBox "The value bookmarked is " & rs.Fields(0).Value, vbOKOnly
End If
cnt = rs.RecordCount
rs.Close
Set rs = Nothing
txtDB.Text = cnt & " rows were listed in CustomerTable."
On Error GoTo 0
End If
connClose
End Sub
The above code is where I have chosen to set the bookmark and then return to the bookmarked row,
in order to demonstrate how it works.
A bookmark can only be used in the current recordset, so once the recordset has been
closed the bookmark becomes obsolete. Also, the bookmark for one recordset cannot be used in
another recordset, even if both recordsets come from the same source (table, etc.) or use the
same select statement.
Bookmarks are actually very simple to use. Once you are aware of some of the salient points, like those
mentioned above, and that you must use a recordset that has a cursor that allows you to move
backward and forward within it, you should be able to read the bookmark property
of any particular row in the recordset. When you read the bookmark property of a row, you need to store
this value in a variant variable, since this is the pointer to the row, and it is what enables you to
return to this row.
Provided the recordset remains open, by simply setting the recordset's bookmark property to the value
of the variable where you stored the bookmark value, the cursor will automatically be moved to
the bookmarked row and you will be able to read the field values as you would normally (see above code).
The only other contingency that using bookmarks within a recordset is dependent on, is whether or
not the provider (in other words the database engine) allows the use of bookmarks. Some providers may
also offer bookmarks with certain cursor types but not with others, so be sure to perform a check for
this before attempting to work with bookmarks in any of your recordsets.
To check if your recordset supports the bookmark property simply use the following code
if rs.Supports(8192) then ...
where rs is the name of the recordset, and 8192 is the parameter value that must be passed to the
supports function to determine whether or not the recordset supports bookmarks.
The previous tutorial used a forward-only cursor, since the data was just being read
and displayed. The bookmark property cannot be used with this type of cursor, as already explained.
The other types of cursors used in ADOCE, are a dynamic cursor, a static cursor and a keyset-driven
cursor. The Microsoft help file lists the dynamic cursor as being one for which providers may or may
not support bookmarks.
The above example, we need to be able to move forward or backward (to our bookmark),
in our recordset, so I have chosen to use an Open keyset-driven cursor.
When you run this code click on the 'Create DB' button to create a database. Then click on the
'Create Tbl' button to create the 'CustomerTable' table within the database. Then click on the
'Insert Rows' button to insert 5 rows into the table, as listed in the 'cmdInsertRows_click()'
subroutine code on the previous page.
You should now have a database with a table in it, and 5 rows in the table. When you click on the
'List Rows' button the listbox will be filled with 5 rows with a customer name going from Customer1
through to Customer5. The bookmark I have set is on Customer3. I display a message showing the bookmark
value for Customer 3. By looking at the code you will see how easy it is once we have read through
the entire recordset and displayed its contents, to return to the bookmarked row and display its column
information, in this case I have just displayed the first column containing the customer name (Customer
3).
To return to the bookmarked row, you simply set the recordset's bookmark property back to the bookmarkVar
variable value and then read the recordset as per normal as in the following code :
rs.Bookmark = bookmarkvar
MsgBox "The value bookmarked is " & rs.Fields(0).Value, vbOKOnly
This a very crude example and I am sure you
can find more meaningful applications for bookmarks, but I hope that you
will now see how easy the concept is and will be encouraged to start experimenting
with it.
Previous Page