Access DB Size

I have an Access DB whose size has gotten to be large, around 12 MB.  The data is not in the DB, as it's linked to the tables in the DB where the data resides, so I know it's not the tables/data increasing that's causing it.

To troubleshoot, I went in and deleted all the forms, then the size went down to 11 MB.  I deleted all the reports, 10.5 MB, all Queries 10 MB, then all Modules and linked tables, and it went down to 8.3 MB.  

How is this possible?  I've compacted an repaired and there is absolutely nothing in the DB anymore.  No linked tables, no forms, no queries, no VB, no modules, nothing, and it's 8.3 MB!!  That means that of the 11 MB original size, 8.3 MB of it is nothing.  

I created a blank DB and imported everything into it, and the size was only 2.8 MB.  Why is this happening?  Is there something I need to do to keep the size down?  
rczubaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
<<JimD:  I've done compact and repair numerous times.  And, while I know that 12MB is nothing, I'm trying to determine how to get rid of 8 MB of nothing. >>

  You mean the 4MB?  If I read your original question right, you went from 12MB down to 8MB.

  The answer is that both Access and JET maintain a lot of structures within a mdb, ie. the msys* tables, temp SQL queries (those begining with ~), and most importantly, the VBA project file.

  I say the last is most important because JET has nothing to do with it directly.  It only sees a BLOB field and cannot compact it.

  Access is responsible for that managing that.  And while you can delete the VBA source, you can't directly do anything with the compiled source.  /Decompile will invalidate it, but that's the only thing you can do that affects it.

  Importing ditches the space because on an import, only source code is imported.

  There are other things in there as well attached to other objects which you don't see that even a import won't clean up either.  There are a couple of apps floating around that will attack that, but all are in various stages of development.  EatBloat is the best I've seen yet, but it hasn't been release either.

  But as the others have said, it's not something to really worry about.  If your concerned about keeping the front end as small as possible, before distributing import everything into a fresh DB.  That's the best you'll do.

JimD.
0
 
ALaRivaCommented:
Access bloats and holds the space, even though it is not in use.

If you compact/repair that 8.3MB version, I'm sure it would decrease close to the 2.8MB size you seen in the new version.

Regular Compact/Repairs are common practice, but just make sure that you keep good backups in case something goes wrong in the process.

hth

- Anthony
0
 
rczubaAuthor Commented:
Anthony:

The 8.3MB has absolutely nothing in it--no tables, forms, reports, queries, vb scripts, etc.  And I did compact/repair after removing everything and it still has 8.3MB.

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 FYI, as has been said, 12MB is nothing.  A Access (JET) database can go up to 2GB.

 And as Anthony has said, JET doesn't re-use deleted space.  You need to do a compact and repair to reclaim that space.

  That can be done from the menu, command line, or at close (select compact on close in options).

JimD.
0
 
rczubaAuthor Commented:
JimD:  I've done compact and repair numerous times.  And, while I know that 12MB is nothing, I'm trying to determine how to get rid of 8 MB of nothing.
0
 
ALaRivaCommented:
Sorry, I didn't notice that you had already said in the original post that you had C&R'd.

Maybe some hidden tables? I'm at a loss. Although, I'm kind of wondering why it is that you are so hamstrung on this? If you are only noticing this issue in a database in which you removed everything, why be so concerned? Was it a problem prior to this posting, what was happening?

- Anthony
0
 
ALaRivaCommented:
Again, if you pulled the tables into a new database, and it was fine, why be so stuck on fixing this particular EMPTY database which is showing the issue?
0
 
rczubaAuthor Commented:
I've been troubleshooting some slow-down in performance since the release of a new version of the DB.  That being said, one of the first things I noticed was the size difference--previous version was 3MB, new version was 12MB.  While there were some new forms/tables/reports added in the new version, it was definitely not 4 times as much as the original database.  
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Why is this happening? "
It simply just does some times ... and you already figured out the fix by importing all the objects  into a new db container.  Don't really lose any sleep over it.  

mx
0
 
ALaRivaCommented:
OK, but what I'm saying is that you were able to import the stuff into a new database without seeing the bloat. Why not distribute that and see if that resolved in? You could have had a bit of corruption, and importing to a new database was all that was needed.

IMO, it seems like you already have the solution, you just need to put it out there and monitor it to see how things go.

JMO, but searching for the cause to this mysterious bloat is a waste of your time when you have essentially gotten around the issue with the new database importing.

- Anthony
0
 
ALaRivaCommented:
damn me and my long winded-ness.

mx said it.
0
All Courses

From novice to tech pro — start learning today.