如何在Access报表Detail节On Format事件调用存储过程并避免重复连接

首先，你遇到的核心问题是Detail节的On Format事件会针对报表的每一行触发一次，而你的TestSPB过程每次都会新建一个ADODB连接，这不仅会造成大量不必要的资源开销，还可能拖慢报表的渲染速度。我们可以从两个方向优化：要么复用连接，要么换个更合适的事件一次性执行存储过程。

一、先修正原代码的小问题 #

你的TestSPB里有个明显的错误：执行.Execute之后又调用rst.Open cmd，这会重复执行存储过程，而且大概率会报错。如果你的存储过程不需要返回记录集（从代码看你只关心返回值和输出参数），完全可以去掉记录集相关的代码，直接通过Command对象获取参数值。

二、最优方案：报表级一次性执行存储过程 #

从你的参数来看（from1、to1、IDHOTELS这些都是报表级的输入，不是每行的动态值），根本不需要在Detail节每行都调用存储过程。我们可以在报表打开时执行一次，把结果存在模块变量里，Detail节直接用变量赋值即可。

修改后的代码步骤： #

1. 在报表的模块顶部声明模块级变量（这些变量在报表整个生命周期内有效）：

Option Compare Database
Option Explicit

Private conn As ADODB.Connection
Private tothabs As Double
Private totsum As Double

2. 在报表的Open事件中初始化连接并执行存储过程：

Private Sub Report_Open(Cancel As Integer)
    ' 建立一次连接，整个报表复用
    Set conn = New ADODB.Connection
    conn.Open "Provider=SQLOLEDB.1;Password=xxxxxxxxx; Persist Security Info=True; User ID=xxxxxxx;Initial Catalog=xxxxxxxx;Data Source=20.20.20.20"
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    With cmd
        .ActiveConnection = conn
        .CommandText = "TESTSP10"
        .CommandType = adCmdStoredProc
        
        ' 添加参数
        .Parameters.Append .CreateParameter("Checkin", adDate, adParamInput, , Me.from1.Value)
        .Parameters.Append .CreateParameter("Checkout", adDate, adParamInput, , Me.to1.Value)
        .Parameters.Append .CreateParameter("IDHotel", adInteger, adParamInput, , Me.IDHOTELS.Value)
        .Parameters.Append .CreateParameter("canthabdbl", adInteger, adParamInput, , Me.cadbl.Value)
        .Parameters.Append .CreateParameter("cantchd", adInteger, adParamInput, , 0)
        .Parameters.Append .CreateParameter("canthab", adInteger, adParamInput, , 1)
        .Parameters.Append .CreateParameter("totalhab", adInteger, adParamReturnValue)
        .Parameters.Append .CreateParameter("Sum", adInteger, adParamOutput)
        
        ' 执行存储过程
        .Execute
        
        ' 获取返回值和输出参数
        tothabs = .Parameters("totalhab").Value
        totsum = .Parameters("Sum").Value
    End With
    
    Set cmd = Nothing
End Sub

3. 简化Detail节的Format事件，直接用模块变量赋值：

Private Sub Detalle_Format(Cancel As Integer, FormatCount As Integer)
    Me.total.Value = tothabs
    Me.sumade.Value = totsum
End Sub

4. 在报表的Close事件中关闭连接，释放资源：

Private Sub Report_Close()
    If Not conn Is Nothing Then
        If conn.State = adStateOpen Then conn.Close
        Set conn = Nothing
    End If
End Sub

三、如果必须在Detail节每行执行存储过程（参数是每行动态的） #

如果你的存储过程需要根据Detail节每行的字段值动态传参（比如每行的ID不同），那还是要在Format事件调用，但可以复用连接：

• 同样把conn声明为模块级变量，在报表Open事件初始化，Close事件关闭
• 修改TestSPB，直接使用模块级的conn，不要每次新建连接

示例修改后的TestSPB：

Public Sub TestSPB()
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    With cmd
        .ActiveConnection = conn ' 复用模块级的连接
        .CommandText = "TESTSP10"
        .CommandType = adCmdStoredProc
        
        ' 这里的参数如果是每行动态的，比如换成Me!SomeField.Value
        .Parameters.Append .CreateParameter("Checkin", adDate, adParamInput, , Me.from1.Value)
        ' ... 其他参数
        
        .Execute
        
        tothabs = .Parameters("totalhab").Value
        totsum = .Parameters("Sum").Value
    End With
    
    Set cmd = Nothing
End Sub

总结 #

• 如果存储过程的参数是整个报表通用的，优先在报表Open事件一次性执行，这是性能最好的方案
• 如果必须每行执行，一定要复用数据库连接，避免重复创建销毁连接的开销
• 注意清理资源，在报表关闭时关闭连接，避免内存泄漏

内容的提问来源于stack exchange，提问作者tonydeleon