SharePoint上Excel用ACE.OLEDB执行SQL报只读错误求助

问题分析 #

本地运行ACE.OLEDB查询正常，但SharePoint打开后报错-2147467259，核心原因是：

• SharePoint中打开的Excel文件为本地缓存副本，ACE.OLEDB默认尝试以读写模式连接，而缓存文件实际处于只读状态
• 即使是Select查询，adLockOptimistic的锁定类型会触发ACE对文件写权限的检查，导致报错

解决方案 #

1. 修改Recordset锁定类型为只读 #

将OpenRecordset函数中的adLockOptimistic替换为adLockReadOnly，避免触发写权限检查：

Public Function OpenRecordset(sql As String) As Object
    Const adOpenStatic = 3
    Const adLockReadOnly = 1 ' 替换原来的adLockOptimistic
    Const adCmdText = &H1
    
    Set OpenRecordset = CreateObject("ADODB.Recordset")
    OpenRecordset.Open sql, GetConnection(), adOpenStatic, adLockReadOnly, adCmdText
End Function

2. 强制连接以只读模式打开 #

在连接字符串中添加Mode=Read，明确告知ACE以只读方式访问文件，同时保留IMEX=1确保混合数据类型读取正常：

Private Function GetConnection() As Object
    Dim m_Connection As Object 
    
    If m_Connection Is Nothing Then
        Set m_Connection = CreateObject("ADODB.Connection")
        m_Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & ThisWorkbook.FullName & ";Mode=Read;" & _
            "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;"";"
    End If
    Set GetConnection = m_Connection
 End Function

3. 确保使用Excel客户端打开文件 #

ACE.OLEDB仅支持本地Excel客户端，不要在浏览器的Excel Online中运行代码，必须下载文件后在桌面Excel中打开执行。

4. 优化连接生命周期（可选） #

避免使用全局持久连接，改用临时连接并在查询完成后关闭，防止缓存连接状态异常：

Public Function OpenRecordset(sql As String) As Object
    Const adOpenStatic = 3
    Const adLockReadOnly = 1
    Const adCmdText = &H1
    Dim conn As Object
    Dim rst As Object
    
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.FullName & ";Mode=Read;" & _
        "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;"";"
    
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open sql, conn, adOpenStatic, adLockReadOnly, adCmdText
    
    ' 将连接绑定到Recordset，后续关闭Recordset时需同时关闭连接
    Set rst.ActiveConnection = conn
    Set OpenRecordset = rst
End Function

' 使用示例
Sub UseRecordset()
    Dim rst As Object
    Set rst = OpenRecordset("SELECT Distinct Offres FROM ['" & sMySheet & "$'] WHERE Domaine IS NOT NULL AND Domaine = '" & sTempDomaine & "';")
    
    ' 处理数据逻辑...
    
    ' 清理资源
    rst.Close
    rst.ActiveConnection.Close
    Set rst = Nothing
End Sub

关键说明 #

• 即使是Select查询，ACE.OLEDB默认的连接和锁定模式会尝试获取文件写权限，SharePoint缓存环境下无法满足该要求，因此必须强制只读配置
• IMEX=1用于处理单元格混合数据类型的读取问题，结合Mode=Read可避免权限冲突

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