Solved

Access 2003 Find Calls to Macros

Posted on 2016-08-31
6
49 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 35

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 35

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
Backup Your Microsoft Windows Server®

Backup 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.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

776 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