1 / 25

ADO Recordsets

ADO Recordsets. 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

asha
Download Presentation

ADO Recordsets

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADO Recordsets

  2. Recordset Objects • Similar to Tables and Queries: data • Using VBA/VBScript you… • Open a recordset, • Locate a record • Update or add a record • Close

  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. Opening with 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 • Bookmarks are supported • MovePrevious is available • RecordCount property is available • Supports batch updates (SQL Server)

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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)

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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)

  21. Recordsets Syntax for Fields rst!FirstName = "Carrie" rst(1) = "Carrie" rst("FirstName") = "Carrie" rst.Fields.Item(1).Value = "Carrie" rst!LastName = "Ohn"

  22. 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

  23. 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

  24. Deleting from Recordsets rst.Delete ' that's all folks

  25. 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…

More Related