I want to delete multiple queries. How Do I do it?

[Guest]

Need to kow how to delete multiple queries.

 

[Guest]

Manually or programmatically?
Unfortunately, if it is manual, the process is:

Select the query
Press the Delete Key
Repeat until all queries to be deleted are

 

[Guest]

I would like it to be programmatically, manually takes to long....

 

[Guest]

The basic idea would be a For Next or a Do Loop with the DeleteObject method
to delete each selected query.

What I don't know is how you will select the queries to be deleted. You
will need a way to select them and put their names in an array and loop
through the array or you could use a form list a list box control, read the
names of all the queries in your database into the row source of the list box
and use the ItemsSelected property collection of the list box to feed the
loop to delete the selected queries.

 

[Guest]

So what would the code be or look like?

The basic idea would be a For Next or a Do Loop with the DeleteObject method
to delete each selected query.

What I don't know is how you will select the queries to be deleted. You
will need a way to select them and put their names in an array and loop
through the array or you could use a form list a list box control, read the
names of all the queries in your database into the row source of the list box
and use the ItemsSelected property collection of the list box to feed the
loop to delete the selected queries.

 

[Guest]

It would look a lot like VBA

But, seriously, folks...
The code will depend on the method you want to use to collect the list of
queries to delete. Post back a description of how you want to do that, and I
will be happy to help.

 

[John Nurick]

If there's a need to delete multiple queries under program control, it
sounds as if multiple QueryDefs are being _created_ in the course of
normal operation of the database. Maybe it would be better to avoid this
by building and executing SQL statements on the fly?

 

[Guest]

Good point, John; however, some operations require a query rather than SQL,
TransferSpreadsheet, for example.
To expand on your thought, using SQL where possible and creating query defs
where needed and deleting them when you are done with them would make sense.

One of the techniques I use where queries are required is to have a template
query that I read into a string, modify the string to include sorting and
filtering, and write it back to the querydef that will be use for the
operation. For example:

strSQL = CurrentDb.Querydefs("qtmpSomeQuery").SQL
....Manipulate strSQL Here
CurrentDb.Querydefs("qselSomeQuery").SQL = strSQL
....Use the Query Here

Now we don't build up a collection of various querydefs.

 

[John Nurick]

Same here - except that more often I just build the SQL statement in a
VBA string and then assign it to the querydef's SQL property.

 

[Guest]

That works, but I'm lazy and don't like hand writting SQL Sort of makes me
think of the old COBOL days (the War and Peace of programming languages)

I could cut and paste it into VBA. I did that a couple of times, but then I
had to make changes to the query, so I just left it in the query.
The VBA approach is probably faster because it doesn't have to retrieve the
query.