vba 错误 3021，没有找到当前记录，从 Access 查询数据到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7664151/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error 3021, no current record has been found, Query data from Access to Excel
提问by user941568
I'm writing a function in Excel VBA which will be called to extract data from an Access database, I'm using ADO connection. The function Get_g_gtop has parameters defined as bellow. Now, I try to use a command object to get the value from recordset, however, I got the error message 3021 : Either BOF or EOF is true, or current record has been deleted. Requested operations requires a current record. The debug points to the line : Get_g_gtop = rst.Fields(0).Value.
我正在 Excel VBA 中编写一个函数，该函数将被调用以从 Access 数据库中提取数据，我使用的是 ADO 连接。函数 Get_g_gtop 的参数定义如下。现在，我尝试使用命令对象从记录集中获取值，但是，我收到错误消息 3021：BOF 或 EOF 为真，或者当前记录已被删除。请求的操作需要当前记录。调试指向行：Get_g_gtop = rst.Fields(0).Value。
Is there anything wrong with the SQL statement to query in Access? Any advice would be highly appreciate!
在Access中查询的SQL语句有什么问题吗？任何建议将不胜感激！
Bing
必应
Function Get_g_gtop(ByVal VehType As String, ByVal Speed As Single) As Variant
函数 Get_g_gtop(ByVal VehType As String, ByVal Speed As Single) As Variant
Dim Dbfilepath As String
Dbfilepath = "C:\Users\sevenice\Desktop\EM Database.accdb"
Set cnn = New ADODB.Connection
cnn.Open "Provider= Microsoft.ACE.OLEDB.12.0;" & " Data Source=" & Dbfilepath & ";" & "Persist Security Info =False;"
'Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
'Dim QueryStr As String
Dim S As Single
If StrComp(VehType, "LDV") * StrComp(VehType, "LDT") * StrComp(VehType, "LHD<=14K") * StrComp(VehType, "LHD<=19.5K") = 0 Then
S = 35.6
'QueryStr = "SELECT [g/gtop] FROM [EM Database].[N (t) Data] WHERE [Vehicle Category]= "" & VehType & "" AND S = 35.6 " & " AND [Speed Lower] <= " & Speed & " AND [Speed Upper] >= " & Speed & ";"
cmd.CommandText = "SELECT [g/gtop] FROM [EM Database].[N (t) Data] WHERE [Vehicle Category]= "" & VehType & "" AND S = 35.6 " & " AND [Speed Lower] <= " & Speed & " AND [Speed Upper] >= " & Speed & ";"
'rst.Open QueryStr, cnn
Set rst = cmd.Execute
Get_g_gtop = rst.Fields(0).Value
ElseIf StrComp(VehType, "MHD") * StrComp(VehType, "HHD") * StrComp(VehType, "Urban Bus") = 0 Then
S = 26.7
QueryStr = "SELECT [g/gtop] FROM [EM Database].[N (t) Data] WHERE [Vehicle Category]=" & VehType & " AND S = 26.7 " & " AND [Speed Lower] <= " & Speed & " AND [Speed Upper] >=" & Speed & ";"
rst.Open QueryStr, cnn
Get_g_gtop = rst.Fields(0).Value
End If
End Function
结束函数
回答by Roman
After you open the recordset (Set rst = cmd.Execute) you will have to check whether it contains any data, before you try to access that data, for example:
打开记录集 ( Set rst = cmd.Execute) 后，您必须先检查它是否包含任何数据，然后再尝试访问该数据，例如：
if not rst.EOF then
'do your stuff with the data
end if
The error you are receiving indicates that you are not geting any records from your SELECT-Statement. Check it as suggested by HansUp.
您收到的错误表明您没有从 SELECT 语句中获得任何记录。按照 HansUp 的建议进行检查。
