Excel宏SQL查询结果异常且自动打开副本，求无需每日重启的解决办法

1. 解决旧数据缓存问题 #

• 更新Pending后立即保存工作簿：ACE OLEDB读取的是磁盘上的Excel文件，而非内存中的版本，如果更新Pending工作表后未保存，查询会读取磁盘上的旧数据。在执行SQL查询前添加保存代码：

  WBMain.Save ' 确保内存中的更新写入磁盘
  

• 修改连接字符串，强制读取最新数据：在连接字符串中添加缓存禁用和文件访问模式参数，避免锁定和缓存残留：

  dBConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & WBMain.FullName & _
           ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;"";" & _
           "Mode=ReadWrite;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2"
  

  • Mode=ReadWrite：确保工作簿可读写，避免只读缓存
  • Jet OLEDB:Database Locking Mode=1：启用共享锁定，减少文件锁定导致的缓存
  • Jet OLEDB:Global Partial Bulk Ops=2：禁用批量操作缓存，强制实时读取数据

2. 优化SQL查询，降低耗时 #

原SQL使用嵌套子查询，会导致重复扫描工作表，可简化为直接左连接筛选，逻辑完全一致但执行效率更高：

vsql = "SELECT P.* FROM [Pending$] P " & _
       "LEFT JOIN [History$] H ON P.[REPAIR_ID] = H.[REPAIR_ID] " & _
       "WHERE H.[REPAIR_ID] IS NULL AND P.[REPAIR_ID] IS NOT NULL"

3. 彻底释放数据库资源，解决自动打开副本问题 #

代码仅关闭了记录集dbRS，未关闭连接dBcn也未释放对象，导致工作簿被持续锁定，关闭时会生成副本。添加完整的资源释放逻辑，建议配合错误处理确保资源释放：

On Error GoTo Cleanup

' --- 核心代码开始 ---
Set WBMain = ThisWorkbook ' 用ThisWorkbook代替ActiveWorkbook，避免切换工作簿出错
WBMain.Save ' 更新Pending后立即保存

dBConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & WBMain.FullName & _
         ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;"";" & _
         "Mode=ReadWrite;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2"

dBcn.Open dBConn

vsql = "SELECT P.* FROM [Pending$] P " & _
       "LEFT JOIN [History$] H ON P.[REPAIR_ID] = H.[REPAIR_ID] " & _
       "WHERE H.[REPAIR_ID] IS NULL AND P.[REPAIR_ID] IS NOT NULL"

dbRS.Open vsql, dBcn

' 写入表头和数据
Dim i As Long
For i = 0 To dbRS.Fields.Count - 1
    DoEvents
    Cells(1, i + 1).Value2 = dbRS.Fields(i).Name
Next i
Range("A2").CopyFromRecordset dbRS
' --- 核心代码结束 ---

Cleanup:
    ' 释放记录集
    If Not dbRS Is Nothing Then
        If dbRS.State = adStateOpen Then dbRS.Close
        Set dbRS = Nothing
    End If
    ' 释放连接
    If Not dBcn Is Nothing Then
        If dBcn.State = adStateOpen Then dBcn.Close
        Set dBcn = Nothing
    End If
    On Error Resume Next

4. 额外优化：避免使用ActiveWorkbook #

ActiveWorkbook可能因用户操作切换导致错误，建议用ThisWorkbook（宏所在的工作簿）明确指定操作对象，避免意外。

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