¿Another way to do this? Stored Queries

[shkrebs]

Hi all.

I'm writing almost all my queries in VBA and in this forum I've learned that to save memory it's best saving the queries and then execute them.
Often I'm using one query as a source for the next query (can't remember what it's called ) and all the queries containing parametres and I find this method usefull - let VBA controle the parametres and ACCESS controle the queries.

So I'm using the following structure:

dbs.QueryDefs.Refresh
For Each QueryNameMain In dbs.QueryDefs
If QueryNameMain.Name = "MyQuery" Then
dbs.QueryDefs.Delete QueryNameMain.Name
End If
Next QueryNameMain
strSQL = "SELECT ......."
Set QueryNameMain = dbs.CreateQueryDef("MyQuery", strSQL)DoCmd.OpenQuery QueryNameMain.Name

BUT it's annoys me that I have to delete the whole query, change the value of the parameter and save the query once again - also because I find this method a little unsecure.

Is there another way of "updating" the queries or only this way (delete and write) ??

Best regards
Søren

 

[neil_turner]

Søren,

Not sure if I'm a 100% with you on this! You don't have to create a QueryDef every time you want to open a query, instead just create a new recordset, then call

currentdb.openrecordset(your_query_here)

then check the recordset returned to make sure it has results i.e.

while not rsdata.eof

or

if not (rsdata.eof and rsdata.bof) then

Not sure if this helps you or not!

Cheers
Neil

 

[shkrebs]

Create recordset...

Hi Neil.

I can see what you want to do, but I'm reusing the same query many times but with different value in the WHERE part.
I.e. collecting the salesnumbers for a group of commercials.

Also because the pc's in this company is quite old and I have to be careful with the memory-use, and by storing the queries I don't use to much memory on the pc - no

Best regards
Søren