?
Solved

Access 2003 Find Calls to Macros

Posted on 2016-08-31
6
Medium Priority
?
74 Views
Last Modified: 2016-09-06
Hello,

I've inherited some Access 2003 DB's that link to MSSQL DB's and the supposed developer versions of the Access DB's are simply copies of original Access DB's.  Meaning that all the tables and such are still linking to production MSSQL DB's.  I have gone in and made sure everything is pointing to a development DB in MSSQL.  Now my only concern is locating any code that fires of a macro (that would touch the production DB's).  I need to be certain that I have cut all ties to the production DB's.

I know that I can select Macros from Object list on left side and find macros there.  However, I would like to easily locate all code references to the macros.  There are too many Forms/Reports to skim through.  The way I am currently doing it is using ctrl-F (Find: macro name) on Project.  Will this guarantee that I catch all uses of a macro?  Is there anything else that I am overlooking that could impact a production DB?

Thanks
0
Comment
Question by:geewilly
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41778279
If you examined each macro and determined that it didn't contain any hard-coded references to production, then you don't need to find all macro references in code.

You will find an excellent tool at FMSINC dot com that analyzes an Access database and produces dozens of reports that could help you when trying to understand a database you didn't create.
0
 
LVL 3

Author Comment

by:geewilly
ID: 41779913
Thank you for the info Pat.  I haven't used Access extensively and just wanted to make sure that there is no weird hidden stuff that can run which doesn't show up under the Objects menu in design view.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41780290
No, there is nothing hidden.  That's why FMS does so well with its tools.  They fill in the holes that MS left in the product.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 22
ID: 41780421
in VBA code, you can also look for --> RunMacro (and RunDataMacro)

If you run my free Code Documenter on your database, you can filter for all the lines containing the above ... and then choose to show the whole procedures these lines are in

download the utility:
Code Documenter for Access, Excel, Word, and PowerPoint
http://www.AccessMVP.com/strive4peace/CodeDocumenter.htm#Download

watch the video on my YouTube channel:
http://www.youtube.com/watch?v=usWFeuhXp9Q
0
 
LVL 22
ID: 41785366
if what you are wanting to do is convert all macros to code to better see what is going on, you can do forms and reports one-by-one using the Convert [Form's/Report's] Macros to Visual Basic on the DESIGN ribbon  ... or you can run this:
Public Sub ConvertAllTheMacros()
'acCmdConvertMacrosToVisualBasic
'160725 strive4peace
'although macros are converted to code, you will have to manually fix places where the program calls those macros

   'set up error handler
   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
     , Cnt As DAO.Container _
     , Doc As DAO.Document _
     , obj As Object
   
   Dim sDocument As String _
      , iNumForms As Integer _
      , iNumReports As Integer _
      , iNumMacros As Integer _
      
   
   'set an object variable to the current database
   Set db = CurrentDb
   
'   'close all open forms
'   Call CloseAllForms
'   'close all open reports
'   Call CloseAllReports
         
   iNumForms = 0
   iNumReports = 0
   iNumMacros = 0
   
   'convert macros for Forms
   'set an object variable to the forms container
   Set Cnt = db.Containers("Forms")
   Debug.Print "-------------- Forms --------------"
   For Each Doc In Cnt.Documents
   
      'get the name of the form
      sDocument = Doc.Name
   
      'print name to debug (Immediate) window
      Debug.Print sDocument
      
      'open in design view
      DoCmd.OpenForm sDocument, acDesign
      
      'acknowledge msgbox of what to convert so user is not prompted
      '  Add error handling
      '  include comments
      'False: don't wait to process keystroke -- go to next statement
      SendKeys "{ENTER}", False
      'convert macros to vba
      DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
      'acknowledge "conversion Finished message"
      SendKeys "{ENTER}", False
            
      'close form and save
      DoCmd.Close acForm, sDocument, acSaveYes
      
      iNumForms = iNumForms + 1
         
   Next Doc
   
   'convert macros for Reports
   Set Cnt = db.Containers("Reports")
   Debug.Print "-------------- Reports --------------"
   For Each Doc In Cnt.Documents
   
      'get the name of the Report
      sDocument = Doc.Name
   
      'print name to debug (Immediate) window
      Debug.Print sDocument
      
      'open in design view
      DoCmd.OpenReport sDocument, acDesign
      
      SendKeys "{ENTER}", False
      DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
      SendKeys "{ENTER}", False
            
      'close Report and save
      DoCmd.Close acReport, sDocument, acSaveYes
            
      iNumReports = iNumReports + 1
   Next Doc
   
   'convert macros for stand along Macros
   Debug.Print "-------------- Macros --------------"
   For Each obj In CurrentProject.AllMacros
      iNumMacros = iNumMacros + 1
      
      Debug.Print obj.Name
      DoCmd.SelectObject acMacro, obj.Name, True
      SendKeys "{ENTER}", False
      DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
      SendKeys "{ENTER}", False

   Next obj
   
   MsgBox "Converted " & iNumForms & " macros for forms " _
      & iNumReports & " macros for reports " _
      & iNumMacros & " macros  ", ,"Done"

   
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set obj = Nothing
   Set Doc = Nothing
   Set Cnt = Nothing
   Set db = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   ConvertAllTheMacros "

   Resume Proc_Exit
   ' Ctrl-Break the MsgBox... then Debug
   ' then set Resume to be the next statement by right-clicking on it
   '            and choosing Set Next Statement from the shortcut menu
   ' then press F8 to step through code one line at a time to see what is wrong
   Resume
   
End Sub

Open in new window

0
 
LVL 3

Author Closing Comment

by:geewilly
ID: 41786634
Thank you for verifying that the source of macro is the only place I need to be concerned with.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

719 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