My Access database gets bigger each time I open it

[Tired and emotional]

Each time I use my Access 2007 application it increases in size whether or
not I add data. I have to use "Compact and Repair" each time to keep its size
down.
This happened with the previous Access 2003 version and also with the
Runtime version I've just created.
How do I stop this happening?

 

[Arvin Meyer [MVP]]

You don't stop it. Every time that the database opens after a compact, it
recompiles the code and queries as they are used. This causes increases in
the database size, as does adding data. Unless you are storing large binary
objects, or doing design work, the size should not increase very much.

 

[Pat Hartman]

Are you creating and destroying temp tables? Are you creating SQL strings
in code rather than using QueryDefs?

 

[Tired and emotional]

Thanks. That seems to be pointing in the right direction, as the more forms
and reports I open, the bigger it gets on closing. I've just done a series of
half-a-dozen openings, and it's already up by 15% in an overall size of 18mb.
As you've suggested, it does get more drastic after design work, particuarly

 

[Tired and emotional]

Hi
Thanks. See my reply above. I'm mostly using the interface (very little VB),
so if I understand you correctly I'm using QueryDefs

 

[Arvin Meyer [MVP]]

You can use Tools >>> Options and select Compact on Close on the General
tab.

 

[Jonathan Brown]

Does using SQL strings in code cause the database to bloat more than usual?
I almost always use SQL strings in code but now I'm wondering if I should
stop.

 

[Tired and emotional]

Brilliant - thanks.

For reference, for anyone else using Access 2007, it's accessible(!) via the
"Office" button. Click the "Access Options" button at the bottom of the frame
and select "Current database" from the options list on the left.

 

[Tom Wickerath]

I use SQL strings in code quite frequently, especially for QBF (Query by
Form) forms. I've never noticed any particular problem with DB
bloat--certainly not to the extent that "tired and emotional" reports, ie.
"up by 15% in an overall size of 18mb."

In code, make sure to close and destroy recordsets that you open. Here is a
tinyurl link to several bloat-related KB articles:

http://tinyurl.com/2dmpw


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Tired,

I've just done a series of half-a-dozen openings, and it's already up
by 15% in an overall size of 18mb.

This sounds like way too much bloat to me. Try looking at some of these
Microsoft KB articles, to see if any of them help you with this issue:

http://tinyurl.com/2dmpw

I know you indicated to Pat that you are using very little VBA code, but
it's worth checking to see if you have code that opens DAO recordset
variables, but you are not closing the recordsets and setting the objects to
nothing.

Are you doing a lot of importing of text files? If so, try removing the
record level locking option, if it is set.

Are you using temporary tables in your database or are you using Make Table
queries and then later deleting these table objects? If so, consider using
linked tables instead. Here is a ready-made example for you:

http://home.comcast.net/~tutorme2/samples/tmpwrkdb.zip

Here is an article that was written for Access 97, but it is still
applicable for JET 4 databases. The author removed it from their web site
some time ago, but the Wayback Machine still has a copy:

WORKING WITH LARGE PROGRAM DATABASES IN ACCESS 97, by Cary Prague
http://web.archive.org/web/20030204023622/http:/www.databasecreations.com/largedb.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John W. Vinson]

and it's already up by 15% in an overall size of 18mb.

Just FWIW, 18Mb is a TINY database. You're limited to 2048Mb, and 100-200 Mb
is not at all unusual. I think you're agonizing over trivia.

John W. Vinson [MVP]

 

[Jonathan Brown]

That's good to know, thank you.

I use SQL strings in code quite frequently, especially for QBF (Query by
Form) forms. I've never noticed any particular problem with DB
bloat--certainly not to the extent that "tired and emotional" reports, ie.
"up by 15% in an overall size of 18mb."

In code, make sure to close and destroy recordsets that you open. Here is a
tinyurl link to several bloat-related KB articles:

http://tinyurl.com/2dmpw


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Arvin Meyer [MVP]]

Does using SQL strings in code cause the database to bloat more than
usual?
I almost always use SQL strings in code but now I'm wondering if I should
stop.

I don't consider that to be very much bloat at all. Any query whether an SQL
string, or a saved querydef is decompiled each time the database is
compacted. SQL strings result in temporarily saved queries. Typically, you
can find the temp queries by using something like:

SELECT Name
FROM MSysObjects
WHERE Left([Name],4)="~sq_";

These usually disappear when the database is compacted, and reappear as they
are used. The bloat is only a tiny bit more than with saved queries

 

[Linq Adams via AccessMonster.com]

As has been indicated, you're not really talking about a big database here.
It should be noted that while Microsoft touts compacting as a method of
preventing corruption, the act of compacting itself can actually cause
corruption! Most texts/experts addressing the issue will tell you to avoid
the "Compact on Exit" option and to **always** backup your database before
compacting. Also note that compacting an already corrupt database can
actually make the chances of data recovery go down.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Pat Hartman]

Yes, embedded SQL makes the database bloat but if you compact on close, you
probably don't see it since the bloat doesn't get to accumulate. However as
Linq mentioned, compact on close is one of those features best left turned
off so if you let the database go for a week or two, you will see some bloat
depending on how much use the embedded queries get.

It is important to compact both the FE and BE on a regular basis to keep the
table stats up to date so that the query plans are as efficient as they can
be. The faster your database grows, the more frequently you should compact.
As Arvin mentioned, when you compact the database, all your code and
querydefs are decompiled and will be recompiled the next time they are used.

 

[Access novice]

Thanks Arvin. I had the same problem. You've solved it.

 

[Arvin Meyer [MVP]]

I might point out that it's always a good idea to back up your database
before compacting, so Compact on Close is not a good long term solution,
because you should never copy an open file.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com