Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Is there anyway to document all the nested objects used in an MS Access macro

Posted on 2014-02-25
5
Medium Priority
?
398 Views
Last Modified: 2014-02-26
Hello all,
I have a huge MS Access migration coming up and I'm trying to prep for it now.  Not only are we bringing up new servers, but we're also going to be converting a boatload of old (2000 - 2003) Access databases to Access 2007 (it might end up being a new version, but the version doesn't matter for this request).  What I'd like to do is clean up the databases by only bringing over the objects that are needed.  Each database has a primary Macro that runs and does a bunch of stuff.  What I'd like to be able to do is programmatically list all the objects, nested objects, functions, etc used by that macro.  

For instance:
Lets say I have a Macro called "mcrAutoRun".  That macro starts by running another macro named mcrM1.  mcrM1 runs a make table query called qryM1_Make to create the tblM1 table.  qryM1_Make uses a select query called qryM1_Select and is made up of two tables (tbl1 and tbl2).  After that the mcrAutoRun macro runs a delete query called qryM2_Delete which deletes the records in the tblM2 table.  Then the mcrAutoRun macro runs an append query called qryM2_Append to append the tbl3 records to the tblM2 table.  Lastly it runs a function called SetCurrentDate(now()) which updates a value in the tblDates table.

What I want to do is pass the macro name to some code and have it printout a list of all the dependent objects.  Whether it prints to the intermediate window or to a table or to a text file, doesn't matter to me.  The desired output would be something like this:

Macro - mcrAutoRun
Macro - mcrM1
Query - qryM1_Make
Table - tblM1
Query - qryM1_Select
Table - tbl1
Table - tbl2
Query - qryM2_Delete
Table - tblM2
Query - qryM2_Append
Table - tbl3
Table - tblM2
Code - SetCurrentDate(now())
Table - tblDates

Any help or direction would be GREATLY appreciated.
0
Comment
Question by:shannonds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 85
ID: 39886492
The best tool for doing this is Total Access Analyzer (www.fmsinc.com). It will tell you everything available for a database.

Rick Fisher's Find and Replace (www.rickworld.com) can do the same thing, although it's not as robust as TAA, and you'll have to dig around a bit more.
0
 

Author Comment

by:shannonds
ID: 39886998
How would you do this in TAA?  I've never used it before and it's a little unclear as to how to tell it to give me a list of all the objects used in a selected macro.  In the documentation wizard, I selected only the macro I'm interested in.  Went through and named the output file and it went ahead and did it's thing, but the end result wasn't what I'm looking for.  It looks like it will do what I'm looking for, I just don't understand how to get it to do it.  I looked for an online demo/tutorial, but no luck.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 39888353
Total Access Analyzer has a complete help file, and will go over all the options needed.

Essentially you would allow TAA to analyze your database, then use the Documentation Explorer to review those objects. There is a "Macro" category in that explorer, which would tell you what you need to know about each macro. Click on that, then expand the Macro you want to review, and you'll see all the details you could ever want to know about TAA.

TAA won't print items exactly as you listed here (nothing will, unless you code it yourself) but it will get you where you need to be.
0
 

Author Closing Comment

by:shannonds
ID: 39888592
Would have preferred some code I code write to loop through the object collection or something like that, but it looks like Total Access Analyzer might do what I need.  Unfortunately, it takes FOREVER to run.  I have a feeling that this process is going to be painful...  LOL
Thanks for pointing me in a direction.
0
 
LVL 85
ID: 39889316
It's always painful ...

FWIW, you could write code to show you the various attributes of different objects, but since you're looking for dependency information, the process is a LOT trickier. You'd need to parse out the SQL of each query and try to find the various object/field names, and then go from there.

I tried it once, and ended up spending a LOT more time than was necessary on the project ... overall, you're far better off just biting the bullet and using TAA.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question