More clarification from you Code Masters Please

  • Thread starter Thread starter Jimbo
  • Start date Start date
J

Jimbo

What if I have more than one query, say for example:
SqlStringA = Transform Query code
SqlStringB = Select Query Code
SqlStringC = Joins SqlStringA and SqlStringB
SqlStringD = Appends SqlStringC to a table

In this case I have four differnt querys, however if I
wanted to write this all in VBA how would I go about that.
I guess my ultimate question is if the case arises, how
would I execute a query in VBA and store the results in
memory to use in a later query also written in VBA?
 
Jimbo said:
What if I have more than one query, say for example:
SqlStringA = Transform Query code
SqlStringB = Select Query Code
SqlStringC = Joins SqlStringA and SqlStringB
SqlStringD = Appends SqlStringC to a table

In this case I have four differnt querys, however if I
wanted to write this all in VBA how would I go about that.
I guess my ultimate question is if the case arises, how
would I execute a query in VBA and store the results in
memory to use in a later query also written in VBA?
Click to expand...

I can think of two ways:

1. (Simple but clunky) Save the SQL statements in querydefs (stored
queries) that you create for the purpose, and let each logically
successive querydef refer as needed to the preceding ones. I don't much
like this approach, though.

2. (More elegant, where possible) Combine the SQL statements into one
big one using appropriate syntax and aliases to treat some SQL
statements as "derived tables". Here's a rough example.

INSERT INTO tblOutput
SELECT A.foo, A.bar, B.baz FROM
(SELECT foo, bar FROM tblA
WHERE blah = 'woof') AS A
INNER JOIN
(SELECT DISTINCT bar, baz FROM tblB
WHERE glock = 'spiel') AS B
ON A.bar = B.bar;

Okay, so I don't have a TRANSFORM statement in there. I don't use them
enough to be confident of the syntax.
 
Back
Top