cannot update. DB or object is read-only 3027


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
 
Ad

Advertisements

D

Dirk Goldgar

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


What is the SQL of qryTest?
 
T

Tim

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

Dirk Goldgar

Tim said:
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top