140 MB file went to 5.08 MB after editting 1 table - is thispossible??

[mattNJ]

Hello All -

I need some ACCESS insight...please...
Several years ago, I built an access db to track my business
scheduling and accounts payable/receivable.
So this database is EXTREMELY IMPORTANT TO ME.

The file has grown to 140 MB.
Today I made a copy of the file and then edited my calendar table.
I removed all columns which had 2006 data (72 totals columns) - the
table had about 144 columns originally.
I then added 72 columns with 2008 headers. These columns are now
blank since I have not added any 2008 data yet. Afterwards, I looked
around and everything looks good - my 2007 data is there and correct.

HERES THE ISSUE.... my new database file has the size of 5.08 MB????
My edit removed about 50% of the files data -- so why is the new file
so small??

Any ideas.... everything looks good...is it possible that the new file
is okay???
It just seemed strange for the file size to go down so much.

Thanks for the help and insight.
- Matt

 

[Tom Wickerath]

Hi Matt,

Are you in the habit of regularly compacting your Access databases? In
Access 2000-2003, you click on Tools > Database Utilities > Compact and
repair database, to perform this procedure. If you still have a backed-up
copy of the original 140 MB file, I suspect that you will see it decrease in
size significantly by doing a compact and repair.

I then added 72 columns with 2008 headers.

Ummm....your database doesn't sound like it is normalized properly. In
general, there are very few cases where a table should need more than about
25 fields, maximum. If it works for you, great, but this doesn't sound like
an optimum design. You might want to read the first two papers here, by
Michael Hernandez:

Database Design 101
http://www.accessmvp.com/JConrad/accessjunkie/DatabaseDesign101


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John W. Vinson]

Hello All -

I need some ACCESS insight...please...
Several years ago, I built an access db to track my business
scheduling and accounts payable/receivable.
So this database is EXTREMELY IMPORTANT TO ME.

The file has grown to 140 MB.
Today I made a copy of the file and then edited my calendar table.
I removed all columns which had 2006 data (72 totals columns) - the
table had about 144 columns originally.
I then added 72 columns with 2008 headers. These columns are now
blank since I have not added any 2008 data yet. Afterwards, I looked
around and everything looks good - my 2007 data is there and correct.

HERES THE ISSUE.... my new database file has the size of 5.08 MB????
My edit removed about 50% of the files data -- so why is the new file
so small??

Any ideas.... everything looks good...is it possible that the new file
is okay???
It just seemed strange for the file size to go down so much.

Thanks for the help and insight.
- Matt

Several thoughts:

I SURE HOPE YOU KEPT A BACKUP. If you didn't, your data is almost surely
toast.

Access databases do *not* shrink on their own. Compacting the database will
recover space freed by deleting records. Might you have the database set to
Compact On Close? Or did you Compact after editing the table structure? That's
the only thing I can think of to do this.

And... if you have a table with 72 columns, that's about 40 columns too many
for any properly designed table (not to speak of 144). If you have "2007
headers" or "2008 headers" in your fieldnames, you have a spreadsheet, not a
relational table! And if you need to change fieldnames every year... you're
way, way off on the wrong track! Data should be stored *in fields in the
table*, not in fieldnames. If you have fields for (say) date periods, your
table design should be reconsidered: "fields are expensive, records are
cheap".

John W. Vinson [MVP]

 

[mattNJ]

Hi Matt,

Are you in the habit of regularly compacting your Access databases? In
Access 2000-2003, you click on Tools > Database Utilities > Compact and
repair database, to perform this procedure. If you still have a backed-up
copy of the original 140 MB file, I suspect that you will see it decrease in
size significantly by doing a compact and repair.


Ummm....your database doesn't sound like it is normalized properly. In
general, there are very few cases where a table should need more than about
25 fields, maximum. If it works for you, great, but this doesn't sound like
an optimum design. You might want to read the first two papers here, by
Michael Hernandez:

Database Design 101
http://www.accessmvp.com/JConrad/accessjunkie/DatabaseDesign101

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________










- Show quoted text -

Hello Tom & John-

Thank you for the replies. As you can tell, I am not a DBA - just a
business owner who has some unique data requirements who started up a
database.
So Tom I took your advice and this is what happened.
I made a copy of the original 140MB production database and did the
REPAIR AND COMPACT and the file reduced to 5.1MB (WOW!).
Can the REPAIR and COMPACT affect the database in a negative way (even
if the database is not setup in the best way)?

These finding ive me hope that my new database (the one I removed 72
columns and added columns for 2008) is okay.
THE ONLY STRANGE THING IS THAT I DID NOT RUN COMPACT & REPAIR ON THAT
DATABASE - it's almost like it did it automatically???

