ADO Recordsets

1
ADO Recordsets
2
Recordset Objects

• Similar to Tables and Queries data
• Using VBA/VBScript your program
• Open a recordset,
• Locate a record
• Update or add a record
• Close, etc

3
Controls on Forms I Recordsets

• The usual job of forms showing data from
  tables/queries
• With recordsets, you must use VBA/VBScript to
  show data in controls
• You fill listboxes, textboxes, etc
• The form doesn't fill them for you
• This is not "bound forms"

4
Controls on Forms II Recordsets

• Another job of forms taking data from controls
  into tables/queries
• With recordsets, you must use VBA/VBScript to
  take form data from the controls and save it
• The listboxes, textboxes, etc are the source of
  tabled data
• But, the form doesn't update the table for you
• This is not "bound forms"

5
Bound forms vs. Recordsets

• Use standard forms for input and output
• Use recordset programming to
• Read data from tables/queries
• Fill controls on forms with this data
• Read data in controls on forms
• Update the tables/queries with user changes to
  the form data
• But, do not use recordset programming instead of
  binding forms to tables/queries. Use it to
  augment forms.

6
Recordset Cursors

• The cursor element controls
• record navigation
• updatability of data
• visibility of changes by other users
• speed of application

7
Types of Cursors

• Static
• Dynamic
• KeySet
• ForwardOnly

8
Static Cursor

• A static copy of a set of records that you can
  use to find data or generate reports
• Additions, changes, or deletions by other users
  are not visible
• MovePrevious is available
• RecordCount property is available

9
Dynamic Cursor

• Additions, changes, and deletions by other users
  are visible
• all types of movement through the recordset are
  allowed, except for bookmarks, if the server
  doesn't support them
• Not a fast cursor
• MovePrevious is available
• RecordCount property is not available

10
ForwardOnly Cursor

• This is the default Cursor
• You only scroll forward through records
• Improves performance when you need to make only
  one pass through a recordset
• Update, AddNew is not available
• MovePrevious is not available
• RecordCount property is not available

11
KetSet Cursor

• Like a dynamic cursor, but faster
• Data changes by other users are visible
• Deleted recs by other users are inaccessible
• Can't see records that other users adduntil
• Refresh the cursor with rst.Resync

12
Forward-type DAO Recordsets

• Identical to Snapshots, except
• Can move only forward
• Rs.MoveNext
• Rs.MoveLast
• Best used for analysis and loading arrays

13
Table-type DAO Recordsets

• Seek to locate a single record, just one
• Fast, must be based on Index
• rs.Index "Names" 'must be in table
• rs.Seek "", strName
• If rs.NoMatch True then .

14
Dynaset-type DAO Recordsets(find numbers)

• Find to locate records, not just one record
• Not as fast as indexed seek, but can be sorted
  for speedier finds by SQL when built
• intID4
• strFind"pkPeopleID" intID
• rs.FindFirst strFind
• If rs.NoMatch True then
• MsgBox "Failed to find " intID
• Endif

15
Dynaset-type DAO Recordsets(find dates)

• Find to locate records, not just one record
• Not as fast as indexed seek, but can be sorted
  for speedier finds by SQL when built
• dteHire01/03/01
• strFind"HireDate" "" dteHire ""
• rs.FindFirst strFind
• If rs.NoMatch True then
• MsgBox "Failed to find " dteHire
• Endif

16
Dynaset-type DAO Recordsets(find strings)

• Find to locate records, not just one record
• Not as fast as indexed seek, but can be sorted
  for speedier finds by SQL when built
• strName"Poynor"
• strFind"LastName" "'" strName "'"
• rs.FindFirst strFind
• If rs.NoMatch True then
• MsgBox "Failed to find " strName
• Endif

17
Beginnings and endings 1/3

• ' our textbook opens recordsets like this
• Dim rs as DAO.Recordset ' any variable name is
  OK
• Dim db as Database ' any variable name is
  OK
• Set db CurrentDb
• Set rs db.OpenRecordset("tblEmployee",dbOpenDyn
  aset)
• ' records are processed as needed
• ' and then the table is closed safely
• rs.Close
• Set rs Nothing

18
Beginnings and endings 2/3

