cannot update. DB or object is read-only 3027

[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

 

[Dirk Goldgar]

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

What is the SQL of qryTest?

 

[Tim]

Hi Dirk: The SQL of qryTest is
SELECT DISTINCT tblA.StuID, tblA.Email
FROM tblA
WHERE (((tblA.Email=0));

 

[Dirk Goldgar]

Hi Dirk: The SQL of qryTest is
SELECT DISTINCT tblA.StuID, tblA.Email
FROM tblA
WHERE (((tblA.Email=0));

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.

 

[Tim]

Got it! Thank you very much for your excellent help! I appreciate it!