Solved

Access 2003 Find Calls to Macros

Posted on 2016-08-31
6
69 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 38

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 38

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

635 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