Filter Reports Still Slow

[CevinMoses]

Big question: Does filtering a report through a form speed up the process?

I have a report grouped on who the associate is so that each associate gets
the same report with just their information. The data is from an Access Join
query of 2 pass-through queries to an Oracle server, one of which is a Union
of 2 tables. Got all that?

When I filter the report through a form for only one associate, it seems
like I am still waiting for the queries to process the information for ALL of
the associates, then the filter causes the report to only give me that
information. Is this what's happening?

If so I will probably need help filtering the pass-through queries prior to
them launching. I know that there's a more efficient manner of doing this,
but I have been unable to accomplish the Join as part of my pass-through
queries, thus a lot of data crunching is being done on the client end.

 

[Duane Hookom]

Try writing some code to change the SQL property of the pass-through query. I
use a ChangeSQL() function in most of my apps that accepts arguments of a
query name and a sql statement. This requires setting a reference to the DAO
library.

Function ChangeSQL(pstrQueryName As String, pstrSQL As String) As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQueryName)
'return the current SQL from this function
ChangeSQL = qd.SQL
'update the SQL to the new SQL (pstrSQL)
qd.SQL = pstrSQL
'clean up
Set qd = Nothing
Set db = Nothing
End Function

 

[krissco]

Good question. AFAIK you can not place parameters within pass-through
queries. What's happening is that your pass-through queries are
executed in full, and the Access query filters the records returned.
This results in 0 savings (as you have seen).

As far as speeding up the pass-through queries, there are a couple
options. Duane's is a good one. You may also decide to place the
Oracle data in a temporary table and query from that table - it all
depends on your App. Do you run the same report for every associate as
a batch, or do you normally just run the report for a single
associate?

If batched, insert the Oracle data into a temp table then run the
report for each individual associate from the temp table (instead of
the pass-through queries).

If not batched, and you don't want to use Duane's method, consider
using an ADO connection to Oracle and executing a (modified) "pass-
through" query through VBA. You can retrieve the SQL from a function
like Duane's and modify it, then execute and save the recordset to a
local table. Some example code:

'Depending on what clauses are present in your pass-through queries,
you may need to modify this code.
Function ExecuteFilteredPassThrough(strQueryName as string,
strCriteria as string, strLocalTempTable as string)

Dim strSql as string
dim con as new adodb.connection, conLocal as adodb.connection
Dim rst as new adodb.recordset

set conLocal = currentproject.connection
con.connectionstring = "whatever you need to connect to you database"
con.open

strSql = CurrentDb().QueryDefs(strQueryName).SQL


'Watch out for more clauses than "SELECT, FROM" - this code will fail
on GROUP BY and ORDER BY
if instr(strSql, "WHERE", 1) = 0 then
strSql = strSql & " where " & strcriteria
else
replace(strSql, "WHERE", "WHERE " & strcriteria & " AND "
end if

rst.open strsql, con, adopenforwardonly, adlockreadonly

strSql = "Insert into " & strLocalTempTable & " Values "

'You can do better than this function - just to give you an idea.
'Watch out for data types. . .
while not rst.eof
conLocal.execute strsql & "('" & rst(0) & "')"
rst.movenext
wend

'Close connections and cleanup objects

end function