$Account.OrganizationName
The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
January 2006

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?

In this issue
  • Tip of the Month – How to Turn an Updatable Query into one that can't be Changed
  • Queries that will update data
  • Queries that will not update data
  • Conclusion and a Look Ahead
  • Trap of the Month – Beware Tables with Numbered Fields

  • Queries that will update data

    Simply stated, the following queries can update data in the databases if changes are made from the query:

    1. A query based on a single table (virtually always)
    2. A query based on a one-to-many relationship (almost always)
    3. 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:

    1. Your query is based on a single table
    2. Your query is based on a one-to-many relationship
    3. 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:

    1. Your query has a calculated field
    2. You are returning unique values
    3. Your query is a Totals or Group By query
    4. Your query is declared as a snapshot
    5. Your query is a Union query
    6. 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:

    1. Open the properties window
    2. Select View, then Properties. This will bring you to the query properties window.
    3. 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.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
     

     


    Custom Software | © Copyright Custom Software All Rights Reserved | Westford | MA | 01886