database bloating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I'm just starting to learn how to program Access 2003 with VBA, and I
discovered that my database is bloating for some reason, I hope that someone
could help me to figure this out...

I'm importing about 40000 rows of data to Access from a server. I put the
data into a table, and everything goes fine. But when I want to delete the
data from the table (I need to do it because I need to test the code), I
succeed, but the size of the database stays the same. So my database is empty
and huge. I close all the recordsets and set them to nothing, so that
shouldn't be the problem?

To delete data from the database I use an SQL command:

Sub Delete_data ()
Dim strSql As String
strSql = "DELETE * FROM Table" 'my table isn't called Table :)
DoCmd.RunSQL (strSql)
End Sub

When I use the "Compact and repair database" -tool that Access has, the size
of the database drops to normal.
 
Ok, thanks! Now I don't have to worry about that so much.. :)
Do you happen to know is there a way to compact the database in code?

BR, Beginner
 
I found
Tools --> Options --> General --> Compact on close
so that, I think, is enough automation for me :)
 
Is there any way to reduce the size while the db is open? I have a program
which runs a few thousand update and select queries on a DB which starts out
at less than a MB. Each query run seems to add a small amount to the
database size. Pretty soon I hit the 2GB mark before the program can
complete. So somehow I need to clear out whatever temporary tables the
database is building periodically, so that I can get the program to finish,
and obviously I can't close the DB before the program finishes to run a
"compact" script.
 
Back
Top