database bloating

[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.

 

[Alex Dybenko]

Hi,
this is a normal behavior, db size gets reduced only after compact

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

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

 

[Alex Dybenko]

Hi,
look at dbengine.compactdatabase method in help

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

I found
Tools --> Options --> General --> Compact on close
so that, I think, is enough automation for me

 

[Guest]

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.

 

[Douglas J. Steele]

Consider using a temporary database for your temporary tables.

Tony Toews has an example at http://www.granite.ab.ca/access/temptables.htm