size of database

[john_liu]

I use a MS ACCESS database with the size of 250MB. The dababase consists of
60 tables and no other objects. The objects in this database can not be
changed. I wanted to reduce the size of database and deleted about 50% of
records in all 60 tables but the size of this database remains 250MB. Could
someone tell me why and how to reduce it.

Thanks

[Allen Browne]

Try:
Tools | Database Utilities | Compact

--
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_liu" <jo******@charter.net> wrote in message
news:lD*******************@fe06.lga...

I use a MS ACCESS database with the size of 250MB. The dababase consists of
60 tables and no other objects. The objects in this database can not be
changed. I wanted to reduce the size of database and deleted about 50% of
records in all 60 tables but the size of this database remains 250MB.
Could someone tell me why and how to reduce it.

[Tim Marshall]

john_liu wrote:

I use a MS ACCESS database with the size of 250MB. The dababase consists of
60 tables and no other objects. The objects in this database can not be
changed. I wanted to reduce the size of database and deleted about 50% of
records in all 60 tables but the size of this database remains 250MB. Could
someone tell me why and how to reduce it.

If you're on Access 2000 or greater:

Got to tools->database utilities->compact and repair database

The access database engine, MS Jet, does not surrender climed space when
records are deleted. The above process is required. Even without
deleting any records, the above process should reduce the size as the
database tends to bloat a bit depending on the activities performed.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Salad]

Tim Marshall wrote:

john_liu wrote:

I use a MS ACCESS database with the size of 250MB. The dababase
consists of 60 tables and no other objects. The objects in this
database can not be changed. I wanted to reduce the size of database
and deleted about 50% of records in all 60 tables but the size of
this database remains 250MB. Could someone tell me why and how to
reduce it.

If you're on Access 2000 or greater:

Got to tools->database utilities->compact and repair database

The access database engine, MS Jet, does not surrender climed space when
records are deleted. The above process is required. Even without
deleting any records, the above process should reduce the size as the
database tends to bloat a bit depending on the activities performed.

Are deleted records the only source of bloat? Does the running of
queries add to bloat too? Maybe creating temp queries, running, then
deleting?

[deko]

> I use a MS ACCESS database with the size of 250MB. The dababase consists
of

60 tables and no other objects. The objects in this database can not be
changed. I wanted to reduce the size of database and deleted about 50% of
records in all 60 tables but the size of this database remains 250MB. Could someone tell me why and how to reduce it.

Is 250MB all that bad? Are you experiencing performance problems? How is
the size a problem?

[Tim Marshall]

Salad wrote:

Are deleted records the only source of bloat? Does the running of
queries add to bloat too?
I don't think queries do...or at least not significantly compared to
form development, say. Most of my work is gainst Oracle, though I've
been doing a hobby project in Jet and I find my back end does not bloat
at all like the front end. Bloat in A2003 is ridiculous compared to
what I was used to in A97 and the decompile really, really is nearly
compulsory.
Maybe creating temp queries, running, then
deleting?

I know this does for certain, when you actually append a querydef to the
querydef collection. The Oracle projects I was doing a few years ago in
A97 featured pass-through queries I would delete after a session and
those apps do bloat a fair bit.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[GoJo4]

"Salad" <oi*@vinegar.com> wrote in message
news:UP****************@newsread3.news.pas.earthli nk.net...

Tim Marshall wrote:

john_liu wrote:

I use a MS ACCESS database with the size of 250MB. The dababase consists
of 60 tables and no other objects. The objects in this database can not
be changed. I wanted to reduce the size of database and deleted about
50% of records in all 60 tables but the size of this database remains
250MB. Could someone tell me why and how to reduce it.

If you're on Access 2000 or greater:

Got to tools->database utilities->compact and repair database

The access database engine, MS Jet, does not surrender climed space when
records are deleted. The above process is required. Even without
deleting any records, the above process should reduce the size as the
database tends to bloat a bit depending on the activities performed.

Are deleted records the only source of bloat? Does the running of queries
add to bloat too? Maybe creating temp queries, running, then deleting?

Dirty little secret: Nothing can stop Access bloat! You can read all the
MS literature on the problem and try their all their suggested fixes but
nothing will stop the bloat. And every version of Access bloats. The only
thing that changes with each version is the excuse for the bloat.

[John Mishefske]

GoJo4 wrote:

Dirty little secret: Nothing can stop Access bloat! You can read all the
MS literature on the problem and try their all their suggested fixes but
nothing will stop the bloat. And every version of Access bloats. The only
thing that changes with each version is the excuse for the bloat.

Its not quite that bad. There are just certain activities that increase the file size at a
quicker pace. Importing data, creating temporary tables or queries, etc. And sometimes bad
programming practices (not closing recordsets for example).

The Compact utility can help manage this by reclaiming the disk space of deleted objects
and data. There are techniques to minimize the problem (don't store images in a .mdb file,
use temp tables in a temp database, etc.)

Of course if this is a persistent problem then perhaps a more sophisticated DBMS is needed
like Oracle or DB/2.

--
'---------------
'John Mishefske
'---------------

[deko]

> Its not quite that bad. There are just certain activities that increase
the file size at a

quicker pace. Importing data, creating temporary tables or queries, etc. And sometimes bad programming practices (not closing recordsets for example).

A bit off topic, but in regard to closing DAO recordsets - is it sufficient
to do this:

Set rst = Nothing

or do we need to explicitly close the rst first like this:

rst.Close
Set rst = Nothing

I remember reading somewhere that the first way will close the rst as the
object is destroyed, but I'd be interested in other folk's thoughts about
this.

[Larry Linson]

"deko" <de**@deko.com> wrote

A bit off topic, but in regard to
closing DAO recordsets - is it
sufficient to do this:

Set rst = Nothing

or do we need to explicitly close the rst first like this:

rst.Close
Set rst = Nothing

Setting to Nothing is supposed to be automatically done when the object goes
out of scope, but hasn't always worked well, smoothly, and/or consistently.
Since it is "for safety's sake", I don't rely on what is "supposed to happen
if all goes well" but use both the Close and Set to Nothing.

And, yes, as a matter of fact, I do wear both a belt and suspenders. <GRIN>

Larry Linson
Microsoft Access MVP

[deko]

> > A bit off topic, but in regard to

> closing DAO recordsets - is it
> sufficient to do this:
>
> Set rst = Nothing
>
> or do we need to explicitly close the rst first like this:
>
> rst.Close
> Set rst = Nothing
Setting to Nothing is supposed to be automatically done when the object

goes out of scope, but hasn't always worked well, smoothly, and/or consistently. Since it is "for safety's sake", I don't rely on what is "supposed to happen if all goes well" but use both the Close and Set to Nothing.

And, yes, as a matter of fact, I do wear both a belt and suspenders.

<GRIN>

10-4.