Solved

Access DB Size

Posted on 2011-02-28
11
280 Views
Last Modified: 2012-05-11
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?  
0
Comment
Question by:rczuba
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 10

Expert Comment

by:ALaRiva
ID: 34999605
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
 

Author Comment

by:rczuba
ID: 34999715
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
 
LVL 57
ID: 34999749

 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:rczuba
ID: 34999760
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
 
LVL 10

Expert Comment

by:ALaRiva
ID: 34999790
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
 
LVL 10

Expert Comment

by:ALaRiva
ID: 34999805
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
 

Author Comment

by:rczuba
ID: 34999846
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
 
LVL 75
ID: 34999869
"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
 
LVL 10

Expert Comment

by:ALaRiva
ID: 34999881
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
 
LVL 10

Expert Comment

by:ALaRiva
ID: 34999892
damn me and my long winded-ness.

mx said it.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 35000088
<<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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question