Need help with QueryDef

[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.

 

[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

 

[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.



.

 

[nitisha]

assigning values from the select query to the access form controls

Hi,

I have an access form. On the form we enter a unique number say Id, and click on the button 'fetch'. On the button click we should get the details populated in the form from the database.

Fetching will happen through a simple select query like: 'SELECT * from Test where Id = 20'
the result of the above query will give the column values that needs to be assigned to the controls on the access form.

Database is an access database.

Any suggestions as to how can it be done?

Thanks