[AccessD] ADODB recordset problem

A guess: the query is not updateable.  Run the query a la carte and see if
you can add a record to it manually.  

How many tables is the query based on?

But I don't know about your code to add the values because I don't know ADO.


Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Sunday, September 06, 2009 7:06 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] ADODB recordset problem

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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com