EOF/BOF错误-Excel VBA [英] EOF/BOF Error - Excel VBA

问题描述

当我尝试从excel文件查询数据库时收到以下错误:

I am receiving the following error when I try to query a database from an excel file:

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

为什么会弹出此错误?我已经测试了数据库中的SQL，并且可以正常工作(我已经检查了四次没有错字).

Why does this error pop up? I've tested my SQL in-database and it works without issue (I've checked 4 times that I don't have typos).

我计划将此数据的特定行添加到列表框，并对其余数据进行其他操作.为了测试我的SQL，我认为我只是迈出了第一步.

I plan on appending a specific row of this data to a listbox, and doing various other things with the rest of the data. To test my SQL, I figured I would just take this first step.

我的代码:

Private Sub UserForm_Initialize()
    Check = True
    On Error GoTo UserForm_Initialize_Err
    Dim cnn As Object
    Dim rst As Object
    Dim StrDBPath As String
    Dim strSQL As String
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    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;" & _
                             "Data Source=" & StrDBPath & ";" & _
                             "Jet OLEDB:Engine Type=5;" & _
                             "Persist Security Info=False;"
     rst.Open strSQL, _
              cnn, adOpenStatic
     rst.MoveFirst
     With Me.ListBox1
         .Clear
         Do
             .AddItem rst![Department]
             rst.MoveNext
         Loop Until rst.EOF
     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

推荐答案

在不知道出现错误的那一行的情况下，我认为这是在本节中

Without knowing what line you are getting the error on I assume it is in this section

 rst.MoveFirst
 With Me.ListBox1
     .Clear
     Do
         .AddItem rst![Department]
         rst.MoveNext
     Loop Until rst.EOF
 End With

我会进行这些更改.

 If rst.recordcount > 0 then
     rst.MoveFirst
 End if
 With Me.ListBox1
     .Clear
     Do while rst.eof = false
         .AddItem rst![Department]
         rst.MoveNext
     Loop
 End With

我还建议对您的记录集使用锁.查看adLockOptimistic和adlockpessimistic.

I would also suggest using a lock on your recordset. Look into adLockOptimistic and adlockpessimistic.

rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic 

要回答您的最后一条评论，我将尝试声明不同的对象.并不是说您的操作方式有什么问题，因为它不会产生任何错误.尝试这样

To answer your last comment I would try declaring the objects different. Not that there is anything wrong with the way you did it since it didn't produce any errors. Try like this

Dim cnn As new adodb.connection
Dim rst As new adodb.recordset

这是早期绑定.因此，您可以取出set cnn = CreateObject()和set rst = CreateObject() 看看有没有人能找到你.

This is early binding. So you can take out the set cnn=CreateObject() and set rst=CreateObject() See if that get's you anywhere.

这篇关于EOF/BOF错误-Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！