Error 3027 - Db or object is Read Only

[Len B]

I have a proc attached to the OnClick event of a button on a form.

In summary the procedure performs these tasks
01 Set dbs = CurrentDb()
02 Set rstBatch = dbs.OpenRecordset(stSelectB, dbOpenDynaset)
03 Set rstEquip = dbs.OpenRecordset(stSelectE, dbOpenDynaset)
04 Set rstMoves = dbs.OpenRecordset("Moves")
05 rstBatch.MoveFirst
06 With rstBatch
07 Do Until .EOF
08 find the matching record in rstEquip
09 set some variables using the current batch record data
10 Append new record to rstMoves and .Update
11 With rstEquip
12 .Edit !Location=stNewLocation .Update
13 End With
14===> .Edit
15 !Processed=True
16 .Update
17 .MoveNext
18 Loop
19 End With
20 Set dbs (and rst x 3) = Nothing

The .Edit statement (14) generates Error 3027 (Db or object RO).
I have concluded that the db isn't read only since (10) & (12)
do not generate an error. The statement (02) creating the rst referenced
in (14) is virtually identical to (03). Both selection criteria strings
contain FROM, WHERE, ORDER BY clauses. (02) contains SELECT DISTINCT but
(03) contains SELECT only.

Removing the DISTINCT removes the error. There should be no need to have
the DISTINCT clause but I cannot totally discount the possibility of
duplicate records in the Batch recordset.

2 Questions -
Why does the DISTINCT make the rst RO?
How do I protect from duplicates?

TIA

 

[Jerry Whittle]

In a query, a DISTINCT or GROUP BY clause will make it unupdateable. Think of
it this way: you could have a DISTINCT query which excludes the primary key
field. When you go to update the table, which records are you talking about?
SQL won't allow for that kind of ambiguity.

 

[Klatuu]

Using the DISTINCT is causing the problem. This is one of those cases where
the recordset becomes somewhat ambiguous and it doesn't know for sure what
record in the table relates to the returned values.

You need to determine what to do in the case of duplicate records. For
example, which record should actually be updated? Then you need to filter
the query to include only those records.

I would suggest you consider using queries for this operation rather than
recordset processing. You will find it to be much faster.

 

[Len B]

Thanks guys.
The Equip table contains EquipNum, Description, Location etc.
Only the (current)location has to be updated in this process.

The Moves table contains EquipNum(FK), MovedTo, From, ByWho, When
These give a movement history of the equipment item.

The Batch table contains data to create records in the Moves table and this
procedure
has to do that creating. There may be duplicate data here which will create
duplicate records
in the Moves table. Of course the autonumber PK has to be omitted from the
rst or DISTINCT
is pointless (as Jerry said). The records here are deleted once the
Movement record has
been created.
..
Perhaps it may be better to deal with duplicate Moves if/when that happens
rather than try to
prevent it in the first place.)

Dave, I'm not sure what your suggestion means. Do you mean that the Batch
records should create
an append query. The number of records in the Batch at any time will not be
large so speed isn't
really an issue but for the sake of my education could you please
elaborate?

--
Len
______________________________________________________
remove nothing for valid email address.
| Using the DISTINCT is causing the problem. This is one of those cases
where
| the recordset becomes somewhat ambiguous and it doesn't know for sure
what
| record in the table relates to the returned values.
|
| You need to determine what to do in the case of duplicate records. For
| example, which record should actually be updated? Then you need to
filter
| the query to include only those records.
|
| I would suggest you consider using queries for this operation rather than
| recordset processing. You will find it to be much faster.
| --
| Dave Hargis, Microsoft Access MVP
|
|
| "Len B" wrote:
|
| > I have a proc attached to the OnClick event of a button on a form.
| >
| > In summary the procedure performs these tasks
| > 01 Set dbs = CurrentDb()
| > 02 Set rstBatch = dbs.OpenRecordset(stSelectB, dbOpenDynaset)
| > 03 Set rstEquip = dbs.OpenRecordset(stSelectE, dbOpenDynaset)
| > 04 Set rstMoves = dbs.OpenRecordset("Moves")
| > 05 rstBatch.MoveFirst
| > 06 With rstBatch
| > 07 Do Until .EOF
| > 08 find the matching record in rstEquip
| > 09 set some variables using the current batch record data
| > 10 Append new record to rstMoves and .Update
| > 11 With rstEquip
| > 12 .Edit !Location=stNewLocation .Update
| > 13 End With
| > 14===> .Edit
| > 15 !Processed=True
| > 16 .Update
| > 17 .MoveNext
| > 18 Loop
| > 19 End With
| > 20 Set dbs (and rst x 3) = Nothing
| >
| > The .Edit statement (14) generates Error 3027 (Db or object RO).
| > I have concluded that the db isn't read only since (10) & (12)
| > do not generate an error. The statement (02) creating the rst
referenced
| > in (14) is virtually identical to (03). Both selection criteria strings
| > contain FROM, WHERE, ORDER BY clauses. (02) contains SELECT DISTINCT
but
| > (03) contains SELECT only.
| >
| > Removing the DISTINCT removes the error. There should be no need to
have
| > the DISTINCT clause but I cannot totally discount the possibility of
| > duplicate records in the Batch recordset.
| >
| > 2 Questions -
| > Why does the DISTINCT make the rst RO?
| > How do I protect from duplicates?
| >
| > TIA
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| >
| >
| >

 

[Klatuu]

I can't give you any detail because I don't know enough about your data, but
all I was saying is using Queries to do mass appends, updates, deletes is
faster.