run a query?

J

jen

hi,
how to ues code (behind a button)to run a query (or
refresh a query)without user's notice the process?
thanks,jen
 
P

Pieter Wijnen

Don't Use DoCmd.... (DoCmd.OpenQuery or DoCmd.RunSQL)

Use
CurrentDb.QueryDefs("QueryToRun").Execute

HTH
Pieter


anthony said:
use the OnClick property of the button.

Are you refreshing the underlying query of the form? if you are then,
Click to expand...
then you can just put me.requery in the VBA code.
 
J

jen

Thank you !
But I need to run a query outside the form in the
database. Can you tell me how?
Thanks,
jen
-----Original Message-----
use the OnClick property of the button.

Are you refreshing the underlying query of the form? if
Click to expand...
you are then, then you can just put me.requery in the VBA
code.
 
J

jen

Thank you,
but I got the message: Can't execute a select query.
is there any other way?
jen
 
D

David Mann

Pieter Wijnen said:
Don't Use DoCmd.... (DoCmd.OpenQuery or DoCmd.RunSQL)

Use
CurrentDb.QueryDefs("QueryToRun").Execute
Click to expand...

Can you explain the difference / advantage of the latter method, please?

David
 
A

anthony

This is the post from Pieter in case you haven't read it...

Don't Use DoCmd.... (DoCmd.OpenQuery or DoCmd.RunSQL)

Use
CurrentDb.QueryDefs("QueryToRun").Execute

HTH
Pieter

Have you created the query yet? if you have, just plug in the name of the query and replace QueryToRun with it. If you need to create query on the fly, you can use the CreateQueryDef() method to create the query in the code. Let me know if you need to do that.

Hope this helped.
 
P

Pieter Wijnen

basically you don't need to put DoCmd.Setwarnings false / true
also DoCmd has the side effect of triggering the activate event
you can also find out how many records where affected

ie
Set DB = CurrentDB
db.QueryDefs("QUERY").EXECUTE
Debug.Print db.recordsaffected

note however that you have to provide all parameters (including forms
references) when using the execute method of a querydef

dim db as Database
dim qdef as QueryDef
dim parm as Parameter
set db = currentDb
set qdef = db.Querydefs("theQuery")
for each parm in Qdef.Parameters ' all parameters must refer to forms
controls
parm.value = Eval(parm.name)
next
qdef.execute
....

HTH


--
Pieter Wijnen

When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca
http://allenbrowne.com/
 
P

Pieter Wijnen

It seems the error is in the query itself
you can't execute a select query
you need insert/update/delete queries
(DDL statements are also supported (ie create table , drop table etc)

I was assuming that the problem was suppressing "This Query will.." type of
messages

HTH
Pieter

anthony said:
This is the post from Pieter in case you haven't read it...

Don't Use DoCmd.... (DoCmd.OpenQuery or DoCmd.RunSQL)

Use
CurrentDb.QueryDefs("QueryToRun").Execute

HTH
Pieter

Have you created the query yet? if you have, just plug in the name of the
Click to expand...
query and replace QueryToRun with it. If you need to create query on the
fly, you can use the CreateQueryDef() method to create the query in the
code. Let me know if you need to do that.
 
Top