PC Review


Reply
Thread Tools Rate Thread

Database Bloat

 
 
KW
Guest
Posts: n/a
 
      15th Aug 2008
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?
 
Reply With Quote
 
 
 
 
Michael Conroy
Guest
Posts: n/a
 
      15th Aug 2008
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.
--
Michael Conroy
Stamford, CT


"KW" wrote:

> 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?

 
Reply With Quote
 
Tony Toews [MVP]
Guest
Posts: n/a
 
      17th Aug 2008
KW <(E-Mail Removed)> wrote:

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/
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database bloat Kevin B Microsoft Access VBA Modules 2 13th Feb 2008 10:02 PM
Database bloat =?Utf-8?B?RGFuSg==?= Microsoft Access VBA Modules 0 5th Nov 2004 03:20 AM
Database bloat =?Utf-8?B?RGFuSg==?= Microsoft Access VBA Modules 0 5th Nov 2004 03:19 AM
Database Bloat Tom Microsoft Access Form Coding 3 26th Jul 2003 03:10 PM
Database Bloat Tom Microsoft Access Getting Started 3 26th Jul 2003 03:10 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:33 AM.