Do I need to compact my database ?

[vamoose]

I have a Access 2000 database with 1 table and 1 form. When the database is opened it retrieves and inserts data into the table from another database then displays this data on the form for 20 seconds. After 20 seconds the data in the table is deleted, retrieved, inserted and displayed again, it does this all day long. This is it's only function, to display production data similar to the displays showing arrival and departue information in an airport. But by the end of the day the Access database has grown to approximately 100 MB. The original file size in the morning, with data, is only 500 KB. Am I doing something wrong. Do I need to compact every 20 seconds and how would this be accomplished with VBA ?

Thank you very much for the assistance.

 

[randy700]

Are you deleting the tables in your database and then recreating them? That would certainly cause it to grow in size. I'd suggest running queries that update the tables.

3 queries would accomplish the task.

Query1 - run an INSERT query to add new data.
Query2 - run a DELETE query to remove outdated information.
Query3 - run an UPDATE query to update the table.



Randy

 

[vamoose]

No I am not deleting and recreating the table, just deleting all data within the table and then inserting the data again.

 

[PHV]

Why using a temporary table instead of a query selecting from the another database ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GingerR]

100MB is nothing. However you can check COMPACT ON CLOSE in TOOLS+OPTIONS + GENERAL tab.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html