Access is caching the record updates in memory. You need to
force it to write the changes to disk.
Open the table and moving to the last record does this.
However,
Try using TableDefs.Refresh method after each SQL
Alternatively, and probably preferred more this and other
reasons ---
Wrap the SQLs updating process within a transaction.
According, you can force Access to write the cache buffer to
disk when you Commit the Transaction.
From Online Help
============================================================
=================
In a Microsoft Jet workspace, you can include the
dbFlushOSCacheWrites constant with CommitTrans, This forces
the database engine to immediately flush all updates to
disk, instead of caching them temporarily. Without using
this option, a user could get control back immediately after
the application program calls CommitTrans, turn the computer
off, and not have the data written to disk. While using this
option may affect your applications performance, it is
useful in situations where the computer could be shut off
before cached updates are saved to disk.
Hope this helps.
Bill McKnight
Quote:
>Hi All,
>I have a problem, which I have no idea how to solve. It
goes like this:
>First i delete all records in a table like this (all this
is coded in
>vba):
>DELETE * FROM foo;
>Then I regenerate it like this:
>INSERT INTO foo SELECT moo.*,boo.* WHERE moo.nr=boo.nr;
>The problem is then; when I run a report a lot of records i
missing (!).
>Not until I open the foo table and close it again, all the
records are
>there when I run the report.
>Is there some special thing I have to do after a sql has
been exec. in
>vba, to update things?
>Best regards
>Terje Nagel