Storing Queries in VBA

[JDubya]

Hi All, This isn't an "I've got a problem" post but more one where I am after some general advice. I recently created (with the help of several helpful members of this forum!) a Sub Procedure in VBA to enable me to automate the Import of some data. The procedure contained a few docmd's and several SQL statements. One of the reasons why I inserted the SQL direct into the procedure and didn't just make a call to the relevant query objects was that I didn't want these task specific queries clogging up the navigation pane, so once the SQL was copied into the procedure (and tested), I deleted the original queries.

However, this has got me thinking! Would it be worthwhile storing all queries as separate procedures or functions in a module. Yes, it can be a bit of a faff getting the syntax right with some of the queries, as the SQL statement doesn't always run "as is" within VBA and you have play around with them. But the upshot is that you can see the inner gubbins of each query at a glance, including all the helpful comments and as already mentioned keep the navigation pane more manageable.

So, am I making a safe assumption or would doing as I suggest create major issues?

Cheers
John

 

[sneuberg]

I can't think of any major issues but I don't see any benefit. Just collapse the queries in the Nav Pane if they bother you. I think that it is much easier to see what a query is doing in the grid view than reading the SQL. Also I've heard that querydefs execute faster as they are precompiled.

I'd say the only benefit to having the SQL in code is that you can more easily see what the code is doing.

 

[stopher]

You can hide queries/table etc. You can also group queries/tables etc and also hide the groups.

 

[JDubya]

Thanks for your post Steve. My issue is that when I create queries I try to give them meaningful names (without making them too long) but I have such an awful memory that a week or so later I can't remember which one is which and what they are for, so I end up having to open them and work out why I created them in the first place. So, having them more easily at hand in VBA and with a good description of the purpose and parameters and query seemed to be a good solution. But I do take your point that this could slow the execution down. Cheers, John

 

[JDubya]

Thanks Stopher, as I've just replied to Steve, it's not just that they get in the way, but that after a while I can't remember what they are for, why I have chosen x criteria etc, so unless there is a serious performance hit, or some fundamental reason why it's bad thing to do, I think I'd rather store them this way, but many thanks for your suggestion.

 

[stopher]

Thanks for your post Steve. My issue is that when I create queries I try to give them meaningful names (without making them too long) but I have such an awful memory that a week or so later I can't remember which one is which and what they are for, so I end up having to open them and work out why I created them in the first place.

You can add a description to each query (right click =>properties.) You can show the descriptions if you like e.g. if you are looking for a particular query (right click at top of panel => view => details)

 

[MarkK]

JDub, I commonly do what you are talking about, is store the SQL that an object requires, as text, inside the object. I don't do it that way exclusively though. In some cases, if multiple objects require the same query, then I'll programmatically create it--if it doesn't already exist.

 

[plog]

...so I end up having to open them and work out why I created them in the first place...I can't remember what they are for, why I have chosen x criteria etc

So you want to store pieces of data related to each other inside of Access? Hmmmm...that is tricky...shot in the dark...May I suggest a table?

 

[Simon_MT]

Where it gets messy is hard coding WHERE statements if you are using FUZZY logic.

I have pop-up Forms with multiple dropdown lists and choose which one you want to use. So I separate the Criteria in VBA and apply the criteria using a standard SQL statement when opening up the From and just add to the criteria upon drilldowns.

It is the criteria that is needs notation. I would be in the same position as John with a plethora of Queries for each selection this streamlines the whole process.

The only exception are Delete or Update Quieries as these are either locked down to a screen record or entirely VBA.

Simon