Find the parent reports of MS Access subreports

I have a comprehensive 2010 MS Access database with probably a 1000 or more reports and subreports.  How can I quickly tell which reports use a particular subreport?
Is there a query that can be run against a sysobject table for this?   Track Name AutoCorrect is not turned on and so that is not an option.
Mel BrooksCITOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I don't know if you'll get a response, but the best tool out there for things like this is Rick Fisher's Find and Replace:

http://www.rickworld.com/

  The cost is VERY reasonable ($30) and has a multitude of options for find (and replacing) of values in a database.   I could not live without this.

  Some though have reported that they have not been able to get in touch with Rick to get registration.

 The other product out there is:

http://www.fmsinc.com/microsoftaccess/BestPractices.html

  Excellent product and has features that Rick's doesn't, but then it costs a bit more too ($299).

 Beyond that, you could write some code yourself fairly easily to loop through all the report objects, loop through the controls, and check the sourceobject property of subreport controls to find where your subreport is used.

 Have to say though, you'd be better off with either of the products above in the long run.


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
Mel BrooksCITOAuthor Commented:
Jim,
I was aware that I could write code to loop through all the reports , but was trying to avoid that.  I opted to go with the Total Analyzer.  After this program ran for over 24 hours (6 hours at 99% on the compact routine) , I terminated.  With what it produced I did get a boat load of information that shows me a lot of coding corrections and changes I need to make.  What I didn't find is the report that shows me the parent reports of my subreports so that I can identify which subreports are orphaned and those that have multiple parent reports.  There is a report that lists out the Parent reports and the subreports attached so I could probably read through and find the parent reports that use the same subreports.  Maybe I'm missing something on this.  Guess I need to read the manual.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< After this program ran for over 24 hours >>

 WOW.  Luke Chung (owner of FMS, Inc) sometimes drops in.   Maybe he'll spot this and be able to offer some insights on that.

<<  What I didn't find is the report that shows me the parent reports of my subreports so that I can identify which subreports are orphaned and those that have multiple parent reports.>>

 I have not used that product myself (I use Find and Replace), but you should be able to get that through the object diagram:

http://www.fmsinc.com/microsoftaccess/Documentation/diagrams/index.html#Object_Diagram

Jim.
0
Mel BrooksCITOAuthor Commented:
I believe the Total Analyzer is going to do what I want.  I found the report for Unneeded Reports.  Once I get all my corrections made and remove unneeded objects, I'm going to analyze this again and see if goes any faster.
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.