Best way to add and delete data

[Karen]

I have a log-database where data is added and deleted
based on a timer.

I can specify the size of the log, so when the log
is "full" I delete the oldest record when a new is added.

The adding and deleting makes the database grow untill it
is compacted.

Are there any "good practices" for this kind of procedure?

I thought about not actually adding and deleting - but
just overwrite the oldest record with the new information.
Would that save me space - and how would it affect the
performance?

I know I could just try - but any comments are appreciated.

 

[John Vinson]

I have a log-database where data is added and deleted
based on a timer.

I can specify the size of the log, so when the log
is "full" I delete the oldest record when a new is added.

The adding and deleting makes the database grow untill it
is compacted.

Are there any "good practices" for this kind of procedure?

I thought about not actually adding and deleting - but
just overwrite the oldest record with the new information.
Would that save me space - and how would it affect the
performance?

I know I could just try - but any comments are appreciated.

Note that this kind of operation is probably NOT appropriate for
Access! Access does NOT free up space when records are deleted or
overwritten; the database continues to grow, with the deleted or
changed records' space being marked "dead" and left unused, and if the
fields are indexed, changes to the indexes will take up space as well.
The space is only recovered when you Compact the database, as you have
seen. Changing from deleting to updating might slow the bloat a bit,
but not much.

You may want to use an old-fashioned flat-file database for this
purpose, or even write C# or VB code to manage the file size yourself.

John W. Vinson[MVP]

 

[Karen]

can I gradually "free" the space in some way as I go along?

 

[John Vinson]

can I gradually "free" the space in some way as I go along?

No, only by compacting the entire database.

John W. Vinson[MVP]