Exuberant database file size increase

[John]

Hi

I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from a
remote db. The mdb file size is normally around 80MB but since running the
vb.net app the mdb file size grows to 1GB or so every few days. After
compact and repair the size does get back to 80MB but it is still a pain to
have to compact repair every few days. What is causing this massive file
size increase and is there any way to control it?

Thanks

Regards

[Allen Browne]

The action queries are causing the size increase. Access may not release the
previously used space until you compact the database.

The most obvious alternatives are to ensure the INSERT and UPDATE queries
don't make any unnecessary changes. For example, instead of:
UPDATE MyTable SET IsPicked = False;
there would be fewer updates if you used:
UPDATE MyTable SET Field1 = False WHERE IsPicked = True;

Similarly, if you are relying on a unique index to weed out duplicates when
you run your INSERT, use a WHERE clause so there are fewer attempts.

Other alternatives might include using the command line switch for
msaccess.exe to compact the database. Or you might create an mdb file with
the right structure but no data, and have it available as a template. Your
vb.net code could then copy the file, populate it, delete the old one, and
rename the new one so that it takes its place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.in fovis.co.ukwrot e in message
news:%2******** *******@TK2MSFT NGP04.phx.gbl.. .

>
I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from
a remote db. The mdb file size is normally around 80MB but since running
the vb.net app the mdb file size grows to 1GB or so every few days. After
compact and repair the size does get back to 80MB but it is still a pain
to have to compact repair every few days. What is causing this massive
file size increase and is there any way to control it?

Thanks

Regards

['69 Camaro]

Hi, John.

What is causing this massive file size increase

Most likely it's the many updates. If the new data doesn't fit into the space
currently allotted for the record, Jet has to make room for it. If there isn't
enough room in the current data page, then the current data page is split in
two, and the records distributed to each half accordingly. Now there's room in
one of the data pages for this new data in this record, but instead of one 4 KB
data page (for Jet 4.0), you now have two 4 KB data pages. Even if the data
pages aren't full (and even when there are no records in it), each data page is
4 KB. (Exception: Jet 2.0, 3.0, and 3.5 use 2 KB data pages to store data,
because they don't accommodate Unicode characters, like Jet 4.0 does.)

is there any way to control it?

Compact it more often.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"John" <Jo**@nospam.in fovis.co.ukwrot e in message
news:%2******** *******@TK2MSFT NGP04.phx.gbl.. .

Hi

I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from a
remote db. The mdb file size is normally around 80MB but since running the
vb.net app the mdb file size grows to 1GB or so every few days. After compact
and repair the size does get back to 80MB but it is still a pain to have to
compact repair every few days. What is causing this massive file size increase
and is there any way to control it?

Thanks

Regards

[John]

The mdb file is used by several users during the day. At night I can try to
compact automatically. Is there any way to find out if no other user apart
from me is using the db is using the db so I can compact it safely?

Thanks

Regards

"Allen Browne" <Al*********@Se eSig.Invalidwro te in message
news:45******** **************@ per-qv1-newsreader-01.iinet.net.au ...

The action queries are causing the size increase. Access may not release
the previously used space until you compact the database.

The most obvious alternatives are to ensure the INSERT and UPDATE queries
don't make any unnecessary changes. For example, instead of:
UPDATE MyTable SET IsPicked = False;
there would be fewer updates if you used:
UPDATE MyTable SET Field1 = False WHERE IsPicked = True;

Similarly, if you are relying on a unique index to weed out duplicates
when you run your INSERT, use a WHERE clause so there are fewer attempts.

Other alternatives might include using the command line switch for
msaccess.exe to compact the database. Or you might create an mdb file with
the right structure but no data, and have it available as a template. Your
vb.net code could then copy the file, populate it, delete the old one, and
rename the new one so that it takes its place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <Jo**@nospam.in fovis.co.ukwrot e in message
news:%2******** *******@TK2MSFT NGP04.phx.gbl.. .

>>
I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and
update queries on some table. The data to be inserted and updated is
coming from a remote db. The mdb file size is normally around 80MB but
since running the vb.net app the mdb file size grows to 1GB or so every
few days. After compact and repair the size does get back to 80MB but it
is still a pain to have to compact repair every few days. What is causing
this massive file size increase and is there any way to control it?

Thanks

Regards

['69 Camaro]

Hi, John.

Is there any way to find out if no other user apart from me is using the db is
using the db so I can compact it safely?

The LDB Viewer will show all of the users currently in the database. You may
find a link to the LDB Viewer and other free diagnostic tools in the "Free
Microsoft Access Troubleshooting Tools" section on this Web page:

http://www.Access.QBuilt.com/html/links.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAMwrote in
message news:Op******** ******@TK2MSFTN GP03.phx.gbl...

Hi, John.

>What is causing this massive file size increase

Most likely it's the many updates. If the new data doesn't fit into the space
currently allotted for the record, Jet has to make room for it. If there
isn't enough room in the current data page, then the current data page is
split in two, and the records distributed to each half accordingly. Now
there's room in one of the data pages for this new data in this record, but
instead of one 4 KB data page (for Jet 4.0), you now have two 4 KB data pages.
Even if the data pages aren't full (and even when there are no records in it),
each data page is 4 KB. (Exception: Jet 2.0, 3.0, and 3.5 use 2 KB data
pages to store data, because they don't accommodate Unicode characters, like
Jet 4.0 does.)

>is there any way to control it?

Compact it more often.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info.
"John" <Jo**@nospam.in fovis.co.ukwrot e in message
news:%2******** *******@TK2MSFT NGP04.phx.gbl.. .

>Hi

I have a typical front end/back end access app. Additionally a vb.net app
accesses the backend mdb file every few minutes and runs insert and update
queries on some table. The data to be inserted and updated is coming from a
remote db. The mdb file size is normally around 80MB but since running the
vb.net app the mdb file size grows to 1GB or so every few days. After compact
and repair the size does get back to 80MB but it is still a pain to have to
compact repair every few days. What is causing this massive file size
increase and is there any way to control it?

Thanks

Regards

[Lyle Fairfield]

John wrote:

The mdb file is used by several users during the day. At night I can try to
compact automatically. Is there any way to find out if no other user apart
from me is using the db is using the db so I can compact it safely?

I have used the code at

http://www.ffdba.com/downloads/Compa...ked_Tables.htm

successfully in the somehat distant past.

I believe some may have reported that it did not operate successfully
for them. The function CanBeOpenedExcl usively may be helpful in
ascertaining whether or not the compact can be done.

If this were my db I would want to examine the .Net procedures that
added and modified data. The bloat you describe may be related to how
they do their job.