Database Keep Growing!!

[wolfen_76]

I have a problem with my access database.. it keep growing bigger & bigger althought i delete all the record in it.. is it common ? how can i overcome this problem? do i need to refresh my database? how is can that be done? i'm using vb6.0 sp3.. please help as i have a 10 mb database with no record in it..

[Lothar Haensler]

apply the CompactDatabase method to your database.
It's a method of the workspace object in DAO.

how's that?

[PedroD]

You can do two things:

1. Use the Tools -> Database Utilities -> Compact Database
2. (better) Start up Access with the "/Decompile" option set. This will remove
stray code snippets effectively.

The reason of MS Access' growth, is that it uses its own file as temprary storage, for which (1) helps to remove. You must do this from time to time, as
this just keeps growing bigger and bigger. Another space-quirk, is stray code. For example, if you've written a lot of code in a form, and you rename that form, the code will be "lost", but not deleted, in which case (2) helps.

NOTE: Always remember to create a backup of your access files before attempting to compact / decompile. I've never had any problems with doing so, but it's always better to be safe than sorry...

P.Dias
Oslo, Norway

[hank]

Unfortunately, as you probably know by now, compactdatabase doesn't do in VB what it does in Access. The files are simply marked as deleted and left there.

What about this? I am experimenting with the same problem myself: I am trying this. Do you think this does it? SORT of looks that way.

I am told that the VB5 COMPACTDATABASE function will not actually reduce a database size and that deleted records in a table are still there even though deleted AND compacted.

I am writing a "reminder" program that will need pruning from time to time. I have a field called "permanent" (clever huh?) that has a Y or N in it. From time to time one should look at the full table and delete (mark with N) any past reminders. e.g. any January reminders. Does anyone have any comments on the following. I am not sure it works, but it seems to.

I use the dbexecute command to do the following:

vbcode

Dim dbs As Database
Dim qdf As QueryDef
Dim whatdir as string

' Modify this line to include the path to Reminder
' on your computer.
whatdir = app.path
Set dbs = OpenDatabase(whatdir & "\Reminder.mdb")

' Select all records in the Reminder table
' and copy them into a new table, Emp Backup.
dbs.Execute "Step1DelFrsh"
' clears out all records in Freshtemp table
dbs.Execute "Step2AppFrsh"
' appends all records with a Y in the "permanent" field to Freshtemp table

dbs.Execute "Step3DelRem"
' clears out all records in the Reminder table

dbs.Execute "Step4AppRem"
' appends from FreshTemp table for Y in "permanent" field to Reminder table

dbs.Execute "Step1DelFrsh"
' clear out Freshtemp again .. just to be sure

dbs.Close
' close any open database .. just to be sure
Beep
/vbcode

Sorry for the lateness of this reply. I just found this when I was looking for a solution.

[Scott123]

access creates "ghost space" which can only be removed by compacting the database. This can be minimized by not using make table queries. One thing to keep in mind is that the db must be closed to compact it.

Pardon my ignorance. But how will I know if i'm using ADO or DAO? What are the similarities/differences?