Select Query acting like an Update Query

[Tboartz]

I am having trouble with my database where my select queries are acting as
update queries. If a user happens to accidentally or otherwise adjust the
results provided by the query, it is updating the information on the
table(s). I need to find out why it is doing this and how to prevent this
from occurring and altering the data.

Thank you,

Terri

 

[Ron2006]

I am having trouble with my database where my select queries are acting as
update queries. If a user happens to accidentally or otherwise adjust the
results provided by the query, it is updating the information on the
table(s). I need to find out why it is doing this and how to prevent this
from occurring and altering the data.

Thank you,

Terri

Any query, unless it is told otherwise (or is somewhat complex)
becomes an updateable query.

In the Form that you have defined using this query, change the
allowupdates, allowAdds, and Allowdeletes to NO.

or when called be sure to call it in readonly mode.


This is one reason why I always present queries as subforms on a form.
It gives me more power over the execution of the queries/forms.

Ron

 

[John W. Vinson]

I am having trouble with my database where my select queries are acting as
update queries. If a user happens to accidentally or otherwise adjust the
results provided by the query, it is updating the information on the
table(s). I need to find out why it is doing this and how to prevent this
from occurring and altering the data.

Thank you,

Terri

You are misinterpreting how Select queries work.

Your select query is working exactly as it is designed to work! It's not an
update query (which updates records the moment it's opened); instead, it is an
*updateable* query. Most Forms are based on such queries, allowing users to
edit data in tables without having to open the entire table.

A Select Query is simply a subset of the data in a table. It looks like a
table, acts like a table, and when the user edits it, the table is what's
being edited. The Query has no independent existance - it's *just a subset of
the table*.

Solution? Never present users with query datasheets (or table datasheets
either, for that matter), but only with Forms. You can set the Allow Updates
and Allow Additions and Allow Deletions properties of the form as appropriate
to let the users change only what you want them to change.

John W. Vinson [MVP]

 

[John Spencer]

You can change the SQL recordset type to SNAPSHOT. This will prevent the
users from updating the records

In query design view, select view: Properties from the menu. Click on the
upper half of the query design view (not on a table) to show the query
properties. Set record set type to Snapshot.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..