Do I have to compact a databse?

[Mark Sreeves]

Access 97 database, VB front end

I understand that when data is deleted from the database its size remains the same until it has been compacted.

Now, my question is...

Is new data written over the deleted data or does it get appended to it?



a database:
XXXXXXXXXXXXXXXXXXXXXXXXXX

add new data(00000)
XXXXXXXXXXXXXXXXXXXXXXXXXX00000

delete some data:
XXXXXXXX________XXXXXXXXXX


compact the database:
XXXXXXXXXXXXXXXXXX

when I add new data and the database has NOT been compacted which of the following occurs?

XXXXXXXX________XXXXXXXXXX00000

or:

XXXXXXXX00000___XXXXXXXXXX

[Mark Sreeves]

Ok, so I've just found out for myself that the database continues to expand even after data has been deleted!


Apart from wastiing disk space, does leaving the database uncompacted have any other implications?

[MrShickadance]

The reason why records don't get deleted is because you would have to rebuild the database everytime you wanted to delete a record. This is a performance hit if you have to rebuild everytime. It's much easier to append and mark as deleted esp. if you have variable width fields. You can kind of think of it as your harddrive. You have to defragment the drive every so often to reduce space waste and optimize data reads. The same is true for databases. Image if your computer had to defrag everytime you deleted a file.