Refreshing an existing query

[Cyrax1033]

I have a database that is fully operational (thanks to everyone's help) now I'm doing some housekeeping. What I'm wanting to change is one query function; I have a form that performs a search based off criteria and uses VBA to generate an SQL string which is then passed to a listbox as its rowsource (nice summary of returned results). The user then presses a command button that exports the results to a report--when the button is pressed I did was create a query from the generated SQL statement using the function

myDB.CreateQuer yDef("queryName ", sqlString)

the query is created, then the report is opened based off that query. When the user closes the report the query is deleted using the function:

myDB.QueryDefs. Delete "queryName"

but when another search is run the query is re-created (same name) and the process repeats.

What I found is that MS Access does not re-allocate it's empty object space which will eventually produce memory problems. I know one can use the Compact and Repair utility in MS Access to reallocate the memory but I want to find a solution to where rather than deleting the query and re-creating it, is it possible to simply update the query with a different parameter and refresh the query?

[Rabbit]

I forget the actual syntax but you can change the querydef.SQL rather than deleting and recreating the query def.

[MMcCarthy]

I forget the actual syntax but you can change the querydef.SQL rather than deleting and recreating the query def.

Set qdf = myDB.QueryDef(" MyQuery")

qdf.sql "SELECT..."

[Rabbit]

Set qdf = myDB.QueryDef(" MyQuery")

qdf.sql "SELECT..."

Thanks Mary. I was in a meeting and didn't have the time to look it up.

[ADezii]

Set qdf = myDB.QueryDef(" MyQuery")

qdf.sql "SELECT..."

I'm not nit-picking, Mary but you must refer to the specific item within the Collection (above code will not work):

Dim qdf As QueryDef

Set qdf = CurrentDb.Query Defs("MyQuery")
Debug.Print qdf.SQL OR qdf.SQL = "SELECT..."

[MMcCarthy]

I'm not nit-picking, Mary but you must refer to the specific item within the Collection (above code will not work):

Dim qdf As QueryDef

Set qdf = CurrentDb.Query Defs("MyQuery")
Debug.Print qdf.SQL OR qdf.SQL = "SELECT..."

Sorry missed the 's' when typing. I assumed from previous code that user had declared MyDB as CurrentDB

[Cyrax1033]

I've been away for quite a while--my apologies. I am so thankful for the code snipplet! It has solved a major memory problem, MAJOR! It would only take a couple of searches to accumulate 10MB, but thankfully we can keep it down to 3MB! Thanks again everyone!

[MMcCarthy]

I've been away for quite a while--my apologies. I am so thankful for the code snipplet! It has solved a major memory problem, MAJOR! It would only take a couple of searches to accumulate 10MB, but thankfully we can keep it down to 3MB! Thanks again everyone!

You're welcome.