cannot update. DB or object is read-only 3027

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I use Access2007. The codes are:

str = "select StudID, Email from qryTest;"
Set rst = DB.OpenRecordset(str, dbOpenDynaset, dbSeeChanges)
rst.MoveFirst
Do While Not rst.EOF
Call ManipulateData(rst("StudID")) 'call a sub-procedure
rst.Edit
rst("Email") = True
rst.Update
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
DB.Close
Set DB = Nothing
 
Tim said:
I use Access2007. The codes are:

str = "select StudID, Email from qryTest;"
Set rst = DB.OpenRecordset(str, dbOpenDynaset, dbSeeChanges)
rst.MoveFirst
Do While Not rst.EOF
Call ManipulateData(rst("StudID")) 'call a sub-procedure
rst.Edit
rst("Email") = True
rst.Update
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
DB.Close
Set DB = Nothing
Click to expand...


What is the SQL of qryTest?
 
Hi Dirk: The SQL of qryTest is
SELECT DISTINCT tblA.StuID, tblA.Email
FROM tblA
WHERE (((tblA.Email=0));
 
Tim said:
Hi Dirk: The SQL of qryTest is
SELECT DISTINCT tblA.StuID, tblA.Email
FROM tblA
WHERE (((tblA.Email=0));
Click to expand...


There's your problem then. DISTINCT queries aren't updatable, because one
output record can potentially represent multiple input records. If you want
your query to be updatable, you have to remove the DISTINCT keyword, and
live with the possibility that it will return more than one record with the
same combination of StuID and Email. Of course, if StuID is a unique field
in tblA, that's not going to happen -- in that case, there would have been
no need for the DISTINCT keyword (= "Unique Values" property of the query)
in the first place.
 
Back
Top