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屋!