how do you view macro code in access

Question

I have a Microsoft Access database and there is a macro there  How do I view the code of the macro

User · Accepted Answer

Open the Access Database  you will see Table  Query  Report  Module  amp  Macro  This contains the macros which can be used to invoke common MS-Access actions in a sequence   For custom VBA macro  press ALT F11

User · Answer

In Access 2010  go to the Create tab on the ribbon  Click Macro  An  Action Catalog  panel should appear on the right side of the screen  Underneath  there s a section titled  In This Database   Clicking on one of the macro names should display its code

User · Answer

You can try the following VBA code to export Macro contents directly without converting them to VBA first  Unlike Tables  Forms  Reports  and Modules  the Macros are in a container called Scripts  But they are there and can be exported and imported using SaveAsText and LoadFromText  Option Compare Database  Option Explicit  Public Sub ExportDatabaseObjects   On Error GoTo Err ExportDatabaseObjects      Dim db As Database     Dim d As Document     Dim c As Container     Dim sExportLocation As String      Set db   CurrentDb        sExportLocation    C  SomeFolder       Set c   db Containers  Scripts       For Each d In c Documents         Application SaveAsText acMacro  d Name  sExportLocation  amp   Macro    amp  d Name  amp    txt      Next d   An alternative object to use is as follows     For Each obj In Access Application CurrentProject AllMacros     Access Application SaveAsText acMacro  obj Name  strFilePath  amp    Macro    amp  obj Name  amp    txt    Next

User · Answer

EDIT  Per Michael Dillon s answer  SaveAsText does save the commands in a macro without having to go through converting to VBA  I don t know what happened when I tested that  but it didn t produce useful text in the resulting file   So  I learned something new today   ORIGINAL POST  To expand the question  I wondered if there was a way to retrieve the contents of a macro from code  and it doesn t appear that there is  at least not in A2003  which is what I m running    There are two collections through which you can access stored Macros     CurrentDB Containers  Scripts   Documents   CurrentProject AllMacros   The properties that Intellisense identifies for the two collections are rather different  because the collections are of different types  The first  i e   traditional  pre-A2000 way  is via a documents collection  and the methods properties members of all documents are the same  i e   not specific to Macros   Likewise  the All    collections of CurrentProject return collections where the individual items are of type Access Object  The result is that Intellisense gives you methods properties members that may not exist for the particular document object   So far as I can tell  there is no way to programatically retrieve the contents of a macro   This would stand to reason  as macros aren t of much use to anyone who would have the capability of writing code to examine them programatically   But if you just want to evaluate what the macros do  one alternative would be to convert them to VBA  which can be done programmatically thus     Dim varItem As Variant   Dim strMacroName As String    For Each varItem In CurrentProject AllMacros     strMacroName   varItem Name      Debug Print strMacroName     DoCmd SelectObject acMacro  strMacroName  True     DoCmd RunCommand acCmdConvertMacrosToVisualBasic     Application SaveAsText acModule   Converted Macro-    amp  strMacroName          CurrentProject Path  amp       amp   Converted Macro-    amp  strMacroName  amp    txt    Next varItem   Then you could use the resulting text files for whatever you needed to do   Note that this has to be run interactively in Access because it uses DoCmd RunCommand  and you have to click OK for each macro -- tedious for databases with lots of macros  but not too onerous for a normal app  which shouldn t have more than a handful of macros

User · Answer

This did the trick for me  I was able to find which macro called a particular query  Incidentally  the reason someone who does know how to code in VBA would want to write something like this is when they ve inherited something macro-ish written by someone who doesn t know how to code in VBA    Function utlFindQueryInMacro          strMacroNameLike As String          strQueryName As String          As String         c  2012 Doug Den Hoed        NOTE  requires reference to Microsoft Scripting Library     Dim varItem As Variant     Dim strMacroName As String     Dim oFSO As New FileSystemObject     Dim oFS        Dim strFileContents As String     Dim strMacroNames As String     For Each varItem In CurrentProject AllMacros     strMacroName   varItem Name     If Len strMacroName    0       Or InStr strMacroName  strMacroNameLike   gt  0 Then          Debug Print      MACRO        strMacroName         Application SaveAsText acMacro  strMacroName   c  temp txt          Set oFS   oFSO OpenTextFile  c  temp txt           strFileContents              Do Until oFS AtEndOfStream             strFileContents   strFileContents  amp  oFS ReadLine         Loop         Set oFS   Nothing         Set oFSO   Nothing         Kill  c  temp txt           Debug Print strFileContents         If InStr strFileContents  strQueryName      0 Then             strMacroNames   strMacroNames  amp  strMacroName  amp               End If     End If     Next varItem     MsgBox strMacroNames     utlFindQueryInMacro   strMacroNames  End Function

[ms-access] how do you view macro code in access?

Examples related to ms-access