• ' most recordsets are opened something like this
• Dim rec as DAO.Recordset
• Set rec _ Currentdb.OpenRecordset("tblPeople",d
  bOpenDynaset)
• ' .processed as needed .etc etc
• rec.Close
• Set rec Nothing

19
Beginnings and endings 3/3

• ' efficient recordsets are opened with SQL
• Dim recdset as DAO.Recordset
• Set recdset Currentdb.OpenRecordset( _
• "SELECT From tblEmployee " _
• "WHERE HireDate lt 01/01/90 " _
• "ORDER BY HireDate", _
• dbOpenDynaset)
• ' .processed as needed .etc etc
• recdset.Close
• Set recdset Nothing

20
Beginning of File (BOF)
rs.MovePrevious If rs.BOF True Then. ' true
if you tried to go before the first record
Ending of File (EOF)
rs.MoveNext If rs.EOF True Then. ' true if
you tried to go after the last record
21
Arrays created from Recordsets
' here is how to store a recordset
into an array Dim varArrayData as Variant
rs.MoveFirst varArrayData rs.GetRows(1000)
'up to 1000 rows rs.Close ' how many
records and fields were stored? (Chap. 11)
intRecordCount UBound(varArrayData, 2) 1
intFieldCount UBound(varArrayData, 1) 1
'stored as varArrayData(Fields, Records) (Chap.
11)
22
Adding to Recordsets
rs.AddNew fields set to defaults ' maybe
show defaults on form Me!txtField1
rs!Field1 ' if using form for data input, save
' or maybe or maybe just fill in fields
rs!Field1 "Unknown" 'save changes (if
any) to new record rs.Update
23
Deleting from Recordsets
rs.Delete ' that's all folks
24
Safety in Editing/Updating Recordsets
Dim ws As Workspace Set ws
DBEngine.Workspaces(0) On Error GoTo
NetworkError ws.BeginTrans 'open a
transaction rs.Edit ' open edit buffer
' maybe use form inputs
rs!Field1Me!Field1 rs.Update ' write
close buffer ws.CommitTrans 'close
transaction Exit Sub NetworkError
ws.Rollback ' undo the edits
25
Editing/Updating recordsets without transactions
Dim rs as DAO.Recordset Set rs
CurrentDb.OpenRecordset("tblStudent",
dbOpenTable) rs.Index "StudentID"
rs.Seek "", strID If rs.NoMatch Then
MsgBox "Seek failed." Else rs.Edit ' open
edit buffer rs!AnyField txtNewData
rs.Update ' write buffer to recordset End If
rs.Close ' close to prevent problems
26
Safety in Adding to Recordsets transaction
processing
Dim ws As Workspace Set ws
DBEngine.Workspaces(0) On Error GoTo
NetworkError ws.BeginTrans 'open a
transaction rs.AddNew ' fields set to
defaults ' maybe show defaults on form
Me!txtField1 rs!Field1 ' example
ws.CommitTrans 'close the transaction Exit
Sub NetworkError ws.Rollback ' undo the
add
27
Keeping track of record position in recordsets
Dim varBookmark As Variant varBookmark
rs.Bookmark ' remember position rs.MoveFirst
' start at first position Do While Not
rs.EOF ' calculate something you want
rs.MoveNext ' move to next position Loop
rs.Bookmark varBookmark ' restore position
28
Example of Locating Records in Dynasets and
Snapshots
Dim rs As DAO.Recordset Set db CurrentDb() ' or
put CurrentDb in next line Set rs
db.OpenRecordset("tblPeople", dbOpenSnapshot) strN
ame InputBox("Enter a last name to
locate") rs.FindFirst "Last " "'" strName
"'" If rs.NoMatch False Then MsgBox
"Located. " rs!First " " rs!Last Else
MsgBox "Name not found." End If rs.Close
29
Example of Locating Records in Tables
Dim rs As DAO.Recordset Set rs
CurrentDb.OpenRecordset( _
"tblPeople", dbOpenTable) strName
InputBox("Enter a last name to locate") rs.Index
"Names" ' must already be this index in
table rs.Seek "", strName If rs.NoMatch False
Then MsgBox "Located. " rs!First " "
rs!Last Else MsgBox "Name not found." End
If rs.Close