Looking for orphans

[RichardMGreen]

Hi all

I want to streamline my database by removing redundant queries and tables (i.e. anything that's no longer used).
I know I could go through renaming items and see what falls over, but is there a way of getting a graphical view of how the tables and queries are linked together?

I've tried various options under Tools/Analyze/Documenter but I just get page after page of text that means very little to me.

I'm using Access 2003 and I can't install anything due to this being a works machine and everything is locked down.

If necessary, I might be able to talk the IT bods into installing some add-ins for me but that would be about it.

All help greatly appreciated.

 

[Montez659]

Were you able to understand the Object Dependencies tool? This always helps me out. You will have to go through each element and refresh, but it can give you a quick snapshot of what tables/queries that an element relies on and also what relies on it. I have used this many time, usually in the early stages of development (when I have 42,386 test forms!) and it helps me to get rid of the excess.

Or maybe you did try it, but I didn't see it listed, so I thought that I'd give it a shot.

 

[RichardMGreen]

I did see that, but I have loads of tables and queries and it would take an age.
Is there any other way to do this to speed things up and just show me the orphans in some sort of list?

 

[CharlesChuckieCharles]

I did similar thing last year... trying to create charts (in visio) to understand the calling sequence of queries within queries within queries (over 16 levels)

I did it by getting the all the sql statements in text then programmatically checking each query name or table against each bit of SQL to build up a dependancy map

Which I then had VBA in Visio call over and over to map lower levels assigning different shapes to Tables and Queries.. ( i then added reports )

I then had to look at VBA embedded SQL and SQL on row source and control source control properties for each form ( although SQL was not just restricted to those two places)

I HAD to do it because the MDB was so archane it was close to impossible to get a grip on what was happening and where certain data was created or changed.

BUT I WOULDN'T DO IT FOR FUN, lifes to short, if its not hurting anyone leave the dead wood where it is!

 

[jackd]

I tend to agree with Montez659 -- object dependencies may be your best bet. It certainly would be the starting point.
You didn't mention unused modules/procedures. For these I use MZTools for vba. It will show procedure callers, unused variables etc. and is free. Very excellent tool -highly recommended.

Note: I see Charles has posted while I was writing. I have seen others who have tried an approach along Charles' line, but the consensus is it's a pile of work and difficult to adjust. Only relevant if maintained.

There is a routine, I think available from Crystal (strive4peace), which is a deep analyzer for tables etc.
I have seen it referenced, but have not used it personally.

 

[RichardMGreen]

There's only a little bit of VBA and it's fine (I've checked each line and variable as I've streamlined it using loops instead of repeated code with minor variations).

I'm really only interested in tables and queries that I no longer need.

 

[jackd]

Here's the link to Crystal's stuff.
http://www.accessmvp.com/strive4peace/Dictionary/Index.htm

 

[SydneyGeek]

Jack, Thanks -- that's useful

Denis