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.ConnectionString = "Data Source=A:\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. RecordsetBOF 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. RecordsetRecordCount 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. RecordsetAbsolutePosition 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.AbsolutePosition

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