[AccessD] ADODB recordset problem

Hi Arthur,
1. use DAO
2. make sure the underlying Query that you are  using allows updates (open
it manually and add a  record)
3. It is more usual to add to a Table directly than via a Query.

Sql = "Select * from tblXYZ"
Set rst = currentdb.openrecordsource(sql)
If rst.eof then
	Rst.addnew
Rst!updatethisfield = "xy2
Rst.update
Endif



Air code, of course.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: 06 September 2009 15:06
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