VBA using independent SQL statements instead of nested queries

[GangnamStyle]

Hi,

so far I preferred to use separate (non VBA) SQL queries instead of huge nested queries. (except for Joins, or simpler WHERE IN statements)

Not only do I think nested queries with more than two tables are hard to understand (after a while) I also had problems when I use them correspondingly with UPDATE statements.


However, now I have to "hide" the queries in VBA which requires me to use the OpenRecordset statement (OpenRecordset("SELECT * FROM atable")).

Is it possible to use a defined SELECT query again (in VBA code) in an other query similar to what one can do in MS Access by simply saving quer1 and using this as input for query2?


Thanks a lot

 

[Uncle Gizmo]

I'm not getting a clear picture of your question. I get the impression that you are creating record sets and I can see no reason why you can't use a record set more than once? So I'm not clear about your question?

 

[GangnamStyle]

Hi Uncle Gizmo,

thanks for your reply - ok let me draw an example.

In MS Access (outside of the VBA world) - would create and save down two queries:


Query1: SELECT A,B FROM Table1 WHERE A=1 (save this query as Object Query1)
Query1: SELECT Query1.A FROM Query1 WHERE Query1.B=2

(ok, I wouldnt really separate these into two queries, but its for illustration)

You can see that Query1 is a saved object and can be used as input into the second SQL.

Now in VBA I would create a recordset:

Set vba_query1 = db.OpenRecordset("SELECT A,B FROM Table1 WHERE A=1")

Then I could use ADO to loop trough vba_query1 - but I think ADO is slower than SQL and I would prefer to stick to SQL as I could so far solve all problems with SQL.

Is there a way to use vba_query1 as an input into a second statement similar to the following:

Set vba_query2 = db.OpenRecordset("SELECT vba_query1.A FROM vba_query1 WHERE vba_query1.B=2")?


(please note that I am aware that it is useless to separate these 2 queries - but it is for illustration.

 

[Galaxiom]

You can use VBA to change the SQL property of a stored query then refer to it from another VBA query. That way you just have one stored query which is reused. You can even hide that query if you want.

However I would encourage you to lean how to format SQL so that subqueries are more manageable.

 

[GangnamStyle]

Hi thanks for you reply?

Do you mean like that:

Set vba_query = db.OpenRecordset("SELECT " & col_var & ".A FROM " & table_var & " WHERE " & table_var & ".B=2")

?

This would actually work for the simple example i have drawn to illustrate the issue, but would not be applicable for the problem itself as the reason why I ask this is that I do want to "un-nest" complex queries, that have a completely different structure.

 

[Galaxiom]

Hi thanks for you reply?

Do you mean like that:

Set vba_query = db.OpenRecordset("SELECT " & col_var & ".A FROM " & table_var & " WHERE " & table_var & ".B=2")

No. Have a stored query for each nested level you anticipate requiring.

When you need to use it, change its SQL property like this:

Currentdb.QueryDefs("qryname").SQL = "SELECT ......."

Then refer to that query from the next level query:

SELECT whatever FROM qryname INNER JOIN sometable ........."

However I would still encourage you to learn how to format SQL so that the nesting is readable. This is easily done in VBA.

 

[GangnamStyle]

Okay Thanks a lot.


At the moment I am not 100% sure what you mean, but I realized I should read a bit more on the functioning of Currentdb.QueryDefs.

Thanks and have a great day.

 

[Cronk]

Firstly,

Set vba_query = db.OpenRecordset("SELECT " & col_var & ".A FROM " & table_var & " WHERE " & table_var & ".B=2")

is opening a recordset.

What is being referred to is that you have a query that you have created in design mode, say, qrySomething.

This query is saved with the sql being something say like
"SELECT * FROM tblOne"

In vba, you can set a reference to that query and change its sql

 set db = currentdb
 set qdf = db.querydefs("qrySomething")
 qdf.sql ="SELECT * FROM tblTwo"
 set qdf = nothing

You can then open a recordset based on the modified query by
set rst = db.openrecordset("select * from qrySomething")

The data is now based on tblTwo, not tblOne

 

[GangnamStyle]

Thanks a lot for the further explanation.

I think I understand the queries now much better.

 

[The_Doc_Man]

but I think ADO is slower than SQL

I am sensing some confusion. This statement is similar to asking "Is it as hot in the summer as it is in the city?" (Mixed nature of references)

SQL by itself does nothing. It is just text in need of a place to work. You need an ADO or DAO recordset to open the SQL in order to be able to pick out the individual records. If you use DoCmd.RunSQL then it is doing so in the context of the database implied by CurrentDB - which is either DAO or ADO. You CAN, however, choose a preference. You can do

Dim dbX as DAO.Database

SET dbX = CurrentDB

(and, of course, the ADO equivalent is also possible.)

I believe that the current default for databases is ADO, but that changed from DAO some time ago and I don't know if the default has changed again. I rarely use the DAO flavor. When you open a SELECT query through a recordset, it is the type of recordset that selects DAO vs. ADO. In the absence of explicit resolution, the type that is opened matches the type of the database environment being opened.

The order in which you have defined the DAO and ADO libraries in your reference list is also significant because "Recordset" appears in both libraries and the first match always wins.

As to which one is faster? Depends on where they are running. The one that runs fastest is usually the one that is on the dedicated server when we are talking large databases or FE/BE split cases. ADO vs. DAO is not significantly different in speed for either one. What's that old Real Estate phrase? Location, location, location! THAT is how you know which one is faster.

For a non-SELECT query (i.e. an action query) you can use a DAO database variable like I showed above to do a

dbX.Execute querystring, dbFailOnError

to execute the action query and that will be pretty fast. Read up on that topic if it applies to you. That is very efficient but of course is a "whole recordset at one time" action and you can't do this for ADO database types.

Reading back, I see I might have rambled on a bit, but then again, I write like I talk - free association mixed with a little dissociation. If you find any value in the comments, well and good. If not, have a nice day.

 

[GangnamStyle]

Hey thanks a lot The_Doc_Man, I seem to obviously lack some more conceptual background - which doesnt surprise me as my vba knowledge is more or less a collection of bits and pieces found by google or in forums - tied together until it gave me a result

Im always happy to get some critics, as this is the only way to learn