Using Passthrough query in Access

We have recently converted a database from Access to SQL and needed to increase the speed of the reports when working with a large recordset. We amended this code from the web which significantly increased the speed on 2 reports but reduced the speed on another – so its a case of testing to get the best results.

Public Sub PassThrough(strSQL As String, tmpQry)
On Error GoTo ErrHandler
 
Dim obj As QueryDef
Dim dbsCurrent As Database
Dim qdfPassThrough As QueryDef
 
Set dbsCurrent = CurrentDb()
 
'delete the existing query
For Each obj In dbsCurrent.QueryDefs
     If obj.Name = tmpQry Then dbsCurrent.QueryDefs.Delete tmpQry
Next
 
 
Set qdfPassThrough = dbsCurrent.CreateQueryDef(tmpQry)
qdfPassThrough.Connect = "ODBC;DSN=YOURDBNAME;DATABASE=mascdataSQL;UID=xxxx;PWD=yyyy" ' replace with your details
qdfPassThrough.SQL = strSQL ' this is generated from the form taking the parameters and changing the table names to add the dbo. 
qdfPassThrough.ReturnsRecords = True 
 
dbsCurrent.Close
 
Exit_ErrHandler:
Exit Sub
 
ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_ErrHandler
End Sub
,

Pat