how do you view code of macro in access?

Question

i have an access database and there is a macro there. how do i view the code of the macro?

Answer 1

Open the Access Database, you will see Table, Query, Report, Module & 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.

Answer 2

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- " & strMacroName, _
      CurrentProject.Path & "\" & "Converted Macro- " & strMacroName & ".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.

Answer 3

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 & "Macro_" & d.Name & ".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 & "\Macro_" & obj.Name & ".txt"
  Next