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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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?
Any way to automate DECOMPILE mode using VBA?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
No do the DECOMPILE trick, open the MDB, compact/repair.
Note the size NOW. It can be like slimfast for MDB's
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.
aesmike,
When one decompiles, doen't the application run a bit slugish? Because it has to compile first before it can run.
Mike
When one decompiles, doen't the application run a bit slugish? Because it has to compile first before it can run.
Mike
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.
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.
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
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
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!)
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!)
ASKER
Thanks to all for your contributions to this!
Tools/Options/General/Comp
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