Technical question. 
Author Message
 Technical question.

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



Sun, 03 Dec 2000 03:00:00 GMT  
 Technical question.

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



Sun, 03 Dec 2000 03:00:00 GMT  
 Technical question.

Just a comment, since Bill already answered your question:

Is this a real query?  If so, you might want to use a join.  It will
probably run much faster then.

Alden


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



Mon, 04 Dec 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Technical question

2. Technical question on the Design Environment

3. Technical question

4. Beginner VB user has a technical question.

5. Highly Technical Question

6. Highly Technical Question

7. Highly Technical Question

8. Advanced technical analysis of stock indexes for the technical programmers who invest - djia.gif (1/1)

9. Advanced technical analysis of stock indexes for the technical programmers who invest - djia.gif (0/1)

10. Stock Technical Analysis Question

11. Looking for a set of question to do VB technical inteview

12. VB Technical Interview Questions.

 

 
Powered by phpBB® Forum Software