SQL查询与VBA错误 [英] SQL Query & VBA error

问题描述

可能重复：

SQL查询和VBA错误

我一直在收到此错误：

BOF或EOF设置为True，或者当前记录已删除。请求的操作需要当前记录。

BOF or EOF set tu True, or the current record has been deleted. The requested operation requires a current record.

    MyQuery = "Select Destinataire, SUM(" & Entete & ") AS NombreTotal FROM [Feuil1$] " _
         & "WHERE [DateMad] Between #" _
         & Format(date_deb, "yyyy/mm/dd") _
         & "# And #" & Format(date_fin, "yyyy/mm/dd") & "#" & Query3 & " Group By Destinataire"

         objRecordSet.Open MyQuery, objConnection, adOpenStatic, adLockOptimistic

         Feuil3.Cells(1, Col) = Entete
         Feuil3.Cells(strLine, 2) = Entete2
         Feuil3.Cells(2, Col).Value = objRecordSet.Fields("NombreTotal").Value

我在最后一行得到错误

  Feuil3.Cells(2, Col).Value = objRecordSet.Fields("NombreTotal").Value

为什么要我提供BOF或自从我问SUM以来的EOF？还如何使这个查询工作plz？
i无法进行调试

why did it asks me for an BOF or EOF since i'm asking the SUM? also how to get this query to work plz ? i cannot breathe with this debugging

debug.print MyQuery提供以下内容：

the debug.print MyQuery give the following :

 Select Destinataire, SUM(NbCompteurElecNR) AS NombreTotal FROM [Feuil1$] WHERE [DateMad] Between #2012/11/21# And #2012/11/26# And [Destinataire] = 'REL12' Group By Destinataire

推荐答案

无法对您的数据发表评论但您收到了一个EOF错误，因为您的查询（MyQuery）不会导致任何数据行。使用查询打开记录集后，必须先检查它是否包含任何行，然后再继续（即，它尚未位于文件 EOF的末尾）。

Unable to comment on your data but you recieve an EOF error because your query (MyQuery) does not result in any rows of data. After you have opened your record set with your query, you must first check that it contains any rows before continuing (ie. it is not already at the end of the file 'EOF'). See below for example.

MyQuery = "....."

Set objRecordSet = New ADODB.Recordset
objRecordSet.Open MyQuery, objConnection, adOpenStatic, adLockOptimistic

If Not objRecordSet.EOF Then
    'The record set contain one or more rows, do stuff
Else
    'The record set does not contain any rows, take action and notify user
End If

objRecordSet.Close
Set objRecordSet = Nothing

如果您正在执行更新/插入操作，则应在发生以下情况时对数据库进行适当的回滚：您将收到数据库错误或其他故障，否则您可能会使数据库处于不愉快的状态。

If your in the middle of an update/insert operation, you should do a proper rollback of the database in the event that you get an DB error or something else fails, otherwise you can leave the database in an unpleasant state. See below for example.

On Error Goto Rollback

objConnection.BeginTrans

MyInsertQuery = "..."

Set objRecordSet = New ADODB.Recordset
objRecordSet.Open MyInsertQuery , objConnection, adOpenStatic, adLockOptimistic

objRecordSet(1) = someValue
objRecordSet(2) = someOtherValue
objRecordSet.Update

objRecordSet.Close
Set objRecordSet = Nothing

objConnection.CommitTrans

Exit Sub

Rollback:
    objConnection.RollbackTrans
    objRecordSet.Close
    Set objRecordSet = Nothing

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