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

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" <john_liu@charter.net> wrote in message
news:lDOpe.17966$QX1.16445@fe06.lga...[color=blue]
>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.[/color]

john_liu wrote:
[color=blue]
> 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.[/color]

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

Tim Marshall wrote:[color=blue]
> john_liu wrote:
>[color=green]
>> 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.[/color]
>
>
> 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.[/color]

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

> I use a MS ACCESS database with the size of 250MB. The dababase consists
of[color=blue]
> 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.[/color]
Could[color=blue]
> someone tell me why and how to reduce it.[/color]

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

Salad wrote:
[color=blue]
> Are deleted records the only source of bloat? Does the running of
> queries add to bloat too?[/color]

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.
[color=blue]
> Maybe creating temp queries, running, then
> deleting?[/color]

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

"Salad" <oil@vinegar.com> wrote in message
news:UPSpe.1367$hK3.964@newsread3.news.pas.earthli nk.net...[color=blue]
> Tim Marshall wrote:[color=green]
>> john_liu wrote:
>>[color=darkred]
>>> 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.[/color]
>>
>>
>> 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.[/color]
>
> Are deleted records the only source of bloat? Does the running of queries
> add to bloat too? Maybe creating temp queries, running, then deleting?[/color]


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.

GoJo4 wrote:[color=blue]
> 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.[/color]

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

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

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.

"deko" <deko@deko.com> wrote
[color=blue]
> 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[/color]

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

> > A bit off topic, but in regard to[color=blue][color=green]
> > 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[/color]
>
> Setting to Nothing is supposed to be automatically done when the object[/color]
goes[color=blue]
> out of scope, but hasn't always worked well, smoothly, and/or[/color]
consistently.[color=blue]
> Since it is "for safety's sake", I don't rely on what is "supposed to[/color]
happen[color=blue]
> 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.[/color]
<GRIN>

10-4.