Solved

Access DB Size

Posted on 2011-02-28
11
302 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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