Discussion: Using SQL string in VBA VS passing parameters to query

[Mihail]

Hello !
I started using Access about one year ago.
Thank you ALL for help me all this time.
I wish to thank especially to Smiley, for his patience at least, who help me with the very basic steps at the beginning.

Reading this thread,
http://bytes.com/topic/access/answers/931314-auto-fill-query-increment-number#post3701560
I see that you, Smiley, use a copy-paste solution to insert a query SQL into VBA code in order to set (by VBA) a parameter.
More other experts from here use SQL strings in VBA. So I suspect there are good reasons to do that.

On the other hand, in my opinion, for the maintenance reasons, is better to use the query itself and pass to the query the parameters you need. If at a latter time is needed to modify the query is enough to use the design view for that query and no need to change anything in VBA.
Of course this is available for "nice" SQL which can be generated using the design view interface for queries.

So I ask you, all experts, to enlightened me when a solution is better than the other one, where (when) should use parametric queries and where (when) should use SQL in VBA and, of course, WHY to chose one way or another one.

Thank you very much !

[NeoPa]

This is an interesting question, that often confuses newer database developers when they come into contact with the work of more experienced developers. I remember bumping into this myself in my early days when we had an Access consultant in to handle a particular project.

In many cases a QueryDef (Saved Access Query) can be used in projects wherever a SQL string can. In fact, there are some places (within Domain Aggragate function calls for instance - DLookup(), DMax(), DAvg, etc) where SQL strings are not applicable at all.

More experienced db developers though, being more conversant with SQL and tending to think in SQL, would often prefer to think of a query in SQL and simply create it in the VBA code there and then. The balance of where and when this occurs is generally down to the developer themself, but issues to consider are :

1. How frequently it's used.
2. How complex it is.

If it's called from more than one place - that is the same query - then a QueryDef has more weight. If it's a very short string with little to it then a SQL string has. QueryDefs are pre-optimised, so a large complex query that is called from multiple places (essentially a fundamental part of the project) may even be designed as a QueryDef by very experienced developers as they will see the benefit of avoiding redoing the optimisation phase. It's a judgement call.

Where a SQL string really becomes necessary rather than just preferred, is where the SQL of the query depends on values available only at run-time. Consider a form that allows you to select the table and fields of what you want to see in a report. The SELECT and FROM clauses are both dependent an what is selected, so designing them into a QueryDef (at design-time) would be impossible. Handling that with a SQL string from VBA however, is perfectly reasonable. I'll say at this time that making changes to a QueryDef in code is actually possible too, certainly with an MDB or ACCDB file. It's highly dubious though as a developer typically wants to know what they're working with and the designs of their main objects are an important part of that. Possible then, but to be avoided in almost all situations.

Filtering is another matter entirely of course. Reports and forms certainly, have a WhereCondition (filtering) parameter, which can be passed on opening, that allows a filter to be specified even for a QueryDef. This means that the RecordSource parameter (The source that the form or report is bound to) can easily be a QueryDef, even if filtering is required.

I hope this gives you something to consider. Others may offer their own unique perspective to the discussion.

[Mihail]

Thank you, NeoPa.
I am happy you understand my English and you point the discussion to the right way.
I fully understand what you say. Also I understand that my skill is not enough to see as far as you can.
Of course I expect other posts in order to make (for myself) as good idea as is possible.
Thank you again !

[TheSmileyCoder]

I have never learnt how to combine parameter queries with VBA. So thats one reason for me not using them. I

f a query does not require parameters, I might design and save it as a query object, and then run it from VBA, but often I also like that the SQL for the code is stored with the code. It makes it easier when reading and reviewing my code that I don't have to open a second object.

Thats really my 2 cents on the subject.

[NeoPa]

FYI Smiley - A QueryDef with parameters, when given an object reference in VBA, presents a Parameters() collection such that parameters may be set prior to invoking the query itself. If you ever feel the need to do something requiring that then we here can help you with it I'm sure :-)

[TheSmileyCoder]

Hi Neo. Thanks for the information. I have no doubt you guys could help me figure it out, but so far I haven't really had the need for it (or realised that I should have done it, so thats another reason for me to follow this thread.)

One thing I do remember reading somewhere is that access can more quickly process stored queries then queries with SQL syntax generated through VBA. I haven't confirmed nor denied it myself yet though.

[NeoPa]

That's covered in post #2 Smiley. QueryDefs have the optimisation stored when they're first actually run. This is generally helpful as it saves the delay of Jet preparing the process order/optimisation on the fly. It's only a negative issue when the data is so changed from the first run that an alternative approach would be more efficient on this occasion. The order/optimisation for a SQL string is always assessed whenever it's run.

[Mihail]

I see that my "discussion" is a very short one.
I think that NeoPa fully covered the subject in post #2.
I wish to thank all of you.

[NeoPa]

Thanks for the question Mihail. If you think you're the only person who was ever curious about these issues then you're very much mistaken. I suspect this thread can be a real benefit to many others needing to understand exactly what you asked about. Good for you :-)

[Mihail]

Help me to understand your technique, Smiley.
You encapsulate the SQL into procedures (subroutines) ? Then you pass values for variables via procedure interface ? Or you use another approach ?

[TheSmileyCoder]

Im afraid that part of this may become hard to understand since neither you nor me, are native english speakers.

I really use variation of techniques. Lets say I need to empty out a temporary table, I would simply do:

Currentdb.Excecute "DELETE * FROM TableName",dbFailOnError

Such a query for example I dont think I would ever bother to write out as a stored querydef. I think it would just add unnecessary clutter to the query window.

I think one of the more advanced procedures I have involves:
Creating a document.
First a temporary table is created, and loaded with all of the paragrahs (records) to go into the document.

Find references
Now each paragrach may contain one or more references to other paragrahs in the same or in other documents. I first do a query to identify all paragrahs with references in them, and them to a another temporary table.

Locate documents
Based on this temporary table I identify to which document the reference points.

Build secondary Documents
I then create X temporary tables containing each of the secondary documents, in order to identify the paragraph number of each refence.

Search n replace
I then do a search n replace of the original document paragraph in which the reference was found, replacing the reference with the target documents Name and Paragraph number.

Now I doubt I could do all this without using codes, since I dont know the tables names beforehand. I couldn't possibly define the queries beforehand. (At least I dont think so).


I dont really know if that answered your question, feel free to ask again.