Front End Bloat Keeps Happening (1 Viewer)

[ALewis06]

I have read that I shouldn't set up an automatic compact on Close for my split db. But each time I open the front end and run the macros, the front end swells to 57MB. Then I manually run a Compact, it goes down to 16.7MB which is still too big to email. What can I do?

 

[plog]

Sounds like the macros are a red herring. I mean if after you compact its still not small enough to do what you want, the macros really aren't the issue. If the smallest you can make your database is still too big, then you are going to have to take out data to get it smaller.

Or, abandon emailing it and instead ftp it or dropbox it or whatever the kids today are doing.

 

[Galaxiom]

Do you write temporary data to the front end?

 

[ALewis06]

@plog LOL at "whatever the kids today are doing"

I definitely prefer to email it; trying to ftp it would be problematic with the group I am working with. And no way do we have Dropbox if we're still using Access 2007.

Would it help to make it an ACCDE file? Advantages? Disadvantages?


and to the other question: No, I am not writing any temporary data to the front end.

 

[jdraw]

What makes this database (FrontEnd) so large?

 

[ALewis06]

There are 9 macros; maybe that's why. Because when I open the db, after running the macros, it swells to 78MB and only when I run the Compact & Repair function does it shrink back to 36MB.

 

[cyanidem]

I had similar problem with my DB, usually around 3-4MB then suddenly bloated to 170MB. Took me some time and research but I found the culprit.
My DB links pictures and stores paths only in tables but Access likes from time to time to cache some of them for no reason and without any rule (known to me at least). There's system table MSysResources which contains those cached pictures and now I just delete every record from it with type img on exit and then compact database. Since then my DB stays at reasonable size.

 

[ALewis06]

I had similar problem with my DB, usually around 3-4MB then suddenly bloated to 170MB. Took me some time and research but I found the culprit.
My DB links pictures and stores paths only in tables but Access likes from time to time to cache some of them for no reason and without any rule (known to me at least). There's system table MSysResources which contains those cached pictures and now I just delete every record from it with type img on exit and then compact database. Since then my DB stays at reasonable size.

This sounds promising; how do I get to MSysResources? And are you saying that you manually go in and delete .img files each and every time you close the DB?

 

[cyanidem]

Right click on Navigation panel header -> Navigation Options... and then in Display Options check Show System Objects.

And I just run simple delete query before exiting application:

DELETE FROM MSysResources
WHERE MSysResources.Type="img";

 

[ALewis06]

Ok, I'll try that. I have an "Exit Database" button on my switchboard. I am wondering if I can just add the delete query to that function so that it is performed each time a user exits the DB.

 

[cyanidem]

Can't see why not. Just run it before application.quit code. And you'll probably need compacting your DB on close as well.

Edit: And maybe check first if there are any img records in that table. It could something completely different in your case that causes bloating.

 

[ALewis06]

Can't see why not. Just run it before application.quit code. And you'll probably need compacting your DB on close as well.

Edit: And maybe check first if there are any img records in that table. It could something completely different in your case that causes bloating.

I will check for img files. Everything I've read advises against automatically compacting the DB on close when it is not a single-use DB, and mine is not.

 

[ALewis06]

I should have said it is not a single-user DB. There will be multiple users accessing it.

 

[cyanidem]

I don't know why would it matter if it's multi- or single-user while we're talking about FE only? Compacting Front End won't touch linked tables from BE, will it? Unless I'm missing something I think it's fairly safe operation.

Anyway, that's up to you of course, I just think that simple deleting records from that system table won't result in smaller size of DB file, you'll need to compact it either automatically on close or manually sometimes.

 

[ALewis06]

Right click on Navigation panel header -> Navigation Options... and then in Display Options check Show System Objects.

And I just run simple delete query before exiting application:

DELETE FROM MSysResources
WHERE MSysResources.Type="img";

@cyanide No MSysResources table showed up, but Thanks for trying.

 

[cyanidem]

Are you sure? I can see it in every DB I opened.

 

[ALewis06]

What I see are the following:

MSysAccessXML
MSysACEs
MSysIMEXColumns
MSysIMEXSpecs
MSysObjects
MSysQueries
MSysRelationships

and that's it

 

[ALewis06]

So it's probably the macros/make-table queries I have: I went to a saved copy of the DB, deleted the 3-4 tables that I didn't need, then split the DB. The front end was less than 2MB! I was thrilled. But before emailing it out, I wanted to be sure all would go well; after running the macros in the switchboard menu, the size is back up to 17MB. #frustrated

 

[The_Doc_Man]

Any other type of object linking & embedding will have the same effect. You NEVER EVER want to store something inside a database other than pure Access structures if you are concerned about bloat. Don't save reports (after viewing them). Don't embed pictures. Always have some sort of pointer to the items. Now, it is possible when using your database to store just file name and type for the object being referenced (doing so as a table field) and then use CurrentDb.Name to find the fully qualified path to your database... then remove everything from that path that isn't the file name using InStrRev and the Left function, then tack on a RELATIVE path and the file name.

Example: You find that CurrentDb.Name returns C:\MyDocuments\Databases\MyDb.MDB - so with InStrRev you find the right-most "\" and use that to extract the substring C:\MyDocuments\Databases\ as the base folder. Then have a child folder - let's call it "\Extras" and put files in it like "X.JPG" and "Y.JPG". So you would store X.JPG as the name of the thing you wanted to see - or Y.JPG, or whatever.

So you have an image control for which the .Image property (or is it .Picture? I can never remember and I don't have Access handy here...) is where you load the path to the object being displayed. In the form's OnCurrent routine, find the name of the object and the base directory. Tack them together via concatenation (using \Extra as the path down from the base) to form

C:\MyDocuments\Databases\Extra\X.JPG

Load that to the image control in the OnCurrent routine. Now, how do you share this stuff? You send the compacted database and the .JPG files via e-mail, perhaps zipped. You tell your users that they must create the \Extra folder underneath whatever folder they use for the holding their copy of the front end. But it doesn't matter WHERE they put the database file - only that you have the \Extra folder beneath it.

This is obviously only a POSSIBLE solution to what you are doing, but it is a way to keep from having to actually store non-database content via OLE methods. And THAT is almost certainly a contributor to frequent bloat.

The other thing that bloats a lot is if you have a front-end table used to gather data from multiple tables on the back-end, building an intermediate data set for graphing or reporting or data mining or SOMETHING. It is not enough to use DELETE * FROM BIGWAD; - that intermediate storage will NEVER go away until you compact/repair the front end file.

 

[gemma-the-husky]

17Mb is not small, but not particularly large.

is the dbs split? is this just the data?

how small does it get if you "zip" it?

and why is using something like dropbox not possible? (you said, but I didn't follow)