I ran some reports between the new database and the live production db
and numbers match.
Do you have anymore insight on this???
Thank you again for the help and time - YOU GUYS ROCK! - Matt

 

[Tom Wickerath]

Hi Matt,

I made a copy of the original 140MB production database and did the
REPAIR AND COMPACT and the file reduced to 5.1MB (WOW!).

As John indicated, Access databases do *not* shrink on their own. You must
use Compact and repair to recover slack space after deleting records, or
other objects, such as forms and reports. To help keep your JET database
healthy, I recommend getting in the habit of using Compact and repair on a
somewhat frequent basis, although I do not recommend setting the option
Compact on Close.

Can the REPAIR and COMPACT affect the database in a negative way (even
if the database is not setup in the best way)?

It's always best to make a backup copy of your database immediately prior to
compacting. I do this by simply adding the .mdb file to a new .zip archive.
Here is an article that is worth reading (and bookmarking):

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

There is one case that comes to mind where you might experience troubles,
but this is easily fixed:

PRB: "Table 'TempMSysAccessObjects' Already Exists" Error Message
May Occur While Using the Compact and Repair Database Utility
http://support.microsoft.com/kb/818099/

(There are also other problems that may occur, but these are solved by
keeping your system updated with service packs, per the article above, "How
to keep a Jet 4.0 database in top working condition").

These finding ive me hope that my new database (the one I removed 72
columns and added columns for 2008) is okay.

I'm sure it is just fine.

THE ONLY STRANGE THING IS THAT I DID NOT RUN COMPACT & REPAIR ON THAT
DATABASE - it's almost like it did it automatically???

Did you import the objects (tables, queries, forms, reports, etc.) into a
new database? Or, did you by chance have the Compact on close option enabled?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[mattNJ]

Hi Matt,


As John indicated, Access databases do *not* shrink on their own. You must
use Compact and repair to recover slack space after deleting records, or
other objects, such as forms and reports. To help keep your JET database
healthy, I recommend getting in the habit of using Compact and repair on a
somewhat frequent basis, although I do not recommend setting the option
Compact on Close.


It's always best to make a backup copy of your database immediately prior to
compacting. I do this by simply adding the .mdb file to a new .zip archive.
Here is an article that is worth reading (and bookmarking):

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

There is one case that comes to mind where you might experience troubles,
but this is easily fixed:

PRB: "Table 'TempMSysAccessObjects' Already Exists" Error Message
May Occur While Using the Compact and Repair Database Utility
http://support.microsoft.com/kb/818099/

(There are also other problems that may occur, but these are solved by
keeping your system updated with service packs, per the article above, "How
to keep a Jet 4.0 database in top working condition").


I'm sure it is just fine.


Did you import the objects (tables, queries, forms, reports, etc.) into a
new database? Or, did you by chance have the Compact on close option enabled?

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________








- Show quoted text -

Hi Tom -

Thanks for the reply and help.

Did you import the objects (tables, queries, forms, reports, etc.) into a
new database? Or, did you by chance have the Compact on close option enabled?

I just did a copy/ paste of the whole .mdb production file and then I
performed my edits on the copy version.
I did my remove/ add of comuns to a table - saved and then renamed
copy file to 2008-Production.
That's it -- and the file size was at 5MB, while original prod file
was 140MB, but now I see that if I perform a repair/compact to the
original prod .mdb
(tested this on a copy of prod file) -- it goes down to 5MB so it
seems that for some reason, the new 2008-Production.mdb file was
automatically
repair/compact since I did not check - compact on close.... who
knows..... Thanks again for the help.
Happy Holiday!
- Matt

 

[Tom Wickerath]

Hi Matt,

I forgot to mention earlier, but one time at work I was helping another
person with their database. I asked them to send it to me by e-mail. Too
large was their reply. Even zipped?, I asked. Yes, still too large. We ended
up finding a shared folder with enough space, where we both had access rights
to the folder. This person's database was some 380 MB in size, yet it didn't
contain much of anything when I looked. I compacted it and it went down to
297 KB (or 297 KB/1024 KB/MB = 0.29 MB)!

Happy Holidays to you too. Good luck on your project!

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[mattNJ]

Hi Matt,

I forgot to mention earlier, but one time at work I was helping another
person with their database. I asked them to send it to me by e-mail. Too
large was their reply. Even zipped?, I asked. Yes, still too large. We ended
up finding a shared folder with enough space, where we both had access rights
to the folder. This person's database was some 380 MB in size, yet it didn't
contain much of anything when I looked. I compacted it and it went down to
297 KB (or 297 KB/1024 KB/MB = 0.29 MB)!

Happy Holidays to you too. Good luck on your project!

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________







- Show quoted text -

Thanks Tom for the help! You are the best!