DAO Recordset giving Read-only Error '3027'

[03Explorer]

I am trying to update current record being processed in a loop based on a SQL statement. But I am getting the error "3027: Cannot update. Database or object is read-only."

    Set rsGenerateMe = CurrentDb.OpenRecordset(SQL, dbOpenDynaset, dbFailOnError + dbSeeChanges)
    If Not (rsGenerateMe.EOF And rsGenerateMe.BOF) Then
        rsGenerateMe.MoveFirst
        Do Until rsGenerateMe.EOF = True

[COLOR=#CC0000]'Errors on line below[/color]
[COLOR=#4E9A06]'3027: Cannot update. Database or object is read-only.[/color] 
               rsGenerateMe.Edit
                rsGenerateMe!GeneratedYn = 1
                rsGenerateMe!GeneratedBy = GetLogonName()
                rsGenerateMe!GeneratedDT = Now()
                rsGenerateMe!SurveyNumberID = rsProjectDetails!SurveyNumber
                rsGenerateMe.Update
        rsGenerateMe.MoveNext
        Loop '-- rsGenerateMe

I also tried using the following to get the same result

              With rsGenerateMe
                   .Edit
                   !GeneratedYn = 1
                   !GeneratedBy = GetLogonName()
                   !GeneratedDT = Now()
                   !SurveyNumberID = rsProjectDetails!SurveyNumber
                   .Update
              End With

I also tried using 'dbOpenSnapshot' instead of 'dbOpenDynaset' "Operation is not supported for this type of object".

 

[03Explorer]

Well, for now, I decided to use a SQL INSERT command. Not sure which is better, but I need to have the data updating. My goal was due to being in a recordset loop, I was going to use native record updating.

Thoughts on recordset vs INSERT?

 

[Andrzejek]

First, you need to decide if you want to UPDATE an existing record (that's what you were going to do in your code, right?) or INSERT a new record with an INSERT statement. Because if you "need to have the data updating", I guess you may do INSERT a new record and DELETE existing one, but why would you want to do that?


---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

Oh, correct Andy... I meant to say "UPDATE" SQL and not "INSERT". Sorry. Thanks for the correction.

 

[Andrzejek]

I think you can do something like (code not tested):

Dim strSQL As String

strSQL = "UPDADTE MyTable " & _
  "SET GeneratedYn = 1, " & _
  "GeneratedBy = '" & GetLogonName() & "', " & _
  "GeneratedDT = #" & Now() & "#, " & _
  "SurveyNumberID = " & rsProjectDetails!SurveyNumber & _
  "WHERE SomePKField = 123"

CurrentDb.Execure strSQL

---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

Thanks Andy. My code I ended up using is similar to what you just provided. Because my code was updating within a Recrodset I was trying to use the recordset to update itself vs use an straight up SQL UPDATE code.

Much thanks for your code.