Solved

Access 2003 Find Calls to Macros

Posted on 2016-08-31
6
39 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 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 34

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 Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 19
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 19
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now