[AccessD] ADODB recordset problem

Arthur:

Why are you using ADO instead of DAO?

Rocky


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

Arthur,

The default value for CursorType is adLockReadOnly - try this:

rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Arthur Fuller
Sendt: 6. september 2009 16:06
Til: Access Developers discussion and problem solving
Emne: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com