Database Size

[Erika]

I have a database that has jumped in size drastically - properties show over
200 mg - did a compact and repair and it got it down to 74 meg but that still
seems excessive. Any ideas on how to correct this issue or what might have
caused it?

 

[Daniel Pineault]

Why do you say this is excessive?
What is being stored in your database (text entries only, images, other)?
What version of Access are you working with?
Is your db split? If so, what is growing the front-end or the back-end?

Below is a good link regarding database bloating:
http://www.granite.ab.ca/access/bloatfe.htm
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[PvdG42]

I have a database that has jumped in size drastically - properties show
over
200 mg - did a compact and repair and it got it down to 74 meg but that
still
seems excessive. Any ideas on how to correct this issue or what might
have
caused it?

Please provide some details about your database. Any design changes
recently? Information such as number of tables, # of rows in each, fields
and field datatypes will allow responders to offer you intelligent advice
and opinions. Without more information, anything you'll get will be a WAG.

 

[Jerry Whittle]

Don't worry. Be Happy.

An Access database file can be up to 2 GB in size. Access doesn't act like
a balloon when you remove records. It keeps its size. It's more like a paper
bag that needs refolding. That's what compact and repair does in a sense.

There are many things that can cause bloat. Access needs some internal space
to do things like sorting records in queries and reports. This is normal. If
you run your database for a while, then the size may well creep back up.

Don't do Compact on Close. If the database needs the internal space to run,
you're just fighting a losing battle and there is always the chance that
something can go badly wrong when doing the compacting.

Now there are some things that can be prevented. If your app is using
temporary tables to move data around, this can cause bloat especially if you
are creating and deleting tables. If your database isn't properly normalized
and you need very complex queries and/or queries based on other queries, this
can also cause bloat.

Storing graphics inside Access will fill it up fast although Access 2007 is
much better about this.

Do a manual compact and repair about once a month. Keep an eye on things to
make sure it doesn't start bloating above 1.5 GB. Otherwise see the first
line of my post.

 

[Jeff Boyce]

Erica

In addition to the other excellent advice you've received elsethread, I've
sometimes noticed that compact & repair doesn't clear out all of the unused
space.

If, after considering the other points folks have made, you still feel that
your db is too large, you could:

1. create a new empty db.
2. import all the objects from the 'too-big' one.
3. set all the startup/other conditions/settings.
4. do a re-compile.
5. backup, then compact/repair the new one. Is it smaller?

This is a common sequence when there's something subtle corrupting a db.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Albert D. Kallal]

I have a database that has jumped in size drastically - properties show
over
200 mg - did a compact and repair and it got it down to 74 meg but that
still
seems excessive. Any ideas on how to correct this issue or what might
have
caused it?

The "might have caused this" is normal, especially if you are desigining
forms etc. The compact + repair is thus going to be used quite often during
the day if you modifying and working on the application as opposed to just
"using" the application to enter data.

Another real source of bloat is using background images in your forms.

I have a application with 160 forms, about 120 reports, and more then
30,000+ lines of VBA code. This application also has 55 5 highly related
tables.

Without data (it is split), the whole application is less then 10 megs in
size.

 

[a a r o n . k e m p f]

Access just isn't as efficient as storage as SQL Server is.

with 'auto_shrink' defaulting to on for SQL Server Express-- it's just
pointless to use Jet for anything

Access Data Projects would mean that you'd never have to worry about
this!

 

[Albert D. Kallal]

message

Access just isn't as efficient as storage as SQL Server is.

with 'auto_shrink' defaulting to on for SQL Server Express-- it's just
pointless to use Jet for anything

Access Data Projects would mean that you'd never have to worry about
this!

Yes, you most certainly would have to worry about this. If you bothered to
read my post, I was talking about the application. That application has no
data in it (in fact, it is an application that uses sql server).

Using sql server does not change nor effect this bloating problem of
graphics on forms, or the need to frequently compact your application during
development.

In my example, I am using sql server..and it changes ZERO, I repeat ZERO of
what was stated....

 

[David W. Fenton]

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote in
m:

[nothing worth reading]

Just get out of jail, Aaron? Or did your parole come to an end?

 

[a a r o n . k e m p f]

are you kidding me?

how is it that ADP doesn't solve the bloating problem out of the box?

 

[a a r o n . k e m p f]

David;

I was never in jail, never convicted, never on parole.

nice try.

Are you still playing with baby-sized databases that blow up when they
hit 10mb?

And insisting that people still use Access 97?

And insisting that replication is available in Access 2007 (format)?

-Aaron

 

[Tony Toews [MVP]]

I was never in jail, never convicted, never on parole.

http://www.courts.wa.gov/index.cfm Search Case Records >> Name
Search .>> Accept >> key in someone's name.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[a a r o n . k e m p f]

legally, it never happened.

You and your faggot friends are the real stalkers-- following me
around, writing letters to me employers and clients-- just because I
swear on the internet?

You're just a sick old lazy dipshit dude you can screw yourself

 

[De Jager]

message
Access just isn't as efficient as storage as SQL Server is.

with 'auto_shrink' defaulting to on for SQL Server Express-- it's just
pointless to use Jet for anything

Access Data Projects would mean that you'd never have to worry about
this!