Append Passthrough Query Results to Local Table

[bwilk77]

Right now, I have several passthrough queries that I'm running off of a
Teradata server. Each of these queries can take up to 10 minutes to run a
piece. I have an append query tied to each passthrough query to add the
results to a single table that is stored in my Access database.

I am trying to run each of the queries based on a list of objects that is
stored in another table in my Access database (I have some code that
concatenates all these objects into one string that I plan to place inside an
"IN" statement in my SQL). I would like to run each of these passthrough
queries through VBA so I can pass this string to the passthrough queries.

My problem is I dont know how to connect to the Teradata server to run my
SQL (including my text string of objects) in VBA. Also, once this has run I
dont know how to append these results to a local table.

I know this is pretty vague, but I dont have any code built yet to share as
an example. Any help or examples would be great.

Thanks.

 

[Alex Dybenko]

Hi,
just make a new append (insert into) query on your pass-through query to
insert it result into local table

INSERT INTO MyLocalTable (Field1, Field2 )
SELECT Field1, Field2 FROM MyPassThroughQuery


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[bwilk77]

Thanks Alex, that will work once I have the results from the passthrough
query.

Is it possible to do this all in VBA? Can I combine a passthrough query and
an append query into the same sql text string? Do I need to separate these
and run the passthrough query first then the append qry? I'm really at a
loss for how to connect to my teradata server and run the passthrough query
within VBA.

I've attached the code I've got so far (its a combination of a bunch of
posts I've seen so far). I added comments to explain each section.


Sub PullData()

‘this is the code I am using to string together all of my object numbers
into one list
‘I plan to use this list within an “IN†statement in the passthrough query
listed below

Dim rec_ObjectNums As DAO.Recordset
Dim str_Objects As String * 32000
Set rec_ObjectNums = CurrentDb.OpenRecordset("select ObjectNumber from
tbl_object_numbers", dbOpenDynaset)
tmp_objects = ""
Do While Not rec_ObjectNums.EOF
tmp_objects = tmp_objects & rec_ObjectNums!ObjectNumber & ","
rec_ObjectNums.MoveNext
Loop
str_Objects = Left(Left(tmp_objects, Len(tmp_objects) - 1) & Space(32000),
32000)



‘what do I declare to make this work???

Dim wksp As DAO.Workspace
Dim dbs As DAO.Database
Dim strSQL As String
Dim strCon As String


‘this is the append query you mentioned before…

strSQL = "INSERT INTO tbl_All_Data ( ObjectNumber, Department, TransValue,
DateRun ) " & _
"SELECT PassThruQry.ObjectNumber, PassThruQry.Department,
PassThruQry.[Average(WeeklyTrans)] AS TransValue, Now() AS DateRun " & _


‘this is the passthrough query that I am running to get my values I want to
append…
‘(there is not actually spaces in between the two pieces of SQL, I just put
them there to separate the two query parts for this post)

"FROM ( “ & _
“SELECT a.ObjectNumber, Average (WeeklyValues) FROM ( " & _
"SELECT ST.ObjectNumber, ST.Date, Count(distinct ST.TransNums) AS
WeeklyTrans " & _
"FROM ST " & _
"WHERE ST.ObjectNumber IN ( “ & str_Objects & “ ) " & _
"Group BY ST.ObjectNumber, ST.Date ) as a “ & _
“GROUP BY 1â€


‘don’t know what should go here either…

strCon = "ODBC;DSN=Teradata;DATABASE=MyDataBaseName;"
Set wksp = DBEngine(0)
Set dbs = wksp.OpenDatabase("", False, False, strCon)
dbs.Execute strSQL, dbSQLPassThrough

End Sub


Thanks again for the help.