Copy an Access query through VBA?

[fredalina]

I'm finding this a hard question to frame for some reason, though it seems a simple question, so I apologize in advance.

I have a database with one query for each item that we sell (it is cumbersome but necessary for the imports I am forced to do from Excel); each query is titled the name of the item. I want to create a simple process for adding new items, which involves (in part) creating a new query with the new item's name, and using that item name in a single criterion for a field within the query. Otherwise, the query will be identical to the other items' queries.

Is there a way to do this within VBA? I would imagine it involving the SQL and changing the item name there and then saving as a new query, but I don't really know where to begin. Any help you can provide would be appreciated!

 

[boblarson]

You do not need to use a separate query for each item. You can make it generic. First off, please explain the actual complete reason why you think you need a separate query for each item. Then we can make a suggestion as to how you can use a SINGLE QUERY for any of the items.

 

[pbaldy]

"Necessary" is often in the eyes of the beholder, but to do what you describe you'd use a DAO QueryDef. Search here on that and you should find the appropriate code. Rather than copying an existing query, I'd probably embed the basic SQL in the VBA and concatenate the criteria to it.

 

[fredalina]

Oh, I've no doubt I could have set it up more efficiently, probably a LOT more efficiently. This is why I love this place; I always learn something great.

I don't think I can briefly explain the reason it's currently set up with individual queries without either providing WAY too much detail or violating privacy. I'll check out QueryDef and see if it answers for me; if not, I'll take a stab at an explanation.

Thanks both!