VBA to Add (Save) & Remove a Query in an MS Access DB within Excel with ADO?

[bs0d]

I'm creating a workbook to query data from an Access database. Part of the process to get the data I need, requires creating one query (A), and then creating a query (B) to the query (A). I've not come across the need to save a query (from Excel) to Access before.

I'm sure this can be done, can someone show me how (ADO?) And also how to Remove it when you're done?

If you have multiple users, would it make sense allowing each of them to create and deleting queries? Each user could be requesting different criteria, so the request is unique to them. I planned on adding their username to the query name to keep it unique. Thoughts?

 

[ranman256]

You normally dont do it that way. You build your queries and keep them, and keep using them. Not make 1 , then delete it.
You can just build your 'query' on the fly as an SQL statement in Excel..no need to build /remove the query.

 

[bs0d]

I understand that, but I need to query the results of a query. How can I do that on the fly without saving the initial query for the second query to reference?

 

[ranman256]

If its a simple join, you can do sub query using the IN command. See any Find Duplicates query made by the wizard.

If not, you can build your query defs. (lookup querydefs)
qdf1.sql = "select * from table"
qdf2.sql = "select * from q1"

then get the recordset from qdf2.