Objects in Access

General Access objectsData access objects (DAO)

used for data managementdata definition data manipulation

used only in code modules

Commonly Used Data Access Objects

DAO Description

Container Object that contains information aboutother objects

Database 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-typedynaset-typesnapshot-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

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 currentrecord

Moving through recordsets

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 onspecify search criteriaexample

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 methodsFindFirstFindLastFindNextFindPrevious

Using DAO

Method Description

Edit Copies current record to copy buffer toallow editing

AddNew Creates a new record in the copy bufferwith default values (if any)

Update Saves any changes made to the record inthe copy buffer

CancelUpdate Empties the copy buffer without savingany changes

Delete Deletes the current record

Methods to edit recordsets

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