ado recordsets l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
ADO Recordsets PowerPoint Presentation
Download Presentation
ADO Recordsets

Loading in 2 Seconds...

play fullscreen
1 / 25

ADO Recordsets - PowerPoint PPT Presentation


  • 262 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'ADO Recordsets' - sandra_john


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
recordset objects
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
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
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
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
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
Types of Cursors
  • Static
  • Dynamic
  • KeySet
  • ForwardOnly
static cursor
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
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
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
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
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
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
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
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
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 seeking strings
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
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
slide19

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

slide20

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)

slide21

Recordsets Syntax for Fields

rst!FirstName = "Carrie"

rst(1) = "Carrie"

rst("FirstName") = "Carrie"

rst.Fields.Item(1).Value = "Carrie"

rst!LastName = "Ohn"

slide22

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

slide23

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

slide24

Deleting from Recordsets

rst.Delete ' that's all folks

slide25

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…