error '3027' cannot update or object is read-only

[cogivina]

Hi All,

I'm having a problem of trying to update a table in access XP. I received the "error 3027 - cannot update. Database or object is read-only". Please advice. Thanks,
-------------------

Sub Update_To_Database()
Dim dbName As Database, Dim Strquery As String
Dim rstData As Recordset, Dim lngRows As Integer

Set dbName = OpenDatabase(DataBasePathName)

Set rstData = dbName.OpenRecordset("Loss Table", dbOpenTable)

STRQuery = " SELECT Month, Loss" _
& " FROM [Loss Table]" _
& " WHERE State= 'FL’" _
& " AND City = 'Miami'" _
& " GROUP BY Month" _
& " ORDER BY Month"

Set rstData = dbName.OpenRecordset(StrQuery, dbOpenDynaset)
ctr = rstData.RecordCount
if ctr <> 0 then rstData.MoveFirst

With rstData
For i = 1 To 12
Select Case ctr
Case Is <> 0
.Edit
!Loss = Range("Loss").Cells(i)
.Update
.MoveNext
Case 0
.AddNew
!State = "FL"
!City = "Miami"
!Month = Range("Loss_Table").Cells(i, 1)
!Loss = Range("Loss_Table").Cells(i, 2)
End Select
Next i
End With

Set rstData = Nothing
Set dbData = Nothing

End Sub

 

[xplo5iv]

Not sure if this helps, but have you tried removing the 'Group By' and 'Order by' from your SQL which opens the recordset?

HTH

Jonathan

 

[xplo5iv]

As a related thought, it's usually less error prone to use the recordset as read-only, and do the updating using SQL.

 

[cogivina]

Hi xplo5iv,

I removed the GROUP BY and it works. Could you explain what you meant "it's usually less error prone to use the recordset as read-only, and do the updating using SQL.". I'm not sure I understood.

Second problem: The codes below do not work if the record for the selected items does not exit. How do you write the code to add a new record to the table?
-------------------------------
Case 0
.AddNew
!State = "FL"
!City = "Miami"
!Month = Range("Loss_Table").Cells(i, 1)
!Loss = Range("Loss_Table").Cells(i, 2)
End Select
Next i
----------------------------

Thanks.

 

[cogivina]

Hi,

It worked after I added State, City... item selected in my STRQuery. It allowed me to add new record to the table in the database.

Thanks.

 

[xplo5iv]

Hi

Sorry I didn't get back to you earlier, I'm in England, and did my replies just before finishing work on Friday!

In a multi-user environment there can apparently be problems with corruption if updating via a recordset. I've never experienced it, but people far more experienced than me said so, and I've always followed their advice when possible!

Using SQL can also be quicker than VBA, I believe.

Glad you got sorted

Jonathan