分配对象时出现 VBA 错误 91 的原因可能是什么 [英] What could be the reason for VBA error 91 when assigning object

问题描述

我在 Excel 中有一个宏，可以从 Access 数据库中查询数据.它对我很好.我与几个同事共享了该文件，其中两个一直收到错误91 对象变量或未设置块变量".

I have a Macro in Excel that queries data from an Access database. It is working fine with me. I shared the file with a few colleagues and two of them keep getting the error "91 Object variable or With block variable not set".

调试表明这一行是罪魁祸首.

Debugging indicates that this line is the culprit.

Set rs = objAccess.CurrentProject.Connection.Execute(SQL)

感谢您可以分享的任何见解.相关代码如下.

Appreciate any insights you can share. Relevant code below.

Sub RefreshData()
On Error GoTo SubError
    Const DbLoc As String = "path to .accdb"
    Dim objAccess As Object 
    Dim rs As Object 
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet
    Dim recCount As Long
    Dim SQL As String
    Const cstrPwd As String = "foo"
 
    'Setup references to workbook and sheet
    Set xlBook = ActiveWorkbook
    
    If xlBook Is Nothing Then
        MsgBox "xlBook not found"
    End If
    
    Set xlSheet = xlBook.Worksheets(2)
    
    If xlSheet Is Nothing Then
        MsgBox "xlSheet not found"
    End If
    
    xlSheet.Range("A5:BA99000").ClearContents
   
    'Communicate with the user
    Application.StatusBar = "Connecting to an external database..."
    Application.Cursor = xlWait
 
    ' connect to the Access database
    On Error Resume Next
    Set objAccess = GetObject(, "Access.Application")
    If Err.Number <> 0 Then
        Set objAccess = CreateObject("Access.Application")
    End If
    On Error GoTo SubError
    objAccess.Visible = False
    objAccess.OpenCurrentDatabase DbLoc, , cstrPwd

    SQL = "SELECT * FROM [name of predefined select query in Access]"
    
    'Execute our query and populate the recordset
    Set rs = objAccess.CurrentProject.Connection.Execute(SQL) ' The culprit :)
 
    If rs Is Nothing Then
        MsgBox "rs not found. SQL=" & SQL
    End If
 
    'Copy recordset to spreadsheet
    Application.StatusBar = "Writing to spreadsheet..."
    If rs.RecordCount = 0 Then
        MsgBox "No data retrieved from database", vbInformation + vbOKOnly, "No Data"
        GoTo SubExit
    Else
        rs.MoveLast
        recCount = rs.RecordCount
        rs.MoveFirst
    End If
   
    xlSheet.Range("A5").CopyFromRecordset rs
    Application.StatusBar = "Update complete"

 
SubExit:
On Error Resume Next
    Application.Cursor = xlDefault
    rs.Close
    Set rs = Nothing
    objAccess.Quit
    Set objAccess = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Exit Sub
 
SubError:
    Application.StatusBar = ""
    MsgBox "RefreshData - UpdateData VBA error: " & vbCrLf & Err.Number & " = " & Err.Description
    Resume SubExit
   
End Sub

注意:我按照这个答案中的建议使用对象，因为它是使用我加密的 .accdb 的唯一方法.

Note: I am using object as advised in this answer because it is the only way that worked with my encrypted .accdb.

推荐答案

我会再次检查连接.

此外，对于测试，请打开一个简单的测试查询以排除您的查询问题:

Also, for a test, open a simple test query to rule out issues with your query:

    SQL = "SELECT Id FROM MSysObjects"
    ' Execute our query and populate the recordset
    MsgBox objAccess.CurrentProject.Connection
    Set rs = objAccess.CurrentProject.Connection.Execute(SQL)
    MsgBox rs!Id

这篇关于分配对象时出现 VBA 错误 91 的原因可能是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！