How to Reduce Size of Front-End

Topic

New Reply

I’d appreciate any ideas/hints/suggestions on how to reduce the size of a front-end database (A97). I usually compact daily. However, I’m trying to keep the size down so when zipped can be emailed (less than 1.5MB zipped).

What type objects seem to require the most space?

Any options I can consider disabling to make the database smaller?

TIA.

Reply | Quote

Replies

Are you talking about reducing it beyond the size it usually compacts to? If so, one trick I’ve found is to run a decompile on the database, then without recompiling it, to run a compact and repair. That will squeeze a bit more space out of it, but it will have to be recompiled on the other end which means it will start more slowly the first time it’s run.

Also remove any unused tables, queries, forms, code, etc. from the database. This includes all those Query1, Query2, etc., temporary queries that you might want to go back to but aren’t really using. If you have links to text files or other external files that aren’t being sent with the database, remove them and add code to create the link on the other end. Don’t leave “extra” code lying around in the database just because you might want to use it some time. Only include code you actually use and stash the extra stuff in a library database somewhere handy.

Reply | Quote

This is an application I’ve been developing over the last year. I’ve nearly completed all the major goals but finding the front-end size creeping upwards – many times I feel NOT in proportion to the objects added.

I’m trying to reduce the front-end database to the smallest possible size. Currently in the process of removing unused objects. However, ’tis a slow process (even with a find/replace utility). Plus, this seems to be making very minor gains size-wise.

How does one decompile a database? I’m using Access ’97.

Next step will be revisiting the coding to see if I can figure more efficient ways to write/structure it. For example due to time constraints I had to quickly create eight (8) separate import forms for different types of imports. Each one has a slightly different process, but some of the code repeats with each of the 8 forms. I plan to see if I can either A) use just one form and through code accomodate for the variations take code that is the same throughout these forms and place in a module.)

I’m also trying to figure out if there are Access options which automatically come with each database which could be disabled if not needed hopefully further reducing the overhead.

Thanks for any further help.

Reply | Quote

Decompile can cure a lot of odd problems in a database, including some that hold onto space when you compact the database. It works the same in Access 97 or 2000 and it decompiles all the code and other compiled objects like queries. Here’s how I usually do it.

1. With a Windows Explorer window open, Click on the Windows Start button on the taskbar and select Run from the menu.

2. Use the Explorer window to locate the Msaccess.exe file and drag that down onto the Run dialog. That will put the file name and path in the textbox.

3. Use the Explorer window to locate the database file you want to decompile and drag that down onto the same Run dialog. That will put that file name and path in the textbox after the Access executable.

4. Click in the textbox and use the End button to move to the end of the string. Then type a space and “/decompile” without the quotes.

5. Then click on OK to run the decompile. If the database has a startup routine you need to bypass, hold down the Shift key while you click the OK button.

6. Then you can go into the Visual Basic Editor either by opening any module in design mode or by clicking Alt+F11 and recompile the code project. If you need to squeeze out as much size as possible for zipping the file and are willing to live with the delay while it recompiles itself later, skip this step.

7. Now you can compact and repair the database and you’ll usually get rid of some additional size, plus you’ll cure some problems you may not even know you had.

Reply | Quote

WOAH! I’ve known about Decomplie, but never actually tried it until now. I took a fairly optimized database that was about 2.5 MB and it reduced down to 1.6 MB!!

Bravo Charlotte!!

Reply | Quote

Here’s an idea to make Decompile easier. You can use Regedit to add Decompile to the right-click menu for MDB databases. Try this:
1) In the Registry, locate: HKey_Classes_RootAccess.Application.9shell (or .8 for Access 97)

2) Add a key called Decompile (or whatever name you choose)

3) Set the default value of that key to the name you want to see in the Right-click menu (“&Decompile”)

4) Add a subkey to the newly created key called “command”

5) Set the default value of the “command” key to: “Your MS Office Path” “%1” /DECOMPILE (Quotes are necessary around the %1, but only necessary around the office path if you use the Long File Name) For Example:

"C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE" "%1" /DECOMPILE

6) When you Right-click an Access Database in Windows Explorer, you’ll see Decompile in the list.

7) Select Decompile, then Compact and Repair to finish the process!

HTH

Reply | Quote

Thanks to all who responded with suggestions. The decompile seems to have had the largest impact. The size of the database went from 7,660 KB down to a svelte 2,972 KB with the zipped version being only 704KB. I feel I now have manuevering room!!!!!!!!

Many many thanks again.

Reply | Quote

Hey Mark,
I know this is an old post but what about A2K2? Would that key then be .10?

Thanks,
Mark

Reply | Quote

Yes:
Office 97 = Office 8
Office 2000 = Office 9
Office XP = Office 10
and the new version that is being beta tested is Office 11.

