Database cleanup

I have an old database which contains several forms, reports, queries, tables, etc.  But now I have a chance to clean it up and only keep the objects that are necessary.  I've created a new empty database and have copied over from the old database the main menu or dashboard from which all reports, forms, etc. are launched.

But here is my question...  is there an easy way to determine which other forms, queries, tables, reports are needed to be copied over also from a command button on the main menu?

In other words, for example, if I have a command button on the main menu which when clicked opens a report that may have a query as it's record source, is there an easy way to know which tables, queries, and report I also need to copy over to the new database?

Maybe there is a tool out there that I am unaware of or maybe there even is a way within Access to gather this information?

--Steve
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Investigate the  object dependencies utility in Access;
Database Tools--> Object dependencies

But as the previous experts stated, ..there is no way to see what objects are "Called", ..only to see what objects depend on others and vice versa.

This is one reason why many developers do not used saved queries as recordsources, ...they simply use the query SQL:
    SELECT * FROM YourTable WHERE...

The Only way to be 100% sure is to test the database fully.
;-)

((Better to have a few odd objects left over, than to delete one that might be needed...)

JeffCoachman
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Spend $37 and buy Rick Fisher's Find & Replace:

www.rickworld.com

There is a cross reference utility in there that does exactly what you want.

 and it will be the best $37 you ever spent.  

Well worth the price and in the toolbox of just about every Expert here.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SteveL13Author Commented:
Jim,

Just so I'm clear... the utility you mention will help me gather the queries, forms, tables,etc. needed to execute a command button on the main menu?  The name "find and replace" leads me to believe that it will find objects and replace them.  But if I'm starting with an empty database there really isn't anything to replace.   Am I mistaken?

--Steve
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Steve,

 F&R is first and foremost  a find and replace tool, but it has a cross reference utility that will tell you for every object in the DB where it is used.   It also can give you every object that is not used, so it's excellent for database cleanup.

  You run a pass for every unreferenced object, delete them, then another pass.  Depending on how the DB was developed will determine how many passes you need to make, but I generally find four passes gets all the junk out.

 It will not for a given object, give you a dependency chain.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW you can download and try it out for free, but the xref utility won't be available until you register it (part of the advanced features).

Jim.
0
PatHartmanCommented:
Newer versions of Access do provide dependency information.  Unfortunately to get it, you need to turn on Name Auto Correct.  So, turn it on.  Do your analysis, then turn it off.  Never leave it on because it doesn't work the way you think it does and it will getcha every time.  Rick's Find and Replace does work the way you think it does so even though it is much more powerful, it is ultimately safer because you will understand when/what will be changed.  Do make a backup first.

Personally, I like the FMS tool (Total Access Analyzer) for analyzing unfamiliar databases.  You couldn't come close to replacing its functionality for the price and besides, your boss will probably agree to buy it if you do a good enough cost justification.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
+1 for FMS Total Access Analyzer. I own both TAA and F&R, and for analysis TAA is a better tool IMO.
0
SteveL13Author Commented:
Well, I spent the $37 for Find and Replace.  I received the registration code late last night and entered it into the product.  But now I get an error when I try to use it...

"The wizard you've requested is not installed or is in a bad state.  Please install or reinstall the wizard."

But it won't let me.  I've sent an email to the person I purchased it from.

?????
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Where are you having a problem with the install?  I've never had an issue with it all the way up through A2010.

Jim.
0
SteveL13Author Commented:
It installed fine.  I did it yesterday.  Then I got the registration code and entered it today.  Now is when I get the error.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well see what Rick comes back with, but I'm surprised you had an issue.  many of us here have it and have recommened it many times.  Don't ever remember anyone coming back and saying they had an issue with anything.

Jim.
0
SteveL13Author Commented:
I'll let you know when I've heard from him.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.