Solved

Access DB Size

Posted on 2011-02-28
11
258 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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 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
 

Author Comment

by:rczuba
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 10

Expert Comment

by:ALaRiva
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
<<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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now