Running Queries without actually reopneing and closing

M

Matt

I have 3 quueries that I use based on a form's input. 2 of the queries must
run first before the last one. The very last query is exported to Excel,
which then updates charts that are refrshed in the original form. Currently,
I have code that opens each query then closes them after exporting. How can
I run them without opening and closing?

I am trying to avoid users from virwing the logic behind the form.

Thanks,
Matt
 
B

Brendan Reynolds

If these are action (append, update, delete or make table) queries, you can
execute them via either ADO (CurrentProject.Connection.Execute "QueryName")
or DAO (CurrentDb.Execute "QueryName").

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
M

Matt

Great, it works perfetly, Thanks!

Brendan Reynolds said:
If these are action (append, update, delete or make table) queries, you can
execute them via either ADO (CurrentProject.Connection.Execute "QueryName")
or DAO (CurrentDb.Execute "QueryName").

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
Click to expand...
 
M

Matt

I tried to run this on a normal Select query and no good. Is there a way to
automatically execute a Select query without opening and closing?
 
B

Brendan Reynolds

What's the goal? A select query just, well, selects records! :) Unless
you're going to do something with those records, there'd be no point in
running the query. So the answer to your question will depend on what it is
you want to do with the records.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
M

Matt

The 3 queries are very important, they must run in order to output to Excel.
After the data is in Excel, a macro automatically runs which updates 30
charts. These 30 charts are then imported into the original form.

So I really need the queries to run, however I rather run them where the
user cannot see them.
 
B

Brendan Reynolds

I'm still mystified as to why you need to do this, but you could open and
throw away a recordset based on the query. I'm thinking perhaps the reason
for doing this might have something to do with some VBA function being
called from the queries, that's why I've added the MoveLast in the code that
follows, to ensure that all rows are accessed.

Public Sub RunSelectQuery(ByVal strQueryName As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strQueryName)
rst.MoveLast
rst.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
T

tina

if the 3 queries are Select queries, they are not changing the data, only
displaying it, as Brendan said. and if you don't want the user to see the
dataset from the Select query, then why open it? (note that, for a Select
query, "open" and "run" have the same result - displaying a dataset.) pls
explain what you believe is being accomplished by opening the Select
queries.
 
M

Matt

You guys are the greatest.

I always thought you had to open the queries to update them. My form
functions much quicker now.

I do have another problem though, when i export to the Excel file, the file
stays open and causes a problem when rerunning the form queries. What code
can I use to avoid this problem?

thanks again,
matt
 
Top