The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
February 2006

You can get there from here: Changing a non-updatable query to one where you can change the data

Last month we ran into the old Maine codger who refused to give the city slicker directions because the tourist hadn't realized that he had gone over a bridge before talking to the old Yankee. Of course the codger realized that the tourist was just a bit confused and was just giving him a hard time-it was a variation of the classic Maine response to the question "How do you get to . . .", which is "Well, you can't get theah from heah."

Of course it's always possible to get from point A to point B -- it's just a question of how complex and involved the process is. Access queries can be just as cantankerous as the old Yankee. In the case of a complex query, it may appear that the query cannot be used to update the data in the database (a non- updatable query), but there are ways to modify a query so that it can be used to change the data in the database (an updatable query). That's the focus of this month's Wizard: How to turn a non-updatable query into an updatable query.

In this issue
  • Tip of the Month- Find the Link for an Attached Table
  • The Problem
  • The Simple Solution
  • Conclusion, a Caveat, and a Look Ahead
  • Trap of the Month – Bouncing Between Records in a Form

  • The Problem

    Let's take a look at a query that cannot be used to update data in the database (a non-updatable query). Once again we're using a summer camp database and we have a query that is detecting activities that have fewer than three campers registered. These activities will be consolidated with other activities or cancelled due to low enrollment. The query below answers this question:

    Figure 1

    It is important to realize that the reason a query is non-updatable is often because the query relies on another query that is non-updatable. This is the case in this example shown in Figure 1. This query (qryNoUpdate3) uses another query (qsumActivityCount), a query which counts the number of activities campers have signed up for. It's this summation or Totals query that makes the query above non-updatable, because it, in itself, is a non- updatable query – see the January 2005 Access Wizard for more information about summation queries.

    When the query in Figure 1 is run it returns the following:

    This report shows the activities that have less than three campers enrolled. Let's say that you decide you want to change the Activity Name Ping Pong to Table Tennis. You can certainly do that in the Activity table, or maybe from a form, but you can't do it from this query because the data it returns is non-updatable. For demonstration purposes, let's say that we really do want to change the name from Ping Pong to Table Tennis everywhere in the database through this query interface. How can we do that? Well there are at least two solutions. This month, we'll take a look at the simpler of the two – replacing the non-updatable summation query used in this query with the function Dsum.

    The Simple Solution

    Before we move to the solution, we really should understand the non-updatable summation query used to count the number of campers signed up for each activity. The figure below presents the query in the design view:

    As I mentioned earlier, this is a summation (or summary) query. When you are using or creating a summary query, you'll see the ∑ symbol at the top of the screen in the menu bar. It uses the table trelCamperPrefActivity (a trel table is an intermediate table for many-to-many relationships, see September 2004) that tracks campers who have signed up for various activities.

    Look back at our original query in Figure 1 above. If we can remove the query qsumActivityCount (a non- updateable summation query) from our query with an expression using the Dcount function, we'll be able to update data in the database when the query runs.

    Here's our revised query:

    The changes to the original query are as follows:

    1. We have added an expression that utilizes the DCount function to the query. Because of space restrictions, you can’t see the entire expression, so here it is in its entirety:

      Expr1: DCount ("ActivityKey","trelCamperPrefActivity","ActivityKey=" & [tblActivities].[ActivityKey])

      The DCount function essentially counts the number of instances that a certain criteria is met. I won't try to explain all the intricacies here. It's part of the much larger subject of domain functions that will be featured in a future article. In the meantime, in order to understand how to use it, search Access Help for "DCount Function."

    2. We've replaced the summation query qsumActivityCount with the table trelCamperPrefActivity – this was the original basis for the summation query. The table trelCamperPrefActivity is called by the DCount function.

    The results obtained with this revised query look exactly the same as before; however there is one very important difference. If we want to change Ping Pong to Table Tennis we can do it directly in the query when it runs and it will ripple throughout the application.

    It's the removal of the summation query that changes the status of the query to updatable. Since we still care about the count, we get to that through explicit counting using the DCount function.

    Conclusion, a Caveat, and a Look Ahead

    Last month we looked at when you can update data in the database through a query (an updatable query) versus when you cannot (a non-updatable query). This month, we discussed a solution that can turn a non-updatable query into an updatable query in certain circumstances. We replaced a summation query with a DCount function calling a many-to-many table, thereby converting a non-updatable query to an updatable query. The DCount function is resource intensive, and if used with a large query that returns a lot records, you may find that performance plummets.

    There are other ways to turn non-updatable queries into queries in which the data can be changed, including something called Sub queries. Be on the lookout for this solution to our problem in an upcoming issue.

    Trap of the Month – Bouncing Between Records in a Form

    Frequently when editing data in a form, you’ll find that when your cursor is in certain fields the form will jump to the next record when you prefer to stay on the current record. This can be really annoying since it requires you to hit the prior record button, or even worse you start entering data on the wrong record.

    Preventing this record jumping behavior is simple with a small change to the form design:

    Open the form in design view and go to the form properties (View | Properties from the menu at the top of the screen). On the tab labeled "Other" there is a property called "Cycle". This property controls what happens when you reach the last control on your form. The default is "All Records." By changing this to "Current Record," you'll no longer find yourself jumping to the next record when you don't intend to.

    Tip of the Month- Find the Link for an Attached Table

    In many of the applications you use and create, you'll have tables that are linked from other databases. You can tell they're linked because to the left of the table name you'll see a symbol indicating the type of link. Most typically the link will be small blue arrow, indicating a link to another Access database.

    Sometimes you'll want to know which database is the source of a linked table. Intuitively, you'd look under the table properties (right click on the table name and choose properties). Strangely, Microsoft, in all its wisdom, has chosen not to show this information in the properties window.

    In order to find the source database for a linked table, open the table in design view – you'll get a message that some properties cannot be modified – say yes to the prompt.

    In the window that comes up, right click in the white space and a dialog box will open. This dialog box contains properties about the table (different properties from what you see if you right click on the table itself and choose properties – yes it's confusing and yes Microsoft got this wrong.) The Description property contains information about the linked table including the full path to the database holding the file as well as the table name. You may have to expand the dialog box to see the information in its entirety, but it will show you what you need to know about the source of the linked table.

    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
    Powered by

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