Need help with QueryDef

H

Harry

I am trying to use VBA to do a Select query.

Dim dbs as dao.database
Dim qdftemp as dao.querydef
Set dbs = CurrentDB
Set dqftemp = dbs.createquerydef("","Select tb1.* from
tb1")
qdftemp.execute dbFailOnError
Set qdftemp = nothing

run these codes return an error "Cannot Execute a Select
Query"

Thanks in advance.
 
C

Chris

Harry,

First, what are you trying to do?

If you want to return the records and look at them in code, use a RecordSet.
Then, either:

Dim Rst as DAO.Recordset
Dim DBS as DAO.Database
dim QDF as DAO.QueryDef

Set dbs = CurrentDB
'Pick one method. First is Preferred
Set rst = dbs.OpenRecordset("Select tb1.* From tb1")
'OR:
'Set QDF = dbs.createquerydef("","Select tbl1.* from tbl1")
'Set rst = qdf.OpenRecordset
'Now you can loop through the recordset
Do Until rst.eof
debug.p rst.fields(0)
rst.movenext
Loop


If you want to run an Action (Update, Insert, Delete) query, then do either
of the following:

Dim DBS as DAO.Database
dim QDF as DAO.QueryDef

Set dbs = CurrentDB
'Pick one method.
dbs.Execute "Update tbl1.Field1 = 'Updated'"
Docmd.RunSQL "Update tbl1.Field1 = 'Updated'"
'OR:
Set QDF = dbs.createquerydef("","Update tbl1.Field1 = 'Updated'")
qdf.Execute
 
H

Harry

Thank you! this is just what I am looking for.
-----Original Message-----
Harry,

First, what are you trying to do?

If you want to return the records and look at them in code, use a RecordSet.
Then, either:

Dim Rst as DAO.Recordset
Dim DBS as DAO.Database
dim QDF as DAO.QueryDef

Set dbs = CurrentDB
'Pick one method. First is Preferred
Set rst = dbs.OpenRecordset("Select tb1.* From tb1")
'OR:
'Set QDF = dbs.createquerydef("","Select tbl1.* from tbl1")
'Set rst = qdf.OpenRecordset
'Now you can loop through the recordset
Do Until rst.eof
debug.p rst.fields(0)
rst.movenext
Loop


If you want to run an Action (Update, Insert, Delete) query, then do either
of the following:

Dim DBS as DAO.Database
dim QDF as DAO.QueryDef

Set dbs = CurrentDB
'Pick one method.
dbs.Execute "Update tbl1.Field1 = 'Updated'"
Docmd.RunSQL "Update tbl1.Field1 = 'Updated'"
'OR:
Set QDF = dbs.createquerydef("","Update tbl1.Field1 = 'Updated'")
qdf.Execute

--
Chris

Please respond to newsgroups, as I
don't check this address very often.



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top