|
Queries – Sometimes you Can Update and Sometimes you Can't
A few years back a city slicker on his way to Bangor,
Maine became hopelessly lost. He finally found a
crusty old Yankee and asked him the best way to get
to
Bangor. He said he had very important business and
hoped that the old guy could tell him the quickest
route to his destination.
Being a Yankee, the old guy was not swayed by the
urgency expressed by the businessman, but decided
to help him out anyway. So he started "Well, first
you turn around, and then go back over the
bridge. . . "
"Wait, wait" said the businessman. "I didn't come
over any bridge."
With that, the old man turned his back and started
to walk away.
"Hey! Hold on - where you going?"
"I can't talk to you", said the old man, "If you didn't
go over a bridge then you're not here yet."
So, what does this have to do with Access
databases? Depending on how you structure a query,
sometimes you can change data in the database
from a query (a.k.a. an updatable query), and at
other times it won’t be possible to update the data
from a query (a.k.a. a non-updatable query), much
like the old guy talking to the city slicker. This
month, we’ll explore which queries will automatically
update data in the database and which queries will
not.
Note - I know this segue is a bit of a stretch - but
it's a great story isn't it?
| Queries that will update data |
 |
|
Simply stated, the following queries can
update data in the databases if changes are made
from the query:
- A query based on a single table (virtually always)
- A query based on a one-to-many relationship
(almost always)
- A query based on a many-to-many relationship
(sometimes)
The simplest query is one that comes from a single
table, and it will, by default, update data in the
database when data is changed from the query. It's
very similar to making changes to the data directly in
a table.
Take the query below which we've used many times
in the past – a listing of campers in a summer camp.
When this straightforward query runs, changes made
to data through the query interface are automatically
made in the data table.
Another type of query, which links the cabin table to
the camper table, will also automatically update data
in the database if changes are made from the query.
This is an example of a one-to-many relationship:
each cabin in the cabin table may hold many campers
from the camper table. If you have a query like this
and make a change to the cabin name when you run
the query, you'll see that the new cabin name has
replaced the former cabin name in the query results.
The change will propagate through all the records.
You're really changing the underlying data in the
cabin table and it immediately ripples to other rows in
your query.
What happens in a query like the one below in which
we show a many-to-many relationship?
Here I've changed the camper table slightly and
removed the cabin assignment I've also added a new
table -- trelCabinCamper. This many-to-many table
holds the assignment of campers to cabins.
This data in these tables are also updatable from a
query – but why?
It can be updated because Access is
able to do a logic walk and figure out how to resolve
your change in the subsidiary tables.
So we've seen that you can update data from a
query under the following circumstances:
- Your query is based on a single table
- Your query is based on a one-to-many
relationship
- Your query is based on a many-to-many
relationship
|
| Queries that will not update data |
|
|
So when can't you update data from a query? Well
you can't use a query to change data when Access
can't resolve the logic to make the changes.
The following (non-exhaustive) list includes situations
where you can't update the data in the database
from a query:
- Your query has a calculated field
- You are returning unique values
- Your query is a Totals or Group By query
- Your query is declared as a snapshot
- Your query is a Union query
- Your query is based on another query that is non-
updatable
Basically the more complex your query becomes (the
more tables and queries within your query), the less
likely it is that you'll be able to make changes to the
data from a query.
This subject is actually quite complex. If you'd like to
see more about this, open Access help and pose the
question: When can I update data from a query?
|
| Conclusion and a Look Ahead |
|
|
Although it's convenient to update data returned
from a query, it's not always possible. Sometimes, as
the old guys in Maine say, "You can't get there from
here." But sometimes you can build bridges and take
detours and still get to your destination. Next month
we'll look at ways you can turn a non-updatable
query into one that you can make updates from.
|
| Trap of the Month – Beware Tables with Numbered Fields |
|
Be on the lookout for tables that have fields followed
by 1, 2, 3 . . . (e.g., Teacher1, Teacher2,
Teacher3). This almost always indicates that you
have a normalization problem and have not set up a
valid many-to-many relationship. If you find this in
your tables, you have problems waiting to happen. If
you’ve had the application for a while, you’re
probably already jumping through hoops to get things
done. Consider taking the repeating data, putting it
into a separate table, and creating a many-to-many
relationship properly by creating an intermediary
table. If you're not sure how to do this, check out
the Normalization Articles in the
archive.
|
|
Tip of the Month – How to Turn an Updatable Query into one that can't be Changed |
|
|
|
Typically you’ll want to make changes from a query,
but every once in a while you’ll want to “lock” that
feature to prevent changes from being made to the
data when a user opens one of your queries. The
easiest way to prevent changes is to make one minor
change in the query properties.
In the query design window:
- Open the properties window
- Select View, then Properties. This will bring you
to the query properties window.
- Change the Recordset Type from Dynaset to
Snapshot.
A dynaset allows the underlying data to be updated
and deleted. A snapshot, however, is a picture of
your data, just like it sounds. The user may view a
snapshot but may not make changes to the data.
|
|
