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?

 

[Tony Toews [MVP]]

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?

I noticed this difference in behavior when moving from A97 to A2000
thus Jet 3.5 to Jet 4.0. I suspect the difference is because when
the database is opened by another app Jet 4.0 is adding each record to
it's own 4 kb page. Whereas in Jet 3.5 records would be added to the
same 2 kb page.

25,000 * 4 kb is 100 Mb so that isn't precisely the answer in your
case though.

Other than this I have no idea.

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/