Class Recordset (DAO VBA)

A Recordset object represents the records in a base table or the records that result from running a query.

The classes Database, QueryDef, Recordset2 and TableDef. give access to class Recordset


Dim rst as Recordset
Set rst = Dim strName As String: strName = 
Workspaces(1).Databases(1).OpenRecordset(Name:=strName)

Methods

AddNew - Creates a new record for an updatable Recordset object. 


    Sub AddNewX() 
     
     Dim dbsNorthwind As Database 
     Dim rstEmployees As Recordset 
     Dim strFirstName As String 
     Dim strLastName As String 
     
     Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
     Set rstEmployees = _ 
     dbsNorthwind.OpenRecordset("Employees", dbOpenDynaset) 
     
     ' Get data from the user. 
     strFirstName = Trim(InputBox( _ 
     "Enter first name:")) 
     strLastName = Trim(InputBox( _ 
     "Enter last name:")) 
     
     ' Proceed only if the user actually entered something 
     ' for both the first and last names. 
     If strFirstName <> "" and strLastName <> "" Then 
     
     ' Call the function that adds the record. 
     AddName rstEmployees, strFirstName, strLastName 
     
     ' Show the newly added data. 
     With rstEmployees 
     Debug.Print "New record: " & !FirstName & _ 
     " " & !LastName 
     ' Delete new record because this is a demonstration. 
     .Delete 
     End With 
     
     Else 
     Debug.Print _ 
     "You must input a string for first and last name!" 
     End If 
     
     rstEmployees.Close 
     dbsNorthwind.Close 
     
    End Sub 
     
    Function AddName(rstTemp As Recordset, _ 
     strFirst As String, strLast As String) 
     
     ' Adds a new record to a Recordset using the data passed 
     ' by the calling procedure. The new record is then made 
     ' the current record. 
     With rstTemp 
     .AddNew 
     !FirstName = strFirst 
     !LastName = strLast 
     .Update 
     .Bookmark = .LastModified 
     End With 
     
    End Function

OpenRecordset - Creates a new Recordset object and appends it to the Recordsets collection. 

Dim rst As DAO.Recordset
Set rst = rst.OpenRecordset

Cancel - expression .Cancel

CancelUpdate - Cancels any pending updates for a Recordset object.

Clone - Creates a duplicate Recordset object that refers to the original Recordset object.

Close - Closes an open Recordset.

CopyQueryDef - Returns a QueryDef object that is a copy of the QueryDef used to create the Recordset object represented by the recordset placeholder (Microsoft Access workspaces only).

Delete - Deletes the current record

Edit - Copies the current record from an updatable Recordset object to the copy buffer for subsequent editing.

FillCache - Fills all or a part of a local cache for a Recordset object that contains data from a Microsoft Access database engine-connected ODBC data source (Microsoft Access database engine-connected ODBC databases only).

FindFirst - Locates the first record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only).

FindLast - Locates the last record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only).

FindNext - Locates the next record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only).

FindPrevious - Locates the previous record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only).

GetRows - Retrieves multiple rows from a Recordset object.

Move - Moves the position of the current record in a Recordset object.

MoveFirst - Moves to the first record in a specified Recordset object and make that record the current record.

MoveLast - Moves to the last record in a specified Recordset object and make that record the current record.

MoveNext - Moves to the next record in a specified Recordset object and make that record the current record.

MovePrevious - Moves to the previous record in a specified Recordset object and make that record the current record.

NextRecordset - expression .NextRecordset

Requery - Updates the data in a Recordset object by re-executing the query on which the object is based.

Seek - Locates the record in an indexed table-type Recordset object that satisfies the specified criteria for the current index and makes that record the current record (Microsoft Access workspaces only).

Update - expression .Update(UpdateType, Force)

Properties

Fields (Default member) - Returns a Fields collection that represents all stored Field objects for the specified object.

AbsolutePosition sets or returns the relative record number of a Recordset object's current record.

BatchCollisionCount expression .BatchCollisionCount

BatchCollisions expression .BatchCollisions

BatchSize expression .BatchSize

BOF returns a value that indicates whether the current record position is before the first record in a Recordset object.

Bookmark sets or returns a bookmark that uniquely identifies the current record in a Recordset object.

Bookmarkable returns a value that indicates whether a Recordset object supports bookmarks, which you can set by using the Bookmark property.

CacheSize sets or returns the number of records retrieved from an ODBC data source that will be cached locally.

CacheStart sets or returns a value that specifies the bookmark of the first record in a dynaset-type Recordset object containing data to be locally cached from an ODBC data source (Microsoft Access workspaces only).

Collect

Connection returns the Connection object that corresponds to the database.

DateCreated returns the date and time a base table was created (Microsoft Access workspaces only). Read-only Variant.

EditMode returns a value that indicates the state of editing for the current record.

EOF returns a value that indicates whether the current record position is after the last record in a Recordset object.

Filter sets or returns a value that determines the records included in a subsequently opened Recordset object (Microsoft Access workspaces only).

hStmt

Index sets or returns a value that indicates the name of the current Index object in a table-type Recordset object (Microsoft Access workspaces only).

LastModified returns a bookmark indicating the most recently added or changed record.

LastUpdated returns the date and time of the most recent change made to a base table. Read-only Variant.

LockEdits sets or returns a value indicating the type of locking that is in effect while editing.

Name returns the name of the specified object.

NoMatch indicates whether a particular record was found by using the Seek method or one of the Find methods (Microsoft Access workspaces only).

ODBCFetchCount

ODBCFetchDelay

PercentPosition sets or returns a value indicating the approximate location of the current record in the Recordset object based on a percentage of the records in the Recordset.

Properties returns the Properties collection of the specified object.

RecordCount returns the number of records accessed in a Recordset object, or the total number of records in a table-type Recordset object. or TableDef object.

RecordStatus expression .RecordStatus

Restartable returns a value that indicates whether a Recordset object supports the Requery method, which re-executes the query on which the Recordset object is based.

Sort sets or returns the sort order for records in a Recordset object (Microsoft Access workspaces only).

StillExecuting expression .StillExecuting

Transactions returns a value that indicates whether an object supports transactions.

Type sets or returns a value that indicates the operational type or data type of an object.

Updatable returns a value that indicates whether you can change a DAO object.

UpdateOptions expression .UpdateOptions

ValidationRule sets or returns a value that validates the data in a field as it's changed or added to a table (Microsoft Access workspaces only).Read/write String.

ValidationText sets or returns a value that specifies the text of the message that your application displays if the value of a Field object doesn't satisfy the validation rule specified by the ValidationRule property setting (Microsoft Access workspaces only).

Fields - A Fields collection contains all stored Field objects of an Index, QueryDef, Recordset, Relation, or TableDef object.

Properties - A Properties collection contains all the Property objects for a specific instance of an object.

QueryDef - A QueryDef object is a stored definition of a query in a Microsoft Access database engine database.