Access.2007.VBA.Bibl..

Forward-only

Forward-only recordsets are similar to snapshot-type recordsets, except that you only move

through the records in a forward direction.

Dynamic

Dynamic recordsets represent the results of updatable queries, possibly based on more than one

table. You can use these recordsets to add, edit, or delete records from one or more base tables, and

so can other users.

QueryDefs ....................................................................................................

QueryDefs correspond to queries in the Access interface. Though you can create dynaset-type

recordsets based directly on queries, and for select queries that works fine, QueryDefs offer extra

functionality: you can create a query on the fly, in VBA code (for example, to filter by a value

entered or selected on a form), and then use that QueryDef as the data source for a recordset. You

can even create a make-table query in code, and execute it to create a table, for circumstances

where you need a table to work with. This eliminates the need for numerous filtered queries, and

also lets you work around various problems with creating recordsets based on parameter queries.

The following procedure creates a QueryDef programmatically, using a SQL string as the data

source, and returns the number of records; it is useful in determining whether there are any

records in a filtered query, before taking an action. I call this procedure frequently in code in the

sample databases for this book:

Public Function CreateAndTestQuery(strTestQuery As String, _

strTestSQL As String) As Long

On Error Resume Next

Delete old query:

Set dbs = CurrentDb

dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler

Create new query:

Set qdf = dbs.CreateQueryDef(Name:=strTestQuery, _

sqltext:=strTestSQL)

Test whether there are any records:

Set rst = dbs.OpenRecordset(Name:=strTestQuery)

With rst

Part II Writing VBA Code to Exchange Data between Office Components