Update recordset "Databse or object is Read only" error

[Alan]

Hi,

In Access 2003, I'm trying to update one field in a recordset and keep
getting the error "3027, database or object is read only." The code is
executed by a button click on a form. I've tried some of the suggestions
i've found, but still can't get it. I've also tried the DIMs as "AS
Database" and "AS DAO.Databae" (and recordset) with no better results. I
know it's something simple.

The code in the true part of "If rstJobs.RecordCount = 0 Then" works fine.
The Else part is creating the error marked by '<-- **ERROR HERE

Please help.

======

Private Sub Command2_Click()

Dim strDB As Database
Dim strSQLFindOld, strSQLDelta As String
Dim nNewDelta As Integer
Dim rstJobs, rstDelta As Recordset


' set database connection
nNewDelta = 0
Set strDB = CurrentDb

' SQL statements
strSQLFindOld = "SELECT Job_No, Job_ID, Delta, vbc, date_Import FROM
tblTransactions " & _
"WHERE Job_No in (SELECT Job_No FROM tblTempImport)"
strSQLDelta = "SELECT n.Job_No, n.Job_ID, n.VBC AS NewVBC, o.VBC AS OldVBC,
n.Delta AS VBCDelta, Min(o.date_Import) AS OldDate " & _
"FROM tblTransactions AS o INNER JOIN tblTempImport AS n ON
n.Job_No=o.Job_No AND n.Job_ID=o.Job_ID " & _
"GROUP BY n.Job_No, n.Job_ID, n.VBC, o.VBC, n.Delta"


' create recordset - job exists in table
Set rstJobs = strDB.OpenRecordset(strSQLFindOld)
If rstJobs.RecordCount = 0 Then
MsgBox "No Records"
DoCmd.SetWarnings False ' disable warnings
DoCmd.OpenQuery "qryAppendImport"
DoCmd.SetWarnings True ' enable warnings
rstJobs.Close
Set rstJobs = Nothing
Else
MsgBox "Found Records"
rstJobs.Close
Set rstJobs = Nothing
Set rstDelta = strDB.OpenRecordset(strSQLDelta, dbOpenDynaset)

Do While Not rstDelta.EOF
nNewDelta = (rstDelta("NewVBC") - rstDelta("OldVBC"))
MsgBox nNewDelta
rstDelta.Edit '<--- **ERROR HERE
rstDelta("VBCDelta") = nNewDelta
rstDelta.Update
nNewDelta = 0
rstDelta.MoveNext
Loop
End If
rstDelta.Close
Set rstDelta=Nothing

End Sub

 

[Dirk Goldgar]

Hi,

In Access 2003, I'm trying to update one field in a recordset and keep
getting the error "3027, database or object is read only." The code is
executed by a button click on a form. I've tried some of the suggestions
i've found, but still can't get it. I've also tried the DIMs as "AS
Database" and "AS DAO.Databae" (and recordset) with no better results. I
know it's something simple.

The code in the true part of "If rstJobs.RecordCount = 0 Then" works fine.
The Else part is creating the error marked by '<-- **ERROR HERE

Please help.

======

Private Sub Command2_Click()

Dim strDB As Database
Dim strSQLFindOld, strSQLDelta As String
Dim nNewDelta As Integer
Dim rstJobs, rstDelta As Recordset


' set database connection
nNewDelta = 0
Set strDB = CurrentDb

' SQL statements
strSQLFindOld = "SELECT Job_No, Job_ID, Delta, vbc, date_Import FROM
tblTransactions " & _
"WHERE Job_No in (SELECT Job_No FROM tblTempImport)"
strSQLDelta = "SELECT n.Job_No, n.Job_ID, n.VBC AS NewVBC, o.VBC AS
OldVBC,
n.Delta AS VBCDelta, Min(o.date_Import) AS OldDate " & _
"FROM tblTransactions AS o INNER JOIN tblTempImport AS n ON
n.Job_No=o.Job_No AND n.Job_ID=o.Job_ID " & _
"GROUP BY n.Job_No, n.Job_ID, n.VBC, o.VBC, n.Delta"


' create recordset - job exists in table
Set rstJobs = strDB.OpenRecordset(strSQLFindOld)
If rstJobs.RecordCount = 0 Then
MsgBox "No Records"
DoCmd.SetWarnings False ' disable warnings
DoCmd.OpenQuery "qryAppendImport"
DoCmd.SetWarnings True ' enable warnings
rstJobs.Close
Set rstJobs = Nothing
Else
MsgBox "Found Records"
rstJobs.Close
Set rstJobs = Nothing
Set rstDelta = strDB.OpenRecordset(strSQLDelta, dbOpenDynaset)

Do While Not rstDelta.EOF
nNewDelta = (rstDelta("NewVBC") - rstDelta("OldVBC"))
MsgBox nNewDelta
rstDelta.Edit '<--- **ERROR HERE
rstDelta("VBCDelta") = nNewDelta
rstDelta.Update
nNewDelta = 0
rstDelta.MoveNext
Loop
End If
rstDelta.Close
Set rstDelta=Nothing

End Sub

Your recordset rstDelta is opened on a SQL statement that is a totals query
(contains the GROUP BY clause). Such a query (and hence any recordset
opened on it) can never be updatable, because each record in the recordset
may be an aggregate of more than one record from the underlying table(s).

What, in plain terms, is it that you want to do?

 

[Alan]

Thanks for the reply Dirk. You gave me an idea to try.

What I want to do is this:
I have 2 tables that are identical. tblTempImport holds data just imported
from a delimited file. This data gets appended to tblTransactions after a
calculation is made. I want to read each record in the Temp table, and find
the oldest record (field date_Import) with matching Job_No and Job_ID from
tblTransactions, if there is one. I then want to take the difference between
tblTransactions.VBC and tblTempImport.VBC, and put the result into
tblTempImport.Delta. Then all of tblTempImport gets appended to
tblTransactions. The append part I can do.

I know I said oldest record above, and in the query I have MIN. I realized
on my drive home it should be a MAX function.

Tomorrow I was going to try a record set from just the temp table not joined
to the other one, then grab tblTransactions.VBC with a separate query and do
the calculations. That should avoid the GROUP BY on the table I want to
update.

Alan

 

[Alan]

Dirk - Thanks. Your answer prompted me to try a different approach, and it
worked. I removed the join and queried each table separately. Works like a
charm.