Using a local table to create a Pass-Thru Query

[Guest]

How do you get the information out of a local table to incorperate it into a
pass-thru query. Here is my example (simplified)

One local table (A) with one value called DWE

A table on Oracle (B) with many fields one of which is DWE

the SQL needs to look like

select * from DB.B where DWE in ('','','','')

the ('','','','') should have the DWE values in the A table. I see that a
join would work so if that is a possiblity I would be willing to use that as
well. The only problem with the join that I have created is that it pulls
all of table B over the network and process the request locally which ends up
taking about 20 to 30 minutes. If don right it would take 5 to 10 seconds.

Thanks,

Cam

 

[John Vinson]

How do you get the information out of a local table to incorperate it into a
pass-thru query. Here is my example (simplified)

One local table (A) with one value called DWE

A table on Oracle (B) with many fields one of which is DWE

the SQL needs to look like

select * from DB.B where DWE in ('','','','')

the ('','','','') should have the DWE values in the A table. I see that a
join would work so if that is a possiblity I would be willing to use that as
well. The only problem with the join that I have created is that it pulls
all of table B over the network and process the request locally which ends up
taking about 20 to 30 minutes. If don right it would take 5 to 10 seconds.

Thanks,

Cam

You will probably need to build the SQL of the passthrough query in
VBA code. The code at

http://www.mvps.org/access/modules/mdl0004.htm

should give you a start...

John W. Vinson[MVP]

 

[Ron Hinds]

Use a QueryDef object for the passthrough and perform the join against it,
like so:

Set qdf = CurrentDb.CreateQueryDef("OracleQuery")
qdf.Connect = "oracle_connect_string"
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647 '2^31 - 1 = ~2GB
qdf.SQL = "SELECT * FROM DB.B"

strSQL = "SELECT * FROM OracleQuery WHERE DWE IN (SELECT DWE FROM A)"

'open recordset here etc.

Replace "oracle_connect_string" with the connection string used by e.g. a
linked table. I often set it like so:

qdf.Connect = CurrentDb.TableDefs("some_linked_table_name").Connect

This will improve the performance but I don't know about 5 - 10 seconds... I
don't think that is realistic in this scenario!

 

[Douglas J Steele]

But that's not a true passthrough, since it can't run entirely on the
server.

What Cam's looking for is something like:


Set rs = CurrentDb.OpenRecordset("SELECT DWE FROM A")
Do While rs.EOF = False
strValues = strValues & rs!DWE & ", "
rs.MoveNext
End If

If Len(strValues) > 0 Then
' Remove extraneous ", " from the end
strValues = Left(strValues, Len(strValues) - 2)

strSQL = "SELECT * FROM DB.B " & _
"WHERE DWE IN (" & strValues & ")"

Set qdf = CurrentDb.CreateQueryDef("OracleQuery")
qdf.Connect = "oracle_connect_string"
qdf.ReturnsRecords = True
qdf.MaxRecords = 2147483647 '2^31 - 1 = ~2GB
qdf.SQL = strSQL
End If

The above assumes that DWE is a numeric field. If it's text, you'll need to
concatenate quotes around it:

strValues = strValues & "'" & rs!DWE & "', "

 

[Ron Hinds]

That's correct, but I've used both methods and in my testing (which may be
specific only to my situation ;-) the other method performs better. However
I would suggest the OP do their own testing to see what works best in their
situation.