QueryDefs and Recordsets

[Jim Pockmire]

I would like to create a querydef (to extract records from a table) and then
use the querydef as a recordset (so as to Movenext) through the records Can
I do this without saving the querydef as a query object first?

 

[PC Datasheet]

Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Dim QDF As DAO.QueryDef
Dim Prm As Parameter
Dim SQLStr As String
SQLStr = "....."
Set Db = CurrentDb
Set QDF = Db.QuerfDefs("MyQuery")
QDF.SQL = SQLStr
For Each Prm In QDF.Parameters
Prm.Value = EVAL(Prm.Name)
Next Prm
Set Rst = Db.OpenRecordset("MyQuery")
Rst.MoveNext

 

[Ken Snell [MVP]]

Any reason why you cannot just open a recordset to extract the records,
without the need to create another query object?

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT * FROM Table_Name " & _
"WHERE [FieldName] = 4;"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' loop through the recordset as you wish with code
'
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing