Recordset Not Updateable

[Guest]

I have a query "qryCountsbyVendorTime" that could only be made updateable by
selecting Dynaset (inconsistent updates) in the recordset type property.

When I open the query in datasheet view, I can edit the count data.
I created a form based on this query, and switched to datasheet view, and
could not edit the data - the info bar at the bottom of the screen reads:
Dataset is not Updateable.

Why??? And how can I create a form based on this query (ideally a subform
so I can group records) that allows me to edit one of the fields?

Aaron

 

[Guest]

On looking through some of the help forums, and on the MVPS.org site, I came
to realize that my brilliant design idea of using lookup fields was maybe not
so brilliant. I went through each of the tables and removed the lookup
fields, then through the relationships diagram, deleted all of the
relationships, closed and reopened the database, and redrew all of my
relationships.

Unfortunately this still did not allow me to edit my records in the form...
WHY????

Aaron

 

[John Vinson]

When I open the query in datasheet view, I can edit the count data.
I created a form based on this query, and switched to datasheet view, and
could not edit the data - the info bar at the bottom of the screen reads:
Dataset is not Updateable.

Why??? And how can I create a form based on this query (ideally a subform
so I can group records) that allows me to edit one of the fields?

It's only rarely necessary or appropriate to base a single Form on a
multitable query; the only time I'd do it is if there were one or two
lookup tables (NOT lookup fields!!!) which cannot be properly
displayed using Combo Boxes.

Please post the SQL of the query. It may not be necessary to have it
multitable at all, but if it is, explain why and someone may be able
to pinpoint the block to updatability.

Note however that no query containing ANY grouping operation is
*ever* updateable.

John W. Vinson[MVP]

 

[Jon Jaques]

Hello,

Please correct me if I'm wrong, but I think the limitation (for Access?) is
that if there are more than two tables in a query, it is not updateable via
the query or form views (even though it IS editable via code).

For instance, I have a "master" table called "Trips", and two child tables,
"vendors" and "attractions"; If I join all three, like this:

SELECT tblTrips.TripID, tblTrips.TripName, tblAttractions.AttID,
tblAttractions.AttName, tblVendors.VendorID, tblVendors.VendorName
FROM (tblTrips INNER JOIN tblAttractions ON tblTrips.TripID =
tblAttractions.TripID) INNER JOIN tblVendors ON tblTrips.TripID =
tblVendors.TripID;

then that query will not be editable. Drop any one table from the query,
though, and voila! It works.

Is there any way to get around that limitation? Would this work using a
datagrid in VB.Net, or would I have to jump through hoops there, as well?

TIA

--Jon Jaques

 

[Rick Brandt]

Please correct me if I'm wrong, but I think the limitation (for
Access?) is that if there are more than two tables in a query, it is
not updateable via the query or form views (even though it IS
editable via code). [snip]
Is there any way to get around that limitation? Would this work using
a datagrid in VB.Net, or would I have to jump through hoops there, as
well?

You are incorrect. There are various reasons why a query will become
non-updateable and the more tables you add the more likely that is to
happen, but there is not a hard limit of two tables. One can create a
single table query that is not editable and one can also create a query with
more than two tables that IS editable.

In addition, if a query is non-updateable then it is non-updateable using
ANY method. Using code does not make any diffference.

There is a help topic that discusses this that you need to check out.

 

[Steve Zag via AccessMonster.com]

So what is the best way to

 

[Steve Zag via AccessMonster.com]

So the problem I am having is that I often have tables I want to update with the results of queries that are not updateable. For instance I want to post a number from a count I got from a sum query but obviously it won't let me. What is the best way to update this table when the data comes from the output of multiple table queries?

 

[John Vinson]

So the problem I am having is that I often have tables I want to update with the results of queries that are not updateable. For instance I want to post a number from a count I got from a sum query but obviously it won't let me. What is the best way to update this table when the data comes from the output of multiple table queries?

The best way to deal with calculated fields (sums, counts) is to
recalculate them as needed, and NOT to store them in ANY table.
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]