This also applies when setting references to the object libraries of Office applications in the Visual Basic Editor: to work with the Excel object library, you must set a reference to the Microsoft Excel n.0 Object Library, where n = 8 for Office 97, n = 9 for Office 2000, n = 10 for Office XP.

Reply | Quote

I’ve downloaded AccessReg.Reg from here
and would like to adapt it to Access 11 and also so that only the Decompile option is added to the right click context menu. I think the attached file should do but I wanted to ask whether the name of the newly added option(e.g. “&Decompile”) shouldn’t appear in the text of the reg file.

Reply | Quote

But it does occur, in [HKEY_CLASSES_ROOTAccess.Application.11ShellDecompilecommand]

Reply | Quote

Thank you Hans, I was following Mark’s example and his third point is
3) Set the default value of that key to the name you want to see in the Right-click menu (“&Decompile”)
so I thought I also had to define the default value of that key.

Reply | Quote

That is what the next line does.

Reply | Quote

Oh ok, thank you Hans

Reply | Quote

My understanding is that you should always backup a database that you are about to decompile.

Reply | Quote

I tried this with success to the problem of creating an MDE file. However, if I perform this function again on the same file, it increases in size. Actually just testing it, I decompiled the file, then compact and repaired it three times. The resultant file went from 5.8MB to 7.5MB. Does anyone know how to cure this? Is there possibly some additional data the thing is storing each time the decompile is done?

Thanks,
Mark

Reply | Quote

Are you talking about decompiling an MDE file? Can you even do that?

I’m not sure I understand what you’re describing. You opened Access with the decompile switch and opened a database, which decompiled the code, right? Then you closed Access and reopened it without the decompile switch and did a compact and repair 3 times? What version of Access are you using? I just tested it with A2k and with the database open and it kept the same size after the first compact and repair. Did you compact to another file and then compact that again, etc.? When I did it that way, I still didn’t get any growth. Did you open up the database in between and do anything at all? That could have caused a change in the size, especially if you’re using subdatasheets or haven’t turned off Name Autocorrect.

Reply | Quote

Thanks Charlotte,
No, my original problem was that I couldn’t create an MDE file. It would just bomb out. So after reading these great posts that you guys put up, I decompiled the file. I then compacted and repaired the file. I have changed nothing about the design of the file while doing this process. The file increases in size every time I decompile and compact it.

Thanks,
Mark

Reply | Quote

Well, all I can tell you is that I can’t reproduce that effect. When I decompile and then compact, the database is reduced in size. If I do another decompile and compact on the same file, the size doesn’t change a bit. Is there anything else you’re doing or not doing?

Did you compile your database before you compacted it? That will result in a larger size, but you have to do a compile before you create an MDE or run the application or you’ll pay a performance price. The only time you would decompile and compact without recompiling first is when you need to squeeze maximum size out of the file in order to post it here or send it by email or squeeze it onto a floppy or something.

Reply | Quote

Charlotte,

My impression was that Access always compiles and compacts when creating an .MDE file, so it wouldn’t make a difference whether you compiled the .MDB before making the .MDE or not. In a test I did (on a database with lots of code), there was no difference in size or performance of the .MDE.

Reply | Quote

That may well be true, Hans. It’s been a while since I created an MDE. I just prefer to handle things like that myself so I *know* it’s been done.

Reply | Quote

Yeah, I tried all of the scenarios you listed and it keeps increasing the size every time I do it. I have no idea what could be causing it to happen. I am not changing anything about the file as far as design goes, just decompile and compact.

Thanks,
Mark

Reply | Quote

Oh, one other thing. Compiling it added size like you had mentioned. The file went from 7.0MB to 9.5MB. That seems like a lot of increase!

Thanks,
Mark

Reply | Quote

Decompiling strips out a log of temporary “pre-compiled” objects from the database. Compiling it puts them back in to improve the performance when you actually run it. With a database that small, the size increase looks huge, but it wouldn’t be as proportionally large in a larger database.

Reply | Quote

Create a new database and migrate all the objects from that one, including menubars, etc. Then try the decompile and compact stuff on the new database and see if you can reproduce the behavior.

Reply | Quote

Hey – thanks Charlotte.
Just used this tip to rescue my DB which I couldn’t even open to run Compact and Repair on!

Reply | Quote

Glad it’s still useful.

Reply | Quote

Glad it’s still useful.

Reply | Quote

Hey – thanks Charlotte.
Just used this tip to rescue my DB which I couldn’t even open to run Compact and Repair on!

Reply | Quote

Embedded graphics can be a big problem as far a space goes. Such as background pictures in forms, logos in reports, etc. Rather than embedding, you could link to the files on disk (although this becomes a problem if they aren’t where you expect them to be, which often happens with multiple sites). Or, you can load them into a table in your database, and get them when needed.

Reply | Quote

Get yourself a copy of Rick Fisher’s excellent Find and Replace. It has options to search for unreferenced objects. It’s amazing how many queries and other things are no longer used or do diddly squat.

David Parton

Reply | Quote