Sub-Query not reflecting changes to the master query it uses

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

garyc - 07 Mar 2008 16:19 GMT

Error in Access 2007:

Changes to criteria in a query used as master query are not reflected in
sub-queries (which refer directly, and only, to that master query) unless the
database is closed then re-opened. Why?

I'm using XL professional, have 1gb ram, Access 2007 installed as part of
Office 2007. I'm using one table; all of my sub-queries refer to a master
query within my database.

Ex: If I make a change to my master query so that it collects a limited
range of dates, and then switch to a sub-query referring to that master, the
change in records shown in the master is not reflected in the sub-query.

In my testing, I created a new db with just a handful of records and a
couple of queries. The 'error' still occurred, so I ruled out that I had too
many queries or too many records. I then tried repairing and compressing the
db and the 'error' still occurred.

In previous versions of Access, the change to a master query always
immediately affected any sub-queries that referred to the master query so I
would expect 2007 to react in the same manner.
Has anyone had this occur and found a solution?
Thanks,

Signature

Gary

Reply to this Message

Michel Walsh - 10 Mar 2008 19:53 GMT

Jet does not support 'dynamic' set (dynamic as defined by ADO).

SELECT *
FROM somewhere
WHERE unitPrice >= 10

will continue to display records, once open, even if you change the unit
price to something < 10. The record will continue to be displayed even if
it does not satisfy, anymore, the condition (unless you re-open the
recordset). That is what a 'key-set' is about. The keys of the records (in
fact, bookmarks) are determined when the recordset is open, and from that
moment, the WHERE clause is forgotten... A record is to be displayed if its
key is in the keys-set, nothing more is checked. You can append new records,
but you won't see new records added by other users.

On the other hand, MS SQL Server can use a dynamic approach: for the same
query, if you change the unit price for something <10, the record is dropped
from the recordset. Side effect, you cannot UNDO such modification, since
you cannot touch the said record, anymore, if under a dynamic-set, if it
fall out of criteria. On the other hand, as for the keys-set, even with a
dynamic set, you won't see new records added by other users (unless you
re-open the recordset).

Sounds your environment uses a key-set, not a dynamic-set.

If you ask for a dynamic-set, with ADO, over a JET database, ADO will
gracefully degrade it to a key-set. You can check that by checking the
recordset type, ONCE it is open.

Hoping it may help,
Vanderghast, Access MVP

> Error in Access 2007:
>
[quoted text clipped - 26 lines]
> Has anyone had this occur and found a solution?
> Thanks,

Reply to this Message