vba - ADODB 连接线上未设置“91”对象变量/With block

Question

我正在尝试查询数据库来填充列表框(以及其他各种任务)。运行代码时出现以下错误:

错误:

91 
Object variable or With block variable not set

我的代码:

Private Sub UserForm_Initialize()
    On Error GoTo UserForm_Initialize_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim StrDBPath As String
    Dim strSQL As String


strSQL = "SELECT [Heads A].[Date Entered], [Heads A Issues].Department, [Heads A Issues].Equipment, [Heads A Issues].[Operation Issues], Sum([Heads A Issues].Downtime) AS SumOfDowntime1, IIf([Head A Crew]='3','C-Crew',IIf([Head A Crew]='2','B-Crew','A-Crew')) AS Crew " & _
        "FROM [Heads A] INNER JOIN [Heads A Issues] ON [Heads A].[HeadLineA ID] = [Heads A Issues].[HeadLineA ID]" & _
        " GROUP BY [Heads A].[Date Entered], [Heads A Issues].Department, [Heads A Issues].Equipment, [Heads A Issues].[Operation Issues], IIf([Head A Crew]='3','C-Crew',IIf([Head A Crew]='2','B-Crew','A-Crew'))" & _
        " HAVING ((([Heads A].[Date Entered])>=#" & Sheets("Choices").Cells(2, 1).Value & "# And ([Heads A].[Date Entered])<=#" & Sheets("Choices").Cells(2, 2).Value & "#) And (([Heads A Issues].Department)='" & Sheets("Choices").Cells(2, 3).Value & "') And ((IIf([Head A Crew]='3','C-Crew',IIf([Head A Crew]='2','B-Crew','A-Crew'))) Like IIf('" & Sheets("Choices").Cells(2, 4).Value & "'='all','*-Crew','" & _
        Sheets("Choices").Cells(2, 4).Value & "'))) ORDER BY [Heads A Issues].Department, [Heads A Issues].Equipment;"

StrDBPath = Application.ActiveWorkbook.Path & "\Daily Closing Report V997.accdb"

cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _ 'THIS IS WHERE THE ISSUE IS
                         "Data Source=" & StrDBPath & ";" &_
                         "Jet OLEDB:Engine Type=5;" & _
                         "Persist Security Info=False;"

rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic

CopyText (strSQL)

If (rst.RecordCount = 0) Then
    MsgBox "0 records found."
    Unload UserForm1
    Exit Sub
End If

If rst.RecordCount > 0 Then
    rst.MoveFirst
End If
With Me.ListBox1
    .Clear
    Do While rst.EOF = False
        .AddItem rst![Department,Heads A]
        rst.MoveNext
    Loop
    End With
UserForm_Initialize_Exit:
     On Error Resume Next
     rst.Close
     cnn.Close
     Set rst = Nothing
     Set cnn = Nothing
     Exit Sub
UserForm_Initialize_Err:
     MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
     Resume UserForm_Initialize_Exit
End Sub

我已经缩小了错误出现在我打开连接的行(上面标记的)上的范围，但我不确定为什么它会按原样运行。 TIA!

Answer 1

尽早将其绑定(bind)为新的。

Dim cnn As new ADODB.Connection

您可以按照 Don Jewett 所说的那样，分两行进行:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

这是后期绑定(bind)，在您的情况下只是额外的一行代码。如果您的代码由于逻辑原因可能永远无法建立连接，那么最好在连接打开附近进行延迟绑定(bind)，以便在需要时才进行绑定(bind)。但由于您正在立即使用连接和记录集，因此您最好尽早绑定(bind)它。