Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access DB Size

Posted on 2011-02-28
11
Medium Priority
?
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 58
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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