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.
TIMASAsked:
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.

 
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 ConnectWise

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
 
TIMASAuthor 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
 
TIMASAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.