ado recordsets. recordset objects similar to tables and queries: data using vba/vbscript you…...
TRANSCRIPT
![Page 1: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/1.jpg)
ADO Recordsets
![Page 2: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/2.jpg)
Recordset Objects
• Similar to Tables and Queries: data
• Using VBA/VBScript you…– Open a recordset, – Locate a record– Update or add a record– Close
![Page 3: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/3.jpg)
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"
![Page 4: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/4.jpg)
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"
![Page 5: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/5.jpg)
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.
![Page 6: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/6.jpg)
Opening with Recordset Cursors
The cursor element controls:• record navigation• updatability of data• visibility of changes by other users• speed of application
![Page 7: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/7.jpg)
Types of Cursors• Static
• Dynamic
• KeySet
• ForwardOnly
![Page 8: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/8.jpg)
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)
![Page 9: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/9.jpg)
Opening Static Tables
' our textbook opens static-cursor recordsets like thisDim cnn As adodb.ConnectionDim rst As New adodb.Recordset Set cnn = CurrentProject.Connectionrst.Open "tblPeople",cnn, adOpenStatic
' … records are processed as needed ' and then the table is closed safely
rst.Close Set rst = Nothing
![Page 10: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/10.jpg)
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
![Page 11: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/11.jpg)
Opening Dynamic Tables
' our textbook opens table-recordsets like thisDim 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
![Page 12: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/12.jpg)
Opening Dynamic SQL
' SQL recordsets can restrict and order the records as follows
Dim strSQL As StringstrSQL = "SELECT * From tblEmployee " & _ "WHERE HireDate < #01/01/90# " & _ "ORDER BY HireDate" Dim cnn As adodb.ConnectionDim rst As New adodb.Recordset Set cnn = CurrentProject.Connectionrst.Open strSQL, cnn, adOpenDynamic,,adCmdText ' … process as needed …rst.CloseSet rst = Nothing
![Page 13: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/13.jpg)
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)
![Page 14: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/14.jpg)
Static/Dynamic/Keyset Cursor Recordsets (finding numbers)
• Locate records, not just one record• Should open with SQL for speedier finds
rst.MoveFirstRst.Find "pkPeopleID=" & cboNavigationIf rst.EOF or rst.BOF = True then MsgBox "Failed to find "Endif
![Page 15: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/15.jpg)
Static/Dynamic/Keyset Cursor
Recordsets (finding dates)
• Locate records, not just one record• Should open with SQL for speedier findsdteHire=#01/03/01#strFind="HireDate=" & "#" & dteHire & "#"rst.MoveLastrst.Find strFind ,, adSearchBackward If rst.BOF = True then MsgBox "Failed to find "Endif
![Page 16: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/16.jpg)
Static/Dynamic/Keyset Cursor
Recordsets (finding strings)
• Locate records, not just one record• Should open with SQL for speedier findsstrName="Poynor"strCriteria="LastName=" & "'" & strName & "'"rst.MoveFirstrst.Find strCriteria ,, adSearchForward If rst.EOF = True then MsgBox "Failed to find "Endif
![Page 17: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/17.jpg)
Static/Dynamic/Keyset Cursor
Recordsets (seeking strings)
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
![Page 18: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/18.jpg)
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
![Page 19: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/19.jpg)
Moving in Recordset (BOF)
rst.MovePrevious
If rst.BOF = True Then….
' true if you tried to go before the first record
rst.MoveNext
If rst.EOF = True Then….
' true if you tried to go after the last record
Moving in Recordset (EOF)
![Page 20: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/20.jpg)
' 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)
Arrays created from Recordsets
![Page 21: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/21.jpg)
rst!FirstName = "Carrie"
rst(1) = "Carrie"
rst("FirstName") = "Carrie"
rst.Fields.Item(1).Value = "Carrie"
rst!LastName = "Ohn"
Recordsets Syntax for Fields
![Page 22: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/22.jpg)
rst.Open "tblPeople",CurrentProject.Connection,adOpenStatic,adLockOptimistic ' necessary to write
rst.Find "pkPeopleID=" & 8If not rst.EOF then rst!Salary = 62000
rst.Update 'save changes
rst.CancelUpdate 'OR cancel the update
Editing Recordsets
![Page 23: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/23.jpg)
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
Adding to Recordsets(two-step process)
![Page 24: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/24.jpg)
rst.Delete ' that's all folks
Deleting from Recordsets
![Page 25: 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](https://reader036.vdocuments.us/reader036/viewer/2022062421/56649e2e5503460f94b1e86e/html5/thumbnails/25.jpg)
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…
Keeping track of record position in recordsets