What can I do to shrink my Access Frontend?
I have a growing Access database. It's backend data is on SQL. The Frontend has the VBA programming, the Queries, the reports etc. Currently it is about 150 Megs (Frontend). When I do work on it, it balloons to about 350 Megs. I then compact and repair and it comes back down.
My question is, what takes up so much room? Do forms take up huge amounts of space, do queries, reports, the VBA itself?
Should I try and cut down on the amount of forms (Many times I have a main form that calls various popup forms)? Should I try to find unused VBA code and comment it out or remove it? Should I try to compress the number of queries I have?
Thanks
My question is, what takes up so much room? Do forms take up huge amounts of space, do queries, reports, the VBA itself?
Should I try and cut down on the amount of forms (Many times I have a main form that calls various popup forms)? Should I try to find unused VBA code and comment it out or remove it? Should I try to compress the number of queries I have?
Thanks
in addition to what Jim mentioned ...
are any temporary tables being created?
are a lot of records being deleted?
are any temporary tables being created?
are a lot of records being deleted?
"Do forms take up huge amounts of space"
A LOT of Forms and/or Reports can for sure be a hit.
Queries and *small* tables (lookups with just a few records, etc) are 'cheap'.
On a relative scale ... the more VBA code, the larger the DB
Note ... immediately after a Decompile ... you db will likely be slightly larger. Do an immediate Compact & Repair ... and boom !
150 Megs >> 300 >Megs is a pretty large FE.
How many Forms ?
How many Reports?
Local tables with a LOT of data?
I take it you HAVE split the db into a Front End and Back End (data only).
A LOT of Forms and/or Reports can for sure be a hit.
Queries and *small* tables (lookups with just a few records, etc) are 'cheap'.
On a relative scale ... the more VBA code, the larger the DB
Note ... immediately after a Decompile ... you db will likely be slightly larger. Do an immediate Compact & Repair ... and boom !
150 Megs >> 300 >Megs is a pretty large FE.
How many Forms ?
How many Reports?
Local tables with a LOT of data?
I take it you HAVE split the db into a Front End and Back End (data only).
If you are using temp tables, I tend to create those in an external database with basically the same name as the FE, but with "_Temp.accdb suffix. I create my temp tables in that, when I need them, link them into the FE, then when the application closes, I drop those linked temporary tables, and delete the temp database. This way you avoid any bloat in the application due to temp tables.
Back to Jim's comment about embedded images. If you are using a logo on reports or something like that, instead of embedding the logo in an image control on the report, consider creating a subreport which contains that image. Then you can reuse that subreport to display the same image and you have only actually added a single image. Depending on what version you are using, there are also image libraries, which, if memory serves me correctly, allow you to upload an image once and reuse that image in multiple locations.
HTH
Dale
Back to Jim's comment about embedded images. If you are using a logo on reports or something like that, instead of embedding the logo in an image control on the report, consider creating a subreport which contains that image. Then you can reuse that subreport to display the same image and you have only actually added a single image. Depending on what version you are using, there are also image libraries, which, if memory serves me correctly, allow you to upload an image once and reuse that image in multiple locations.
HTH
Dale
adding onto Dales comment about images:
I experimented with Resources (reusing images) in the early days and decided that I better liked embedding a logo image on a form to be a subform (ie, f_logo_sub), and report to be a subreport (ie, r_logo_sub). I also sometimes save different sizes (ie, r_logo_small_sub) This, however, would probably not account for big temporary bloat.
Since your data is linked to SQL Server, my guess is that temporary tables, or records, are being created and that is why the FE gets so big -- Dale went more in-depth about temp tables.
I experimented with Resources (reusing images) in the early days and decided that I better liked embedding a logo image on a form to be a subform (ie, f_logo_sub), and report to be a subreport (ie, r_logo_sub). I also sometimes save different sizes (ie, r_logo_small_sub) This, however, would probably not account for big temporary bloat.
Since your data is linked to SQL Server, my guess is that temporary tables, or records, are being created and that is why the FE gets so big -- Dale went more in-depth about temp tables.
"It's backend data is on SQL. "
OOPS .. I missed that ... so ignore about splitting
But ... do give the Form, Reports, Query counts ...
OOPS .. I missed that ... so ignore about splitting
But ... do give the Form, Reports, Query counts ...
Temp tables (which have already been mentioned) are the most likely culprit of the bloat but other things cause bloat also.
1. modifying objects in design view.
2. running embedded SQL (Access has to create execution plans each time the embedded SQL runs whereas it uses saved execution plans when running querydefs)
3. redimming arrays
1. modifying objects in design view.
2. running embedded SQL (Access has to create execution plans each time the embedded SQL runs whereas it uses saved execution plans when running querydefs)
3. redimming arrays
ASKER
Hey Everyone,
I will try to answer all your questions. I have to say you brought up a lot of great information, stuff I would never think of.
• I only have one image on the main form
• I do not have any temp tables
• I do not have a lot of records deleted (at least nothing I would consider a huge amount)
• OK now I know I am going to be yelled at but here are my form and query counts
o Forms – 76
o Queries – 1320
• No local tables
• I do have some embedded SQL in the VBA code but not very much at all.
I will try to answer all your questions. I have to say you brought up a lot of great information, stuff I would never think of.
• I only have one image on the main form
• I do not have any temp tables
• I do not have a lot of records deleted (at least nothing I would consider a huge amount)
• OK now I know I am going to be yelled at but here are my form and query counts
o Forms – 76
o Queries – 1320
• No local tables
• I do have some embedded SQL in the VBA code but not very much at all.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did create another database and exported everything in, it went down to about 60 megs. Thanks!
Just a comment or two on something Pat said:
<< bloat but other things cause bloat also.>>
On the embedded SQL; depending on what you mean by "embedded", that may not be entirely true. Combo's and Listbox's (or any object for that matter) which has embedded SQL do get their plans saved. These show up in the querydef's collection as objects starting with ~ (Tilde). However SQL in code does not.
I really disliked the change because prior to that, you could choose if you wanted to have SQL always re-costed or not by choosing to use a querydef object vs a SQL statement (on dynamic tables, it's sometimes wise to always get a recosting). Now there is no choice.
And on the redimming of arrays, not sure how that causes bloat. To my knowledge, VBA arrays are strictly in-memory objects.
Jim.
<< bloat but other things cause bloat also.>>
On the embedded SQL; depending on what you mean by "embedded", that may not be entirely true. Combo's and Listbox's (or any object for that matter) which has embedded SQL do get their plans saved. These show up in the querydef's collection as objects starting with ~ (Tilde). However SQL in code does not.
I really disliked the change because prior to that, you could choose if you wanted to have SQL always re-costed or not by choosing to use a querydef object vs a SQL statement (on dynamic tables, it's sometimes wise to always get a recosting). Now there is no choice.
And on the redimming of arrays, not sure how that causes bloat. To my knowledge, VBA arrays are strictly in-memory objects.
Jim.
By embedded, I mean SQL in strings in VBA. SQL in the RowSource or RecordSource properties is actually converted to a querydef and saved. If you look at the MSysObjects table, you will see them all with "~" as a prefix to their name. Bloat caused by embedded SQL is no where near what it was with previous versions but it still causes some.
To force execution plans to be recreated for querydefs, compact the FE. However, to get the most benefit, first compact the BE to get Access to update its statistics. People are appalled when they move from test to production and find that everything slows to a crawl once a substantial amount of data is added to the BE. The problem is caused because the execution plans were all based on having tiny row counts in the tables. So rather than optimizing data retrieval, Access just takes the easy way and uses full table scans. Compacting eliminates that particular problem.
When you work on a database, Access ends up keeping multiple copies of whatever you are changing so design work ALWAYS bloats the database. You need to compact frequently and also make frequent backups when you are in development mode.
1320 queries is excessive if the maximum number of tables you have is 76 (one per form). Sounds like you are not making effective use of parameters.
To force execution plans to be recreated for querydefs, compact the FE. However, to get the most benefit, first compact the BE to get Access to update its statistics. People are appalled when they move from test to production and find that everything slows to a crawl once a substantial amount of data is added to the BE. The problem is caused because the execution plans were all based on having tiny row counts in the tables. So rather than optimizing data retrieval, Access just takes the easy way and uses full table scans. Compacting eliminates that particular problem.
When you work on a database, Access ends up keeping multiple copies of whatever you are changing so design work ALWAYS bloats the database. You need to compact frequently and also make frequent backups when you are in development mode.
1320 queries is excessive if the maximum number of tables you have is 76 (one per form). Sounds like you are not making effective use of parameters.
Biggest thing that eats up space is images embedded in forms or reports....are you using any?
If not, then you might be carrying around some additional junk. The best thing to do is create a new db container and then import everything into it.
/decompile will also often help if the space being used is code related.
Jim.