Taking Out the Trash (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 10:51
Joined
Dec 20, 2007
Messages
2,061
Well, where to begin, no, I mean really. Where to begin?

Bloat, bloat and more bloat.

I'm guesstimating there are about thirty percent extra components, in a database with over a thousand major objects. This system was built by importing, and developing, forms, queries, tables and modules, over a long period of time.

How would you go about reducing them to as close to minimum as possible. Some of the code pieces were written by Allen Brown in the late 90s early 20s. Some of that is still running.

Are there any short cuts or best practices?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:51
Joined
Oct 29, 2018
Messages
21,358
Hmm, not sure there is a shortcut. You could maybe start out using the dependency checker, but it doesn't check all objects.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:51
Joined
Feb 19, 2013
Messages
16,553
just some basic suggestions. And assumes db has been kept in a reasonable state.

For tables, and assuming you use relationships, check if there are tables in msysobjects that are not in msysrelationships. Any that aren't are potential targets for deletion.

for forms and reports scan through your code for docmd.openform/report if you don't find the form or report they also are potential targets for deletion. Take care re subforms/reports

Queries are more problematic and I don't have any simple suggestions right now.

Having identified the potential tables/forms/reports for deletion, turn off autocorrect if on, then rename those potential items with a zz prefix.

If you subsequently find something falls over, you can rename it back again. Otherwise after a few months, delete them. Alternatively move them to a db archive and bring back if required.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:51
Joined
Jan 20, 2009
Messages
12,849
Get the V-tools plugin, use the Total Deep Search and enter the name of an object. It will find everywhere that it is mentioned in the entire project.

Compact and repair regularly as stuff is removed.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:51
Joined
Jan 20, 2009
Messages
12,849
Having identified the potential tables/forms/reports for deletion, turn off autocorrect if on, then rename those potential items with a zz prefix.

Just posting to re-emphasise the importance of turning off NameAutoCorrect first in case the OP misses this detail
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:51
Joined
Feb 19, 2002
Messages
42,970
I would start with Total Access Analyzer by www.FMSINC. It isn't free. It isn't "cheap" but it will save you a lot of time and so will pay for itself. It has tons of reports that help you to connect the dots and to find objects which are not being used.

In my databases, I always turn off NameAutoCorrect and leave it off unless I want it to do its thing. It MUST be off to make my following suggestion work.

When I find objects I think are not used, rather than deleting them, I rename them to zzzoriginalname to push them to the end of the list and I leave them for a month or two. The reason for having NameAutoCorrect off is because it will be "helpful" and reconnect any reference to the zzz name which isn't what you want at all. You want things to break so you can actually identify if this object is used in some obscure way.
 

Users who are viewing this thread

Top Bottom