Using a Join query, but unable to edit the data

G

Gilly

Hi

One of my colleagues has created a query using two tables, using the most
appropriate join for the type of data he wants displayed. He has also
applied referential integrity.

However, he now finds that he is unable to edit the data or add to this
table within this query.

Any suggestions?
 
A

Allen Browne

A 2-table query will generally be editable. You should be able to edit
fields and create records on at least one of the tables.

The query results will be read-only if any of the following apply:
.. It has a GROUP BY clause (totals query).

.. It has a TRANSFORM clause (crosstab query).

.. It contains a DISTINCT predicate.

.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).

.. It involves a UNION.

.. It has a subquery in the SELECT clause.

.. It uses JOINs of different directions on multiple tables in the FROM
clause.

.. 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.)
 
O

Ofer

Try and set the UniqueRecords Property of the query to yes, that allow me to
update alot of queries.
 
V

Vincent Johns

Allen said:
A 2-table query will generally be editable. You should be able to edit
fields and create records on at least one of the tables.

The query results will be read-only if any of the following apply:
. It has a GROUP BY clause (totals query).

. It has a TRANSFORM clause (crosstab query).

. It contains a DISTINCT predicate.

. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).

. It involves a UNION.

. It has a subquery in the SELECT clause.

. It uses JOINs of different directions on multiple tables in the FROM
clause.

. 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.)
Click to expand...

.... or if the Query's "Recordset Type" property is Snapshot instead of
Dynaset. (OK, he probably didn't do this, but I thought I'd mention it.)

Can he edit the data OUTSIDE the Query (directly in the Tables)? (If
not, the Query is not the source of his trouble.)

If he defines another Query using those Tables without referential
integrity required, can he update? If he sets the recordset type to
"Dynaset (inconsistent updates)", can he edit the data?

You might try variations of varying levels of restrictiveness to
determine at what point the Table becomes uneditable -- that should give
a pretty good clue as to the answer.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Top