remove read-only status

[Cooz]

Hi everyone,

When I try to run an Access application that I placed on my standalone
laptop, somewhere along the way error 3027 "Cannot update. Database or object
is read-only." pops up.

I discovered that this happens in the VBA line
rs.Edit

This line is preceded by
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Qr1 WHERE " & criteria$)
Do While Not rs.EOF

Qr1 is a query that contains about six tables; criteria$ is a string that
correctly complements the SQL statement. The network version of the
application doesn't have a problem with these lines of code anyway.

My question is: what exactly can have become read-only; how can I check this
and how can I make it read-write again? When I right-click the tables that
take part in Qr1, I cannot find a read-only attribute under 'Properties'.

Thank you,
Cooz

 

[John Spencer]

If you open qr1 directly, can you update records in it?

With 6 tables involved, the answer is probably no.

There are many reasons that records returned by a query cannot be updated.

In Access Help type the following in the Answer Wizard tab
When can I update data from a query
In the results window select About Updating Data and select the
appropriate option.

If you cannot edit the data in a query, this list may help you identify
why it is not updatable:
--Query based on three or more tables in which there is a
many-to-one-to-many relationship
--Query that includes a linked ODBC table with no unique index or a
Paradox table without a primary key
--Query that includes more than one table or query and the tables or
queries aren't joined.

--It has a TRANSFORM clause. A Crosstab query is always read-only.
--It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause.
Queries that aggregate records are read-only.
--It has a GROUP BY clause. A Totals query is always read-only.
--It contains a DISTINCT predicate (i.e.; Unique Values is YES in the
query's properties.)
--It involves a UNION. Union queries are always read-only.

--It has a subquery in the SELECT clause.
--It uses JOINs of different directions on multiple tables in the FROM
clause.
--The fields in a JOIN are not indexed correctly: there is no primary
key or unique index on the JOINed fields.
--The query's Recordset Type property is Snapshot. Set Recordset Type to
"Dynaset" in the query's Properties.
--The query is based on another query that is read-only (stacked query.)

--Your permissions are read-only (Access security.)
--The database is opened read-only, or the file attributes are
read-only, or the database is on read-only media (e.g. CD-ROM, network
drive without write privileges.)

-- Access 2007: The query calls a VBA function, but the database is not
in a trusted location so the code cannot run.

Also see
http://allenbrowne.com/ser-61.html


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Cooz]

Thank you John, for your profound answer.

Still, the db works fine in the network environment, which implies that Qr1
can be updated over there. Since I use Access 2003, only "Your permissions
are read-only" and "The database is opened read-only, or the file attributes
are read-only, or the database is on read-only media" are left. I've checked
that - and they can be eliminated as well.

But.

By adding the dbOpenDynaset, 0, dbOptimistic parameters to the rs
definition, I obtained a read-write recordset. Ha! Microsoft documents this
in kb article 209943, which applies to Access 2000. I use Access 2003 - an
update would be nice. It makes me wonder why the original on the network
functions ok without these three, though.

Thanks for wanting to help - I appreciate it.

Cooz

 

[Fred]

Sorry to ask something this simple, but I'm assuming that you already checked
the Windows file attributes on the db file? Certain ways of moving it to
the laptop (e.g. by CD) could have changed it to read only.

 

[Cooz]

Hi Fred,

Yes, that was the first thing I did. There was no cd involved.

Cooz