Reducing Database Size

[Chris Wood]

Hi,

I am trying to reduce the size of my database in Access.
I changed a field of data type number, field size double
to field size single. Instead of making the database
smaller, it made the database bigger. In fact, everytime
I make a change to field size, whether to a bigger or
smaller field size, the database itself becomes bigger. I
even tried creating a new field of smaller field size,
copying the data to this new field and deleting the old
larger field size, but the database just keeps on
growing. Can someone explain why the database keeps
getting bigger every time a field size change is done, and
how to decrease database size by reducing field size?

Thanks,

Chris

 

[deko]

-----Original Message-----
Hi,

I am trying to reduce the size of my database in Access.
I changed a field of data type number, field size double
to field size single. Instead of making the database
smaller, it made the database bigger. In fact, everytime
I make a change to field size, whether to a bigger or
smaller field size, the database itself becomes bigger. I
even tried creating a new field of smaller field size,
copying the data to this new field and deleting the old
larger field size, but the database just keeps on
growing. Can someone explain why the database keeps
getting bigger every time a field size change is done, and
how to decrease database size by reducing field size?

Thanks,

Chris
.

First, do a repair and compact, which I assume you've
already tried. Next, do a full import. this works great
for me - reduces the size of my mdb by over half. I use
Access 2003. From what I understand, the VBA code piles
up (or someting) ane that's why after making changes the
mdb is bigger. Here's what I do: 1. reboot. 2. create a
blank mdb, with no start up options. 3. close the blank
mdb (and Access). 4. reopen the blank mdb, and go to Filetables, etc. from the orig mdb. 5. close the new mdb. 6.
open the new mdb, run compact and repair.

 

[Duane Hookom]

Reducing the field size won't significantly affect the mdb size. You should
backup before and compact after changing table structures in the database.
Deletions and other changes in the mdb are not removed from the file until
you compact.

 

[TC]

As Duane said, it's a complete waste of time trying to reduce your db size
by changing number field types.

What is the current size of your database, and why do you need to reduce it?

TC

 

[Jeff Boyce]

Chris

TC's questions are "on point"! Why are you trying to "reduce the size"? If
you have so much data that Access cannot efficiently handle it, you need to
consider a more robust back-end. If you are concerned about performance,
you can look to indexing and modifying your queries to possibly make gains.

But if your table structure was defined and designed well to start with, why
would your data be "willing" to be changed?! If the original need was for
numeric/double, won't changing to numeric/single mess everything else up
that depends on that field?

Good luck

Jeff Boyce
<Access MVP>