Database frequently doubles in size - is this a problem?

[David Anderson]

I am in the process of developing an Access 2000 front end database, and
today the database size has started to rapidly double in size after even
minor code changes (it grows from 2.3MB to over 4MB). A forced power down
was required on one occasion this afternoon after my PC totally froze while
editing my database, but so far, I'm not getting any error messages. A
Compact & Repair always gets the size back to normal, but I find this change
in behaviour rather worrying (it never used to grow like this a day or so
ago).

For better or worse, I have been running C&R every 10 mins or so to keep
this growth under control. Am I right to worry about this? Are there any
well-recognised bad programming habits that can cause this problem?

David

 

[Guest]

It can do that if you are doing a bunch of Make Table Queries for example.
If you are, it's better to just use a delete query to delete the data and
then an Append query to add the new records. Also, if you have a Make Table
query you can, except in certain circumstances, just use the same query (but
as SELECT QUERY) as the base for other queries instead of having to make
tables.

 

[Tony Toews [MVP]]

I am in the process of developing an Access 2000 front end database, and
today the database size has started to rapidly double in size after even
minor code changes (it grows from 2.3MB to over 4MB).

Nah, don't worry about it. That's quite typical. I would make a
copy every hour or two.

I had one database that within a day would bloat from 17 Mb to 39 Mb.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

It can do that if you are doing a bunch of Make Table Queries for example.
If you are, it's better to just use a delete query to delete the data and
then an Append query to add the new records. Also, if you have a Make Table
query you can, except in certain circumstances, just use the same query (but
as SELECT QUERY) as the base for other queries instead of having to make
tables.

Actually if that is contributing that much to bloating then I'd
suggest visiting the TempTables.MDB page at my website which
illustrates how to use a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David Anderson]

I'm not running any make-table queries on a regular basis so perhaps my
database bloat is no more than normal Access behaviour, as you suggest. I
will stop worrying about it.

When you say "make a copy every hour or two", do you really mean that your
normal practice during development is to retain multiple separate copies
saved at 2-hourly intervals rather than simply overwriting a single backup
copy every 2 hours?

David

 

[Tony Toews [MVP]]

I'm not running any make-table queries on a regular basis so perhaps my
database bloat is no more than normal Access behaviour, as you suggest. I
will stop worrying about it.

Right, you'll find after a while that it will stop bloating as much.

When you say "make a copy every hour or two", do you really mean that your
normal practice during development is to retain multiple separate copies
saved at 2-hourly intervals rather than simply overwriting a single backup
copy every 2 hours?

Yes, because I'm a pessimist. Also within Windows Explorer it's
really easy to click on the MDB, Ctl+C and Ctl+V and it makes a copy
of the file titled Copy of <insert your MDB name here>.mdb. Then
Copy 2 etc, etc. Real easy.

At the end of the day or when I accumulate 5 or 10 I zip them up using
Winzip into a zip file titled <insert your MDB name here> yyyy mm
dd.zip. Then I move that zip to an archive folder and delete those
zip files a month or two later.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David Anderson]

Tony,
I'm also a bit paranoid about backup - except for the times when I get too
tied up with what I'm doing and forget to do it......

I like your idea of using the Windows Explorer copy facility. That really is
a very simple solution - and it is a useful addition to a conventional daily
backup process. Thanks for that.

David

 

[David W. Fenton]

I like your idea of using the Windows Explorer copy facility. That
really is a very simple solution - and it is a useful addition to
a conventional daily backup process. Thanks for that.

I run an UNDELETE program (Executive Undelete) so that every time I
compact, I have a recoverable copy of the previous version. It's
saved my bacon many times, and it's one of those
set-it-and-forget-it things so I don't have to be perfect to get the
advantage of it.

 

[Tony Toews [MVP]]

I'm also a bit paranoid about backup - except for the times when I get too
tied up with what I'm doing and forget to do it......

I like your idea of using the Windows Explorer copy facility. That really is
a very simple solution - and it is a useful addition to a conventional daily
backup process. Thanks for that.

You're quite welcome. And thanks for asking. I've now blogged that.
There's all these things I do without thinking that I need some
prodding to think about and let other folks learn from my experience.
And my mistakes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/