Database Bloat

[KW]

I have two separate applications that open the same database. This is
causing database bloat when inserting records. I created the following
program to run some tests. I will just put in the basics:

Dim adoconn As ADODB.Connection
ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" &
App.Path & "\db.mdb;Uid=Admin;Pwd="
Set adoconn = New Connection
adoconn.ConnectionString = ConnectionString
adoconn.Open

For index = 1 to 25000
<<BUILD SQL INSERT STATEMENT AND ASSIGN TO TMR_SQL>>
Dim TMR_rs As ADODB.Recordset
Set TMR_rs = CreateObject("adodb.recordset")
TMR_rs.CursorLocation = adUseClient
TMR_rs.Open TMR_SQL, adoconn, adOpenDynamic
If TMR_rs.State = 1 Then TMR_rs.Close
Set TMR_rs = Nothing
Next


The above code inserts 25000 records into a table. If I run the above code
when the database is closed the database only increases about 8MB in size and
performing a compact does not shrink the size. However, if I run my
application that opens the databse prior to running the above code the
database increases 65MB. Can anyone tell me why having the database open
prior to running the code causes the database size to increase so much?

 

[Michael Conroy]

KW,
I don't know why Access does this, I just know it is normal. I opened a 20
meg mdb early in the morning and worked on it all day. By that I mean making
queries and reports and adding code to handle things. I was importing some
stuff from Excel, but it was a new version of the same spreadsheet, that is,
it was about the same number of records, just updated information. So I was
increasing the size of a 10,000 record table by maybe one hundred records.
Occasionally I even experienced a situation where simple code would not
execute properly. By the end of the day the file had balooned to 80 meg. It
is now standard practice for me to compact before lunch and before I shutdown
for the day. And sometimes I even close out of Access altogether and restart
it for a clean slate. If I had to guess I would say Access is caching all the
activities performed on the database until it is compacted. When the database
is closed, caching activities are not possible, when open, mandatory. But
that is just a guess.

 

[Tony Toews [MVP]]

Answered in the microsoft.public.access newsgroup.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/