Query free database

[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!

 

[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.

 

[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

 

[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

 

[MajP]

Which I want to learn, so please guide

If you got a specific question please post, but asking us to randomly guide you means nothing. There are lots of resources if you need to learn SQL or VBA.

If you are trying to learn how to write SQL then writing it in vba is not necessary. Instead of using the designer simply go to SQL view and type it in. I recommend you read every example here. If you go through this, you will be away ahead of most people.
SQL Tutorial (w3schools.com)

 

[isladogs]

I agree with @MajP and was going to suggest exactly the same site for learning SQL.
In the query designer, change to SQL view to see how the SQL is constructed though Access often adds lots of largely unnecessary brackets.
You may also find my sql to vba converter useful whilst you are learning ... SQL to VBA & Back Again - Mendip Data Systems.
This is designed to assist with creating SQL for use in VBA procedures
It can also help you convert back to SQL for use in a saved query

You might also find these two articles from my website useful: Query Join Types and Optimising Queries

Also be aware that you cannot use dB.execute or DoCmd.RunSQL on SELECT queries.
Use a query def in code or a saved query for those

If you get stuck on particular points, ask in another thread.

However do bear in mind the cautionary points made by others such as @MajP and @The_Doc_Man

 

[isladogs]

Pat
I'm not sure if that first line was aimed at me or the OP.
If it was aimed at me, then I think you have misunderstood the purpose of the converter.
I've added a couple of sentences for clarification but it might help more to look at the link provided