Transcript presentation source

Objects in Access
General Access objects
Data access objects (DAO)
used for data management
data definition
data manipulation
used only in code modules
Commonly Used Data Access
Objects
DAO
Description
Container
Database
Object that contains information about
other objects
The open database
DBEngine
The database engine
Property
An object property
QueryDef
A saved query
Commonly Used Data Access
Objects
DAO
Description
Recordset
Set of records defined by a table or query
Relation
Relationship between 2 tables or queries
Tabledef
A saved table
User
A user account in the database engine
Workspace
An active database session
Using DAO
Using the current database
declare an object variable of type Database
use the CurrentDb function to store a reference to
the current database
example
Dim dbs As Database
Set dbs = CurrentDb( )
Using DAO
Looping through collections
uses For Each statement, similar to a For Next
loop
example
Dim dbs As Database
Dim conTest As Container
Set dbs = CurrentDb()
For Each conTest In dbs.Containers
Debug.Print “Container: “; conTest.Name
Next
Using DAO
Recordsets
records belonging to a table or query
most commonly used types
table-type
dynaset-type
snapshot-type
Using DAO
Table-type recordsets
default type for any recordset object
can use indexes to speed up the process of searching
for records
example
Dim rec As Recordset
Set rec = db.OpenRecordset(“Students”)
Using DAO
Dynaset-type recordsets
can contain a table or the result of a query
can be edited and the results will be reflected in the
underlying tables
useful if the recordset object is very large
example
Set rec = db.OpenRecordset(“Students”,
dbOpenDynaset)
Using DAO
Snapshot-type
not updateable
example
Set rec = db.OpenRecordset(“Students”,
dbOpenSnapshot)
Using DAO
Moving through recordsets
Method
Description
rec.MoveNext Makes next record current record
MovePrevious Makes previous record current record
MoveFirst
Makes first record current record
MoveLast
Makes last record current record
Move n
Makes n records away the current
record
Using DAO
RecordsetClone method
alternative to OpenRecordset method
creates copy of recordset that can be manipulated
independently of the form’s recordset
Bookmark property
what Access uses instead of record numbers
stored as an array of bytes
used to synchronize recordset objects which are
clones of each other
Using DAO
Finding records in table-type recordsets
Seek method used with indexed fields
select indexed field to search on
specify search criteria
example
rec.Index = “Price”
rec.Seek “=“,
curPrice
Using DAO
Finding records in dynasets and snapshots
Find method
Works on non-indexed fields for all types of
recordsets
Four methods
FindFirst
FindLast
FindNext
FindPrevious
Using DAO
Methods to edit recordsets
Method
Edit
Description
Copies current record to copy buffer to
allow editing
AddNew
Creates a new record in the copy buffer
with default values (if any)
Update
Saves any changes made to the record in
the copy buffer
CancelUpdate Empties the copy buffer without saving
any changes
Delete
Deletes the current record
Using DAO
Editing field values in a recordset
example
rec.Edit
rec!strField = NewValue
rec.Update
Using DAO
With … End With
logic structure often used with DAOs, but can be
used with controls and collections