Run Time Error 3027 - Can't update. Database or object is read only

[Phil Hood]

Hello,

I have attached the following code to the 'After Update'
event of a control on a form. However, I get an error
message:

"Run Time Error 3027 - Can't update. Database or object is
read only."

I think it might be something to do with the fact that the
select statement uses two tables but I'm not sure?

Any help would be greatly appreciated.

Thanks

Phil.

-----------------------------------------------------
Dim dbsCurrent As Database
Dim rstHeatBonusPoints As Recordset
Dim strQuerySQL As String
Dim rstHomeFirstAndSecond As Recordset
Dim strQuerySQL2 As String

Set dbsCurrent = CurrentDb

strQuerySQL = "SELECT * FROM Results WHERE [Results]!
[HeatID]= " & _
[Forms]![Heat edit]![HeatID] & ";"

Set rstHeatBonusPoints = dbsCurrent.OpenRecordset
(strQuerySQL)

rstHeatBonusPoints.MoveFirst
Do Until rstHeatBonusPoints.EOF
rstHeatBonusPoints.Edit
rstHeatBonusPoints!BonusPoints = 0
rstHeatBonusPoints.Update
rstHeatBonusPoints.MoveNext
Loop

Forms![Heat edit].Refresh

rstHeatBonusPoints.Close
dbsCurrent.Close

Set dbsCurrent = CurrentDb

strQuerySQL2 = "SELECT * FROM Results, RiderMatch WHERE
((([Results]![HeatID]) = " & _
[Forms]![Heat edit]![HeatID] & ") And (([Results]!
[Result]) = 3) And (([Results]![RiderID]) = [RiderMatch]!
[RiderID]) And (([RiderMatch]![ridingPosition]) < 9)) Or
((([Results]![HeatID]) = " & [Forms]![Heat edit]![HeatID]
& ") And (([Results]![Result]) = 2) And (([Results]!
[RiderID]) = [RiderMatch]![RiderID]) And (([RiderMatch]!
[ridingPosition]) < 9))ORDER BY Results.Result;"

Set rstHomeFirstAndSecond = dbsCurrent.OpenRecordset
(strQuerySQL2)

If Not (rstHomeFirstAndSecond.EOF And
rstHomeFirstAndSecond.BOF) Then

rstHomeFirstAndSecond.MoveFirst
rstHomeFirstAndSecond.Edit
rstHomeFirstAndSecond!BonusPoints = 1
rstHomeFirstAndSecond.Update
End If
-----------------------------------------------------

 

[Douglas J. Steele]

Without spending too much time trying to figure out why the query isn't
updatable, be aware that it's almost always more efficient to run an Update
query than to loop through a recordset updating a single field to the same
value.

In other words, your first recordset should be replaced with:

strQuerySQL = "UPDATE Results " & _
"SET BonusPoints = 0 " & _
"WHERE [Results]![HeatID]= " & _
[Forms]![Heat edit]![HeatID]

dbsCurrent.Execute strQuerySQL, dbFailOnError

and I'm afraid I'm too lazy right now to figure out the SQL to replace the
second one.

As to why your query isn't updatable, one reason is because you're not using
JOINS to relate the two tables.

While a little dated, the following article has good information about
updatable queries:
http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I have attached the following code to the 'After Update'
event of a control on a form. However, I get an error
message:

"Run Time Error 3027 - Can't update. Database or object is
read only."

I think it might be something to do with the fact that the
select statement uses two tables but I'm not sure?

Any help would be greatly appreciated.

Thanks

Phil.

-----------------------------------------------------
Dim dbsCurrent As Database
Dim rstHeatBonusPoints As Recordset
Dim strQuerySQL As String
Dim rstHomeFirstAndSecond As Recordset
Dim strQuerySQL2 As String

Set dbsCurrent = CurrentDb

strQuerySQL = "SELECT * FROM Results WHERE [Results]!
[HeatID]= " & _
[Forms]![Heat edit]![HeatID] & ";"

Set rstHeatBonusPoints = dbsCurrent.OpenRecordset
(strQuerySQL)

rstHeatBonusPoints.MoveFirst
Do Until rstHeatBonusPoints.EOF
rstHeatBonusPoints.Edit
rstHeatBonusPoints!BonusPoints = 0
rstHeatBonusPoints.Update
rstHeatBonusPoints.MoveNext
Loop

Forms![Heat edit].Refresh

rstHeatBonusPoints.Close
dbsCurrent.Close

Set dbsCurrent = CurrentDb

strQuerySQL2 = "SELECT * FROM Results, RiderMatch WHERE
((([Results]![HeatID]) = " & _
[Forms]![Heat edit]![HeatID] & ") And (([Results]!
[Result]) = 3) And (([Results]![RiderID]) = [RiderMatch]!
[RiderID]) And (([RiderMatch]![ridingPosition]) < 9)) Or
((([Results]![HeatID]) = " & [Forms]![Heat edit]![HeatID]
& ") And (([Results]![Result]) = 2) And (([Results]!
[RiderID]) = [RiderMatch]![RiderID]) And (([RiderMatch]!
[ridingPosition]) < 9))ORDER BY Results.Result;"

Set rstHomeFirstAndSecond = dbsCurrent.OpenRecordset
(strQuerySQL2)

If Not (rstHomeFirstAndSecond.EOF And
rstHomeFirstAndSecond.BOF) Then

rstHomeFirstAndSecond.MoveFirst
rstHomeFirstAndSecond.Edit
rstHomeFirstAndSecond!BonusPoints = 1
rstHomeFirstAndSecond.Update
End If