DB Front End is extremely large !

[SodaJim]

Hello,

I'm new here and thought this would be the best place to begin fine tuning
my database skills.

I developed an Overtime DB that has worked well for over a year now. I'm
currently working on splitting the DB and have removed almost all the data in
three tables. I'm leaving data so I can see the results. The problem I've run
into is that the front end is 14 megs! I only have three tables(BE), four
forms, several querys, and two reports... This DB up until recently was only
around 3 megs if my memory servers me...

What I have noticed is the Sys Table, MSysAccessObjects, has around 3435
records! I attempted to remove records to no avail.

What's the best avenue to get the FE back to a reasonble size before I split
the "Live" DB and distibute the FE to the other computers...?

Thanks in advance for any suggestions!

Jim

 

[Allen Browne]

Try this sequence:

1. Create a new database.

2. Turn off the Name AutoCorrect check boxes under:
Tools | Options | General.

3. Import (File | Get External | Import) the tables, or link them (File |
Get External | Link) if the database is split.

4. Import the other objects (queries, forms, reports, macros, modules.)

5. Set minimal References under Tools | References (from the code window).
If you are not sure what references you need for your version of Access,
see:
http://allenbrowne.com/ser-38.html

6. Compile (Debug menu, from the code window).

7. Compact:
Tools | Database Utilities | Compact

The other thing that can make the front end large is any graphics you have
imported into your forms or reports, or stored in an OLE Object field in a
table.

 

[SodaJim]

Thanks for quick response Allen!

I followed your instructions only to have the DB size reduced to 10 megs...

Here's a few details for analyzing:
3 tables:
tbl1 - 61 records
tbl2 - 19 records
tbl3 - 17 records
8 queries
5 forms with no images
2 reports

MSysAccessObjects - 2614 records
MSysACEs - 112 records
MSysObjects - 38 records
MSysQueries - 144 records
MSysRelationships - 1 record

This still seems like a very large foot print, 10 megs, for this DB...?

Any thing else I should be looking at or is this the nature of the beast...?

Thanks again for your suggestions!
Jim

 

[Allen Browne]

Jim, I don't really know what MSysAccessObjects does. It's a system table
with long binary data, and seems to relate to replication. 2614 seems high.

An alternative approach might be to use the undocumented SaveAsText and
LoadFromText to rebuild the database, and see if that shrinks the number of
entries in this table.

 

[SodaJim]

Thanks again Alan!

I'll attempt this technique this evening and see what results...

Thank you,
Jim

 

[SodaJim]

Hello again,

Where can I find info on SaveAsText/LoadFromText...?

I attempted to export everything as txt files but this didn't work well as
some fields had problems... Not to mention how time consuming this technique
is since I had to export each object at a time...

I wish I knew what MSysAccessObjects table stored, why soo many records, at
least in this case, and why I cannot delete any of the records in an attempt
to minimize file size...

Thanks again,
Jim

 

[Allen Browne]

SaveAsText/LoadFromText is undocumented, but the arguments are
straightforward. You just specify the type of object, its name, and the name
of the file.

You can use this programmatically, e.g. looping through the AllForms
collection. I'm not sure, but if memory serves correctly, this backup wizard
by Terry Kreft uses this technique:
http://www.mvps.org/access/modules/mdl0045.htm