Recordset Objects • Similar to Tables and Queries: data • Using VBA/VBScript you… • Open a recordset, • Locate a record • Update or add a record • Close
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"
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"
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.
Opening with Recordset Cursors • The cursor element controls: • record navigation • updatability of data • visibility of changes by other users • speed of application
Types of Cursors • Static • Dynamic • KeySet • ForwardOnly
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 • Bookmarks are supported • MovePrevious is available • RecordCount property is available • Supports batch updates (SQL Server)
Opening Static Tables ' our textbook opens static-cursor recordsets like this Dim cnn As adodb.Connection Dim rst As New adodb.Recordset Set cnn = CurrentProject.Connection rst.Open "tblPeople",cnn, adOpenStatic ' … records are processed as needed ' and then the table is closed safely rst.Close Set rst = Nothing
Dynamic Cursor • Additions, changes, and deletions by other users are visible • all types of movement through the recordset are allowed • Not a fast cursor • MovePrevious is available • Bookmarks not supported • RecordCount property is not available • Does not support batch updates
Opening Dynamic Tables ' our textbook opens table-recordsets like this Dim cnn As adodb.Connection Dim rst As New adodb.Recordset Set cnn = CurrentProject.Connection rst.Open "tblPeople",cnn,adOpenDynamic, , adCmdTable ' … records are processed as needed ' and then the table is closed safely rst.Close Set rst = Nothing
Opening Dynamic SQL ' SQL recordsets can restrict and order the records as follows Dim strSQL As String strSQL = "SELECT * From tblEmployee " & _ "WHERE HireDate < #01/01/90# " & _ "ORDER BY HireDate" Dim cnn As adodb.Connection Dim rst As New adodb.Recordset Set cnn = CurrentProject.Connection rst.Open strSQL, cnn, adOpenDynamic,,adCmdText ' … process as needed … rst.Close Set rst = Nothing
KeySet Cursor • Like a dynamic cursor, but faster • Bookmarks are supported, unlike dynamic • Data changes by other users are visible • Deleted recs by other users are inaccessible • Can't see records that other users add: • Until you refresh the cursor with rst.Resync • Supports batch updates (SQL Server)
Static/Dynamic/Keyset Cursor Recordsets (finding numbers) • Locate records, not just one record • Should open with SQL for speedier finds rst.MoveFirst Rst.Find "pkPeopleID=" & cboNavigation If rst.EOF or rst.BOF = True then MsgBox "Failed to find " Endif
Static/Dynamic/Keyset Cursor Recordsets(finding dates) • Locate records, not just one record • Should open with SQL for speedier finds dteHire=#01/03/01# strFind="HireDate=" & "#" & dteHire & "#" rst.MoveLast rst.Find strFind ,, adSearchBackward If rst.BOF = True then MsgBox "Failed to find " Endif
Static/Dynamic/Keyset Cursor Recordsets(finding strings) • Locate records, not just one record • Should open with SQL for speedier finds strName="Poynor" strCriteria="LastName=" & "'" & strName & "'" rst.MoveFirst rst.Find strCriteria ,, adSearchForward If rst.EOF = True then MsgBox "Failed to find " Endif
Static/Dynamic/Keyset Cursor Recordsets(seekingstrings) rst.Index = "LastNameIndex" ' this index MUST be hard coded in the table!! rst.Open "tblPeople", cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect strWhich = "first: " ' strWhich is used below strName = InputBox("Enter a last name") rst.Seek strName, adSeekFirstEQ Do Until strName <> rst!LastName ' loop works bc rst is ordered by last name Debug.Print "Sought " & strWhich & rst!FirstName & " " & rst!LastName rst.MoveNext strWhich = "next: " Loop Locate records fast based on a table index
ForwardOnly Cursor • This is the default Cursor • You only scroll forward through records • Improves performance when you make only one pass through a recordset • Bookmarks not supported • Update, AddNew are not available • MovePrevious, Find are not available • RecordCount property is not available • Does not support batch updates
Moving in Recordset (BOF) rst.MovePrevious If rst.BOF = True Then…. ' true if you tried to go before the first record Moving in Recordset (EOF) rst.MoveNext If rst.EOF = True Then…. ' true if you tried to go after the last record
Arrays created from Recordsets ' here is how to store a recordset into an array Dim varArray() as Variant rst.MoveFirst varArray = rst.GetRows rst.Close ' how many records and fields were stored? (Chap. 8) intRecordCount = UBound(varArray, 2) + 1 intFieldCount = UBound(varArray, 1) + 1 'stored as varArray(Fields, Records)
Recordsets Syntax for Fields rst!FirstName = "Carrie" rst(1) = "Carrie" rst("FirstName") = "Carrie" rst.Fields.Item(1).Value = "Carrie" rst!LastName = "Ohn"
Editing Recordsets rst.Open "tblPeople", CurrentProject.Connection, adOpenStatic,adLockOptimistic' necessary to write rst.Find "pkPeopleID=" & 8 If not rst.EOF then rst!Salary = 62000 rst.Update 'save changes rst.CancelUpdate'OR cancel the update
Adding to Recordsets (two-step process) rst.Open "tblPeople", CurrentProject.Connection, adOpenStatic,adLockOptimistic' necessary to write rst.AddNew ' add a blank record ' optional to add values to fields rst!Salary = 52000 rst!Sex = "F" rst!FirstName="Sammin" rst!MiddleName="Janet" rst!LastName="Evening" rst.Update 'save changes rst.CancelUpdate 'OR cancel the new record
Deleting from Recordsets rst.Delete ' that's all folks
Keeping track of record position in recordsets Dim varBookmark As Variant varBookmark = rst.Bookmark ' remember position rst.MoveFirst ' start at first position Do While Not rst.EOF ' calculate something you need rst.MoveNext ' move to next position Loop rst.Bookmark = varBookmark ' restore position ' this will throw an error for dynamic and forward cursors. To prevent the error message (but live with no bookmarks) use this logic: If rs.Supports(adBookmark) Then…