HELP VBA

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

 

[Albert D. Kallal]

how

would I execute a query in VBA and store the results in
memory to use in a later query also written in VBA?

You can't store the results in memory for *LATER* use for another query.

So, you can most certainly execute a query, and have the results sent to
memory (this is called a reocrdset). That recordset can be searched, or
processed in code. So, this data in memory can be searched, processed, and
examined. However, you CAN NOT execute sql against this memory object.

So, if you MUST execute a query on a query, then the solution is to simply
CREATE the queries in code...and then execute them....

Since you are just executing a series of quires..then you don't need the
memory "recordset" object. (however, as a matter of coding and writing
applications...you should become familiar with reocrdsets, and how to
process/execute sql in-line in your code. For MOST stuff, I do use a memory
based reocrdset. However, in your case BECAUSE you are executing a query on
a query..then the reocrdset is of NO use. (however, the final results could
be sent to a memory object in place of a temp table..but this again is only
of use if you just need to process the data via some code).

SqlStringA = Transform Query code
SqlStringB = Select Query Code
SqlStringC = Joins SqlStringA and SqlStringB
SqlStringD = Appends SqlStringC to a table

So, what you do is write code to create the quryes..and then execute them...

Dim strMyWhere As String
Dim strMySql As String
Dim qryTemp1 As dao.QueryDef
Dim myDB As dao.Database

On Error Resume Next
Set myDB = CurrentDb

myDB.QueryDefs.Delete ("QueryA")
Set qryTemp1 = myDB.CreateQueryDef("QueryA", SqlStringA)

myDB.QueryDefs.Delete ("QueryB")
Set qryTemp1 = myDB.CreateQueryDef("QueryB", SqlStringB)

......... just create all your queres you need in code...

myDB.QueryDefs.Refresh

Then, execute the last query

Currentdb.Execute "QueryD",dbFailOnError

 

[rich]

Hi,

not sure what SqlStringA does, but could you not UNION SqlStringB & C
and then make it into a make table query?

Rich

 

[Dirk Goldgar]

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?

I posted my own answer to this question where you also, independently,
posted it in the .modulesdaovba newsgroup. Please don't multipost;
it's neither helpful to you nor polite to others.

 

[Jimbo]

I like this solution a lot, I appreciate you taking time
to answer and giving sample code.