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.
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:
- We have added an expression that utilizes the
DCount function to the query. Because of space
restrictions, you cant 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."
- 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, youll 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.
|
|