Access 2003 database too large

[FL]

I've removed more that 1/2 of records yet the database size did not
significantly reduce in size. Database also compacted. Database is a
backend with security. Current size is 1.5G Database was 2000 orig. now
converted to 2003. Any suggestions on how to reduce the size?

 

[Maurice]

Simple answer would be to remove even more records. But if you have removed
1/2 of the records and still there remains 1.5G how big was it then before
you moved the records?

Check the following pointers:

See if any temptables are being used and if so if they are being emptied
when they are not used. If you do compact try it twice in a row often that
will help a little. Lastly try the following (if possible) create an empty db
and export every bit of data to that new clean db. When imported compact the
database and see if it is also 1.5g in size. If so try to analyse the data
and think about the usage of the data. Is every bit of data being used? Can
some data be transported to an archive db. And finally if the size doesn't
decrease think about upsizing your backend to SQL-Server.

 

[Jeff Boyce]

If I store [PersonName] once for each time that person buys a product, any
product, my tables and database will grow too big.

If I store [ProductName] once for each time that product is purchased, my
tables and database will grow too big.

You haven't described the data in your database.

Perhaps there are ways to store the information you need without storing all
the data you've been storing.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[FL]

Jeff,
Yes, I do store repeat information but it's required to which product type
is being presented in each record. This is a database that records daily
test data for various products. How can I avoid the selection of
[ProductName] for each record stored? Currently I have a table with all
Product Names and use a drop down box for user to select product for each
test record recorded. I also use other fields such as [UserName] that are
also documented with each record and the same ID could be used on many
records. I think my biggest problem is with the storage of a graph image
copied from Excel to some records. But, not sure. I have many other
fields that store numbers and others with text. A few date fields too.
There are also calculations performed on some forms to come up with numberic
results.

I haven't tried the other suggestion from Maurice yet. How do I use SQL
for my BE?
Thanks, FL
--
FL

If I store [PersonName] once for each time that person buys a product, any
product, my tables and database will grow too big.

If I store [ProductName] once for each time that product is purchased, my
tables and database will grow too big.

You haven't described the data in your database.

Perhaps there are ways to store the information you need without storing all
the data you've been storing.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've removed more that 1/2 of records yet the database size did not
significantly reduce in size. Database also compacted. Database is a
backend with security. Current size is 1.5G Database was 2000 orig.
now
converted to 2003. Any suggestions on how to reduce the size?

 

[Jeff Boyce]

No, it isn't "required". If your table is storing repeat information, you
have a spreadsheet, not a relational database.

For example, if you are repeating [UserName] (rather than [UserID]) in
multiple records, your data is subject to serious threats of integrity.

I don't recall you mentioning that you were storing images ... in earlier
versions of Access, storing images leads to runaway "bloat", perhaps the
symptom you described.

As an alternate approach, folks will store a "pointer" (i.e., the
filepath/name) to the image, and use that to pull up the image as needed.
In Access 2007, "bloat" has apparently not been as much an issue.

Before you decide to migrate your data to a SQL-Server, consider revisiting
your data's normalization. If your data design has not been
well-normalized, stuffing the data in ANY relational database won't make it
a better-performing pile of data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,
Yes, I do store repeat information but it's required to which product type
is being presented in each record. This is a database that records daily
test data for various products. How can I avoid the selection of
[ProductName] for each record stored? Currently I have a table with all
Product Names and use a drop down box for user to select product for each
test record recorded. I also use other fields such as [UserName] that
are
also documented with each record and the same ID could be used on many
records. I think my biggest problem is with the storage of a graph image
copied from Excel to some records. But, not sure. I have many other
fields that store numbers and others with text. A few date fields too.
There are also calculations performed on some forms to come up with
numberic
results.

I haven't tried the other suggestion from Maurice yet. How do I use SQL
for my BE?
Thanks, FL
--
FL

If I store [PersonName] once for each time that person buys a product,
any
product, my tables and database will grow too big.

If I store [ProductName] once for each time that product is purchased, my
tables and database will grow too big.

You haven't described the data in your database.

Perhaps there are ways to store the information you need without storing
all
the data you've been storing.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've removed more that 1/2 of records yet the database size did not
significantly reduce in size. Database also compacted. Database is
a
backend with security. Current size is 1.5G Database was 2000 orig.
now
converted to 2003. Any suggestions on how to reduce the size?

 

[mimoun DAGHDOUGH]

Jeff,
Yes, I do store repeat information but it's required to which product type
is being presented in each record. This is a database that records daily
test data for various products. How can I avoid the selection of
[ProductName] for each record stored? Currently I have a table with all
Product Names and use a drop down box for user to select product for each
test record recorded. I also use other fields such as [UserName] that
are
also documented with each record and the same ID could be used on many
records. I think my biggest problem is with the storage of a graph image
copied from Excel to some records. But, not sure. I have many other
fields that store numbers and others with text. A few date fields too.
There are also calculations performed on some forms to come up with
numberic
results.

I haven't tried the other suggestion from Maurice yet. How do I use SQL
for my BE?
Thanks, FL
--
FL

If I store [PersonName] once for each time that person buys a product,
any
product, my tables and database will grow too big.

If I store [ProductName] once for each time that product is purchased, my
tables and database will grow too big.

You haven't described the data in your database.

Perhaps there are ways to store the information you need without storing
all
the data you've been storing.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I've removed more that 1/2 of records yet the database size did not
significantly reduce in size. Database also compacted. Database is
a
backend with security. Current size is 1.5G Database was 2000 orig.
now
converted to 2003. Any suggestions on how to reduce the size?