Need help from you Code Masters

[Jimbo]

I have a crosstab query and I use an append query to take
those values into a table. Now I want to put all this into
some vba code. I was thinking i might have to use a ADODB
recordset to store the values from the Crosstab query into
a temp table in memory, then reference the table in
memeory with my append query something like:

SqlStringA = My Transform Code
Set rsSQLResultA = New ADODB.Recordset
rsSQLResultA.Open SqlStringA, CurrentProject.Connection,
adOpenStatic

SqlStringB = "INSERT INTO table * SELECT * FROM
rsSQLResultA

Will someting like this work if so how do I get it to
work, or you got a better way, thank in advance.

 

[Alex Dybenko]

i think you can just do everything in one query:

SqlStringB = "INSERT INTO table * " & My Transform Code

then execute this SQL
CurrentProject.Connection.execute SqlStringB

In case your "My Transform Code" returns some select query

HTH

 

[Guest]

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?

 

[Alex Dybenko]

i think case i think you can save each query and the join them
curentDB.querydefs("qrySqlStringA").SQL= SqlStringA
curentDB.querydefs("qrySqlStringB").SQL= SqlStringB
curentDB.querydefs("qrySqlStringC").SQL= "... join ..."

currentdb.execute "...Appends SqlStringC to a table..."

HTH

 

[Chris Nebinger]

Or, you can nest the queries..


dim strSQL as String

strSQL = "Insert Into TableName(Field1,Field2,Field3) "
strsql = strSQL & " From (Select queryA.*, QueryB.* from
(Select statement for query A) AS Query A, "
strsql = strSQL & " INNER JOIN (Select statement for Query
B) As query B) As QueryC "

Try doing this in the SQL view of a query. You'll see how
it works and then cut/paste the SQL code into VBA code.


Chris Nebinger