Using Passthrough query in MASC

Sep 12

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
Set qdfPassThrough = dbsCurrent.CreateQueryDef(tmpQry)
qdfPassThrough.Connect = "ODBC;DSN=MASCSql;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 
Exit Sub
MsgBox Err.Number & ": " & Err.Description
Resume Exit_ErrHandler
End Sub

Leave a Reply

You must be logged in to post a comment.

If you need some help on a project drop leave a comment on the post and I will reply.