Need help with reducing the size of a Microsoft Access 2003 database

Okay I deleted all queries, forms, reports and modules from a single database - compacted and repaired - still I have the size at 12,000 KB.  That is just too large!  

I have 37 total tables and I plan to go through all the TEXT fields and be sure that not all are at 255 size - most of which seem to be at 25 length instead.  What other measures can I take to reduce the size of the databases besides field size?

If database size can be reduced by limiting the number of indexes- what is some VBA code in which I may list all my indexes?

ALSO - as I was typing this question - I went ahead and created a new database and imported the tables only.  Now I get 2,880 KB this time for the newly created database that has the tables.  How come when I create a new database and import the tables - the size is less than when I delete all my queries, forms, reports and modules with keeping just the tables?

Also what that tells me is when I run an update on the database don't merely import other forms, reports, and queries - just create a new database and then import the tables - then theh rest and the size will be smaller?  To me that doesn't make sense - is there a proper explanation for this?

Thank you for your help thus far!
LVL 1
stephenlecomptejrAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Thomas SwaneyConnect With a Mentor OwnerCommented:
First of all normally I would have a front end database with the forms, reports, user GUI interface stuff and then have linked tables to the backend database with the exception of any temporary tables that you may use in the front end database.

I found a article that references Access 97 regarding your size question. One thing is that whenever you make any modifications, delete records, etc. the file size grows. Check out the following link to understand more about how it works:
http://web.archive.org/web/20030204023622/http:/www.databasecreations.com/largedb.htm
0
 
aesmikeConnect With a Mentor Commented:
Stephen,
You need to open the MDB when Access is in DECOMPILE mode.  This is different than Compact/Repair.  Access leaves chunks of unused code in the MDB that collects over time.  All the compacting and deleting of objects won't make this go away.  You start Access using the /DECOMPILE command line switch.  After starting MSACCESS this way, open your MDB.  Then compact/repair.
This will bring your MDB down to size.
0
 
Thomas SwaneyConnect With a Mentor OwnerCommented:
I didn't know about this. Thanks aesmike. I will try this one myself.
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.

 
Mike EghtebasDatabase and Application DeveloperCommented:
Also,

Tools/Options/General/Compact On close

Should help.

-----------
Each report add couple of MB to the size. Combine the reports. I had a project with some check boxes when user checked Group By States (as it was applicable to the reports), the report in on open event made that section visible or invisible. Basically, in place of having four or five report object I had one with choices made from a form to open the reports.

Also, most of queries could be included in the record source of the forms or reports. This should reduce the number of the query objects and possibly reduce the overall size.

Compact on close should help significantly.

Mike
0
 
peter57rCommented:
Reducing the size of text fields will have no effect on the total size.  Access only uses the space required for the entered data; it does not use fixed length fields.
0
 
stephenlecomptejrAuthor Commented:
What about creating temp tables and deleting them - is there a better to clean those up besides compact/repair?

Any way to automate DECOMPILE mode using VBA?

0
 
peter57rConnect With a Mentor Commented:
Decompile is a feature to be used by a developer during application development to resolve certain types of problem..  It is not something to do at run-time without reason.

'Compact on close' deals with runtime bloating and is what you've been pointed at.
0
 
aesmikeCommented:
Do a Compact/Repair  then note the size afterwards.
No do the DECOMPILE trick, open the MDB, compact/repair.
Note the size NOW.  It can be like slimfast for MDB's
0
 
aesmikeCommented:
Also, when your code is behaving in strange, mysterious, and unexplained ways that defy all logic....do the Decompile trick.  It fixes more things when you've tried everything else.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
aesmike,

When one decompiles, doen't the application run a bit slugish? Because it has to compile first before it can run.

Mike
0
 
aesmikeCommented:
True, It does have to compile everything.  However, this takes a few seconds at the most.  An once it's compiled, it doesn't need to be recompiled.
The DECOMPILE trick is not something you do all the time.  You do it when you need to make sure that all the extra space is compacted out.  Access has always had some problems keeping track of compiled code in the MDB.  Over time, when code is recompiled, the old compiled code remains in limbo.  Compacting doesn't clean that up.  The only thing that gets rid of this is to open it when Access is in DECOMPILE mode.
In my practice, I have a rather large front end MDB.  250 forms, 100 reports, 75 queries, 40 modules/classes, 75,000 lines of code.   My front end is typically around the 20Mb range.  With usage, the front end swells up to 100Mb+.  Yet I'll notice that the MDB may only compact down to, say, 35Mb.  Before I release an update to my client, I have a routine that decompiles the MDB then compacts it.  The decompile removes the compiled code so when I upload my front end, it may be only 15Mb.  The first time the user uses the MDB the code gets compiled and the size goes back up to 20Mb.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
re> 250 forms, 100 reports, 75 queries

Howmany tables?

I am doing an application with sevent tables 1-many, man-many, etc. via a single form. What this means, aside from the fact the size is reduced because there are fewer forms, the user will have easy learning curve and perform in very efficient manner.

I bet your reports could be reduced to 1/3 if some of the eralier information I have discussed applies in your case.

You may want to challenge me to bring your forms doen to 15, reports to 30 and queries to 15 with pay of course.

Mike
0
 
aesmikeCommented:
Well my app is a different animal but I share your philosophy on overloading my forms (and reports).  I hate apps that have a zillion little distinct and separate forms.  Usually those are apps that started with Wizards :)  I HATE maintaining forms and reports so I don't add a new form unless I can truely avoid it.

My app has approximately 170 tables.  All tables reside in either an SQL or Oracle database so my front end has only linked tables.  It is an application that runs a County Government entity (Tax Collector, Assessor, Auditor department.  There's a lot of forms and reports because there are a lot of different sub-modules within (Tax Forms, GIS, Digital Scanning, Photo Processing, Workflow, Public Service Kiosk, blah blah blah).  Still, I'm amazed that the Front end is only 20Mb.  All my graphics are linked to external files and loaded dynamically at run-time.  As you know, graphics do to a front end what Krispy Kreme does to my back end (ie makes it HUGE!)
0
 
stephenlecomptejrAuthor Commented:
Thanks to all for your contributions to this!
0
All Courses

From novice to tech pro — start learning today.