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 to a pass-through 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.
Pass-through Function
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 SubSales Report Code for Pass-through Queries
Private Sub cmdSalesbyDate_Click()
On Error GoTo Err_cmdPreview_Click
If Me!tmpDateto < tmpDatefrom Then
MsgBox ("To date cannot be less than From Date")
Me.tmpDatefrom.SetFocus
Exit Sub
End If
Dim tmpSQL As String
tmpSQL = tmpSQL & "SELECT CustCode,ProdCode, Company_Name, Product_Description, sum(Sales_Qty) as SalesQty,SUM(Prod_Cost * Sales_Qty) AS CostPrice, "
tmpSQL = tmpSQL & " SUM(dbo.Transactions.Line_Value) AS Line_Value, SUM(dbo.tblTransactions.Units) AS NoofUnits, dbo.tblCustomers.Customer_Type "
tmpSQL = tmpSQL & " FROM tblCustomers INNER JOIN tblTransactions ON dbo.tblCustomers.ID = dbo.tblTransactions.CustCode LEFT OUTER JOIN"
tmpSQL = tmpSQL & " tblProdDescr ON tblTransactions.ProdCode = tblProd.SalesProdCode"
tmpSQL = tmpSQL & " WHERE (dbo.tblTransactions.Hdate >='" & ActDate(Me.tmpDatefrom) & "') AND (dbo.tblTransactions.Hdate <= '" & ActDate(Me.tmpDateto) & "') "
If Me.cboCust <> "*" Then
tmpSQL = tmpSQL & " and CustCode='" & Me.cboCust & "'"
End If
If Me.cboProduct <> "*" Then
tmpSQL = tmpSQL & " and ProdCode='" & Me.cboProduct & "'"
End If
tmpSQL = tmpSQL & " GROUP BY CustCode, Company_Name, ProdCode, Product_Description, Customer_Type"
Dim stDocName As String
stDocName = "rptProdSales"
Call PassThrough(tmpSQL, "qry_" & stDocName)
DoCmd.OpenReport stDocName, acPreview
Exit_cmdPreview_Click:
Exit Sub
Err_cmdPreview_Click:
If Err.Number = 2501 Then Resume Next
MsgBox Err.Description
Resume Exit_cmdPreview_Click
End SubIn the above code I use the report name and prefix the “qry_” to make the pass-through query. The reports recordset is set to this pass-through query.
Because the summarisation and totalling are done at the server only the smaller recordset is returned to the report.
Pass-through queries in Microsoft Access are powerful tools that directly send SQL commands to an external database server (e.g., SQL Server, MySQL, or Oracle) instead of being processed by the Access database engine. Here are the main benefits of using pass-through queries:
Benefits of Pass-Through Queries
1. Improved Performance
- Server-Side Execution: Pass-through queries execute directly on the external database server, leveraging its processing power. This reduces the load on the Access database and the local machine.
- Efficient Data Retrieval: Only the required data is sent back to Access, minimizing network traffic and improving speed, especially when working with large datasets.
2. Access to Full SQL Functionality
- Advanced SQL Features: Pass-through queries allow you to use SQL syntax and functions specific to the external database system, which may not be supported by Access SQL.
- Complex Queries: You can write more complex queries, such as those involving advanced joins, Common Table Expressions (CTEs), window functions, or stored procedures.
3. Reduced Resource Consumption
- Optimized Processing: By processing queries on the server, Access avoids the overhead of handling large datasets locally. This is especially beneficial for heavy computations or large-scale operations.
- Selective Data Transfer: Only the result set is sent to Access, saving bandwidth and storage resources.
4. Enhanced Scalability
- Handling Large Databases: Pass-through queries are well-suited for working with large databases hosted on robust database servers, enabling Access to handle more significant workloads effectively.
- Multi-User Environments: By offloading processing to the server, they support better performance in environments with multiple concurrent users.
5. Direct Interaction with the Database
- Real-Time Data Access: Pass-through queries interact directly with the server’s current data, ensuring real-time accuracy.
- Bypass Access Limitations: They bypass Access’s query limitations, like maximum record restrictions or limited SQL syntax.
6. Support for Server-Side Security and Features
- Database Roles and Permissions: Pass-through queries respect the server-side user permissions, allowing for robust security management.
- Trigger Execution: Queries can trigger database-side triggers, ensuring consistency and enforcing business rules at the server level.
7. Simplified Maintenance
- Centralized Query Management: By executing SQL directly on the server, you can manage complex logic or business rules in one place (e.g., stored procedures or server views), rather than duplicating logic across multiple Access queries.
- Easier Updates: Changes to the database schema or logic can often be handled directly in the server-side objects (like views or stored procedures), reducing the need to update individual Access queries.
- Cleaner Access Database Design: Offloading complex queries to the server simplifies your Access database structure, making it easier to maintain, troubleshoot, and document.
- Version Control: Centralized server-side query management facilitates the use of version control tools, improving collaboration and change tracking for teams working on the database.