Appended data not being recognized

[OldGuy]

I appended data to an existing table using an append query. Existing
queries, based on the table, did not bring up the appended data. Queries
created after the fact did. What did I do wrong? Thanks in advance.

 

[Michel Walsh]

Queries, and recordset, do not see records append by 'others' unless they
are re-queried. They only see the records you append THROUGH THEM. You have
to give a very large meaning to 'others', it includes your VBA code, or even
yourself, in the user interface, but using other queries, (or the tables,
directly) and even form. So, you have to requery the query (or form) to see
data appended by others. You don't have to requery to see known data now
DELETED data, neither MODIFIED data (unless there is a transaction going
on), just for new appended data.


Vanderghast, Access MVP

 

[OldGuy]

Michel,
Thank you, but I’m not sure I understand. Are you saying that if I append
new records to an existing table and run a pre-existing query against it, the
recordset will not include the newly appended records?

 

[Michel Walsh]

If you HISTORICALLY do:

1) open the recordset (a 'dynaset' one, not a firehose),

2) append data to the table directly from the table view

then the recordset (1) won't see the record you added in (2) unless you
requery the recordset. You can close it, then re-open it to see what (2) has
added too, INSTEAD of REQUERYing it. Requerying may save some time, and it
is probably somehow easier, if you just want see records added by 'others'.




In Northwind: Open the form Categories. Wait until the 1 OF 8 is
displayed at the bottom. Keep it open.
Open the table Categories, add a category, save the record, close the
table.
Observe that the form Categories is still showing 1 OF 8 and won't see
the new record you added through another mean (here, using the table,
directly).


(remove the record you added in Categories to get it back to its initial
state).



Not all recordsets would behave the same way. A forward only, read only
recordset won't do that, but the common one, a DAO dynaset or an ADO keyset
recordset will. These 'high tech' recordsets determine the 'bookmarks' of
the records they can reach at the moment they are opened and from that
point, can keep track of new records added through them (adding their
bookmarks to their collection of already known bookmark) but are clue-less
about other records added by other means. They can see records modifications
(update and delete) of those records they know about their bookmarks, even
if these modifs are done through other means, but that is all what they can
do. The cannot see new records added by other means, unless you recreate
their bookmark collection (requery). A low tech recordset like a forward
only read only does not do that: it is like a 'pointer' standing on a record
and that pointer will seek the next record if you move forward: it does not
maintain a collection of bookmarks... since it plays with only one record at
any time, in fact. But you can't append record through a read only
recordset, after all.




Vanderghast, Access MVP

 

[OldGuy]

I used an append action query to add the records to the table. I assume that
was directly. I can see the data in the table. However, queries (for
reports) created before the addition do not bring up the new data. I don't
know why the dynaset would not include them. I tried removing all criteria
from the select query, but still no new records.