Programming the RecordSet Object

1
Programming the RecordSet Object
2
Recordset Object (ADO)

• When you use ADO, you manipulate data almost
  entirely using Recordset objects.
• A Recordset object represents the entire set of
  records from a database table or the results of
  an executed command.
• All Recordset objects are constructed using
  records (rows) and fields (columns).

3
Cursor

• A cursor caches data on a client.
• Provides synchronization mechanisms
• Provides tools to minimize network traffic.
• Define how a cursor is created using the
  CursorLocation and CursorType properties
  pertaining to the Recordset object.
• You must set these properties before opening the
  recordset.

4
The Recordset CursorLocation Property

• Specifies the location of the cursor engine and
  can be set to one of two constants
• adUseClient
• adUseServer
• default

5
The Recordset CursorType Property

• The CursorType property defines the kind of
  cursor that will be opened.
• adOpenStatic creates a static cursor
• adOpenForwardOnly creates a forward only cursor
• adOpenDynamic creates a dynamic cursor
• adOpenKeyset creates a keyset cursor

6
The Recordset LockType Property

• To resolve the conflicts that may arise when two
  users try to change the same records, database
  providers support locking.
• To specify the type of locking, set the LockType
  property to one of the following
• adLockReadOnly (default)
• adLockPessimistic
• adLockOptimistic

7
The Recordset Open Method

• The Open method opens a cursor (recordset).
• Syntax
• recordset.Open Source

8
Private rstCurrent As New ADODB.Recordset Private
Sub Form_Load() Dim conMagnet As New
ADODB.Connection Dim cmdMagnet As New
ADODB.Command conMagnet.Mode
adModeShareDenyNone conMagnet.Provider
"Microsoft.Jet.OLEDB.4.0" conMagnet.Connection
String "Data SourceA\Magnet.mdb"
conMagnet.Open Set cmdMagnet.ActiveConnection
conMagnet cmdMagnet.CommandType
adCmdTable cmdMagnet.CommandText
"tblOrderMaster" rstCurrent.LockType
adLockOptimistic rstCurrent.CursorLocation
adUseClient rstCurrent.CursorType
adOpenKeyset rstCurrent.Open cmdMagnet
Call LoadCurrentRecord End Sub
9
Recordset Navigation Methods

• Navigate through the records in a Recordset with
  the following Recordset methods
• MoveFirst
• MoveLast
• MoveNext
• MovePrevious
• Move
• Examples
• rstCurrent.Move 5
• rstCurrent.MoveFirst
• rstCurrent.MoveLast

10
Recordset BOF and EOF Properties

• The BOF and EOF properties indicate the beginning
  or end of the Recordset.
• The BOF or EOF property is True when you move one
  record past the first or last record.
• If both BOF and EOF are True, there are no
  records in the Recordset.
• Example
• If rstCurrent.BOF True Then
• rstCurrent.MoveFirst
• EndIf

11
Recordset RecordCount Property

• Use the RecordCount property to return the number
  of records in a Recordset object.
• The property returns -1 when ADO cannot determine
  the number of records.
• Reading the RecordCount property on a closed
  Recordset causes an error.
• Example

txtRecordCount.Text rstCurrent.RecordCount
12
Recordset AbsolutePosition Property

• Use the AbsolutePosition property to determine
  the current record number
• The AbsolutePosition property is a Long Integer
  between 1 and the number of records in the
  Recordset.
• Example

txtCurrentRecord.Text rstCurrent.AbsolutePositio
n
13
Recordset Find Method

• recordsetName.Find (criteria)
• Searches a Recordset for the record that
  satisfies the specified criteria.
• If the criteria is met, the recordset position is
  set on the found record otherwise, the position
  is set on the end of the recordset.
• Example
• rstCurrent.Find Member ID 1234

14
Examples

• Dim strName As String
• strName "John Smith"
• rstCurrent.Find "fldID 123"
• rstCurrent.Find "fldName 'John Smith'"
• rstCurrent.Find "fldName '" strName "'"

15
Private Sub cboMemberID_Click() Dim strTemp
As String strTemp cboMemberID.Text
rstCurrent.MoveFirst rstCurrent.Find "Member
ID '" strTemp "'" Call
LoadCurrentRecord End Sub