[AccessD] ADODB recordset problem

Arthur Fuller fuller.artful at gmail.com
Sun Sep 6 09:06:02 CDT 2009


I have a query that may or may not return any rows. I create a recordset
based on the query and manipulate it using an ADODB recordset. In the event
that the recordset is empty (query returns no rows), I need to add a row to
the recordset and place text in it saying "Nothing to report".
The code I'm using is:

<vba>
    Dim rs As ADODB.Recordset
    Dim sSQL As String
    Dim intAssessID As Integer

    intAssessID = CurrentAssessID()
    sSQL = "SELECT * FROM Pre_Start_Review_Report_qry_New"

    Set rs = New ADODB.Recordset
    rs.Open sSQL, CurrentProject.Connection, adOpenDynamic

' Code added to deal with the No NO Issues problem
' Append a record to the record set saying "Nothing to Report" if the record
set is empty
    If rs.BOF And rs.EOF Then
        Dim varFields()
        Dim varValues()
        varFields = Array("MemoID_Field", "AssessID")
        varValues = Array("Nothing to Report", intAssessID)
        rs.AddNew varFields, varValues
        rs.Update
    End If
</vba>

The error message I'm getting is "Current RecordSet does not support
updating..." I have also commented out the adOpenDynamic part of the rs.Open
statement but that didn't work either. What am I doing wrong?

TIA,
Arthur



More information about the AccessD mailing list