Query free database (1 Viewer)

[nirmal]

Can we design a database where we do not design a query, instead we use the SQL view of a query in vba code, so the code works more faster.
Secondly no chance of accidental deletion of any query designed

 

[isladogs]

Yes you can use SQL in place of saved queries both in code and in record sources for forms and reports.
In fact that is how I normally do things especially if compiling my FE as an ACCDE as the SQL will then be unavailable to users
However, using SQL will not necessarily be faster and may at times be slower as saved queries are optimised behind the scenes by the database engine. See my article comparing the speeds of each method: Speed comparison tests - saved query vs SQL vs query def
Also, sometimes it is necessary to use saved queries especially where you have a number of stacked queries based on earlier queries.
In the end, the decision is partly personal preference and partly practicality...rather than just based on speed.

 

[theDBguy]

Hi. Just to add another voice, the short answer is a Yes, you can do that. The decision just comes down to whether the benefits outweigh the efforts and potential drawbacks. Cheers!

 

[Pat Hartman]

so the code works more faster.

Not sure where you got this idea. Querydefs are like compiled programs. The first time you run a saved querydef, Access calculates an execution plan and saves it so it can be reused the next time you run the query. By contrast, every single time you run an embedded query, Access must calculate an execution plan first. It doesn't take much time but you should know that embedded SQL incurs MORE overhead, not less and so is less efficient than stored querydefs.

Coming from the mainframe world, I was quite used to embedded SQL when I learned Access. Then I learned that Access does things the way it does for a reason and the reasons are not capricious. It was that epiphany that convinced me that if I was going to use Access, I should probably do things the "Access" way rather than trying to mold Access into my vision of how the world should work.

There are times when I use embedded SQL but normally it is because the SQL is dynamic rather than static. My complex search forms always use dynamic SQL. I also have a couple of queries with very complex WHERE clauses that Access takes it upon itself to rewrite to make even more complex if I save them as a querydef after switching to QBE view so I save those as SQL Strings. I still use the saved querydef but I save it in SQL view rather than QBE view and as long as I never switch to QBE view, Access doesn't try to "help" me by rewriting the WHERE clause. The saved SQL string is so I don't have to retype the WHERE clause when I fix up the querydef.

If the SQL is static, meaning that you only provide parameter values to control data selection, you will save yourself a lot of work (and some execution delay to calculate an execution plan) if you organize your querydefs efficiently and nest and reuse them.

If you are afraid of your own clumsiness (users should NEVER even see the navigation pane, so you don't need to worry about users deleting stuff), then hide the querydefs to make it harder for you to be careless.

 

[NauticalGent]

However, using SQL will not necessarily be faster and may at times be slower as saved queries are optimised optimized behind the scenes by the database engine.

 

[CJ_London]

My comment would be that you develop your app using queries and the resultant query plan is constructed on test data - typically a few records all nicely populated . Then it gets floated in the real world with thousands of records and not fitting the test data norm. Ergo the query plan is not optimised for the real world. So the query is not as efficient as you would want.

the query plan overhead is very small but each time it is run it is optimised for the current data targets

for me, dynamic queries win overall

 

[isladogs]

@NG
Glad you agree but remember that in the UK, we still practise 'correct spellings' for words like minimise, maximise, optimise and colour

 

[NauticalGent]

@NG
Glad you agree but remember that in the UK, we still practise 'correct spellings' for words like minimise, maximise, optimise and colour

Just so you know I am watching!

 

[The_Doc_Man]

I'll add that if you want to use ANY of the Domain Aggregate functions (vice SQL Aggregates), you MUST have a QueryDef because you cannot run a Domain Aggregate against an SQL string.

However, I think there is a middle ground to be had. If I have a query that isn't going to change no matter what I do, I put it into a QueryDef. If I have a complex JOIN-based query based on, say, cascading combo boxes, I'll build a basic named query as my base but then build a layered query on the base and have the layer do the filtration. There IS such a thing as trying to NOT re-invent the wheel.

I always build stored queries when I'm using forms that will draw data from linked tables. That way I can do things like impose a specific order of record appearance, which is something you can't do so easily from a table-based form. Even when the query draws every field from the single table on which it is based, I can do sorting and filtration (of a non-dynamic style) in the query before giving the record to the form. And that is (in my experience) easier than always trying to build a dynamic query that, despite BEING dynamic, always does the same thing.

 

[Pat Hartman]

My comment would be that you develop your app using queries and the resultant query plan is constructed on test data - typically a few records all nicely populated . Then it gets floated in the real world with thousands of records and not fitting the test data norm. Ergo the query plan is not optimised for the real world. So the query is not as efficient as you would want.

That is why the last thing you do before releasing a new version of the app is compile it. That essentially decompiles all the querydefs. That allows Access to keep up with the growth in your BE. My process downloads a new FE each time the user opens the app so essentially every querydef gets a new execution plan every day but only once since the users keep the app open all day.

 

[CJ_London]

are you saying that compiling vba also decompiles (i.e. removes the execution plan) from queries? Didn't think the two were connected. I decompile-compact-recompile, is that what you mean?

 

[isladogs]

@Pat Hartman
I'm not sure that your last statement is correct.

Compacting removes all saved execution plans.
So if you compact your application before releasing it, all query execution plans will be redone when first run.

However to a large extent, this is academic in my opinion. Having run repeated tests (see below), the differences in the time taken for saved queries & SQL statements is negligible. In fact SQL is often slightly faster ... despite query optimisation by the database engine

For example, see my article Query vs SQL vs Query Def

 

[Pat Hartman]

Thanks, I meant compact but compile doesn't generate the execution plays. The first run does that.

Over time, Access has gotten much more efficient with the execution plans. This used to be a huge bloat creator but that has been fixed. It really all comes down to how many times the same query gets run in any given day. So, using querydefs is not so important for efficiency as it once was but they are still more efficient for organizing your table access if you create them using an orderly naming scheme and reuse them where possible. Instead of having to search your code and change all instances of x to y, you can probably change a single querydef if you haven't been sloppy.

 

[Galaxiom]

However to a large extent, this is academic in my opinion. Having run repeated tests (see below), the differences in the time taken for saved queries & SQL statements is negligible. In fact SQL is often slightly faster ... despite query optimisation by the database engine

Total time isn't the only thing that matters. How much memory and actual processor time is used would also be factors, especially with multiple users running on remote desktop (or equivalent) in a virtualised environment where these resources are rationed to users rather than having a glut of resources on a single machine.

 

[nirmal]

Sirs, I am still not able to get the message
Whether to go with existing queries or go for VBA with SQLA code

 

[NauticalGent]

Sirs, I am still not able to get the message
Whether to go with existing queries or go for VBA with SQLA code

I will let those you contributed respond, but if you are looking for opinions, given the reasons you supplied for considering SQL statements instead of QueryDefs (stored/saved queries), I would suggest you use QueryDefs.

 

[isladogs]

To summarise, its largely personal preference
Speed will be similar whether you use SQL or stored queries.
To prevent accidental deletion, either hide the navigation pane ...or use SQL.

In the end, its your choice.

 

[MajP]

Personally, I think it is both. Some things are just done easier and cleaner via code, others by querydefs. I personally do not do any control references in a query. I just find that hard to write and debug. Far easier for me to do it in code. But I am not going to write a bunch of code for a simple select query.

 

[The_Doc_Man]

Sirs, I am still not able to get the message
Whether to go with existing queries or go for VBA with SQLA code

For reasons I stated earlier, going entirely one way or the other EXCLUSIVELY is wrong, as there are times when QueryDefs are right and times when dynamically created SQL strings are right. It should not be an EITHER/OR situation, but rather an AND/OR situation.

 

[nirmal]

To summarise, its largely personal preference
Speed will be similar whether you use SQL or stored queries.
To prevent accidental deletio

Sir, actually if I learn to use sql view code in a vba code, then I am likely to better understand the vba coding.
It is bit easy to design a query in access. So I intend to use the same in vba code.
Which I want to learn, so please guide