Retrieving Macros info from Access with C#

I'm working on a migration project that deals with several Access 2003 DBs which have hundreds of macros defined on each.  I would like write a quick console app that will open the Access file and print out each name and actions of all the macros. 
(I.E. if i have a macros in an Access file named MyMacros and it's actions are "Run Query 1", "Run Query 2" and "Run Query 3" --- I want to be able to print this information to a file)

How would I go about in doing this? Which libraries do I need?
 

Question Info


Last updated November 15, 2017 Views 1,519 Applies to:
Answer

There really isn't a good/easy way to do this.

The AllMacros collection allows us to loop through the names of all macros in a database but unfortunately, there is no collection/object within the Access Object Model that allows us to iterate through Macro actions.

You could possibly use the undocumented "Saveastext" function to dump each macro into a text file. Exporting a single macro using this function would look like this:

Application.saveastext acMacro,"macro1","c:\macro1.txt"


The result would be a text file called macro1.txt that looks something like this:

Version =196611
ColumnsShown =8
Begin
    Action ="MsgBox"
    Argument ="\"test\""
    Argument ="-1"
    Argument ="0"
End
Begin
    Action ="OpenReport"
    Argument ="Report1"
    Argument ="5"
    Argument =""
    Argument =""
    Argument ="0"
End


If you were then able to write C# code to parse through the exported text file to identify Macro actions, this might be a possible solution for you.

 

To dump all macros from a database into text files at the same time, you could implement code similar to the following:

' Copy this code into a module in a new database

Function FilterDB(strFilePath As String)
Dim objAccess As Object
Dim strFolder As String
Dim strCurrentObject As String

Dim fs

Dim objAllObjects As New Collection
Dim objObjectGroup As Object
Dim intObjType As Integer
Dim i As Integer
Dim j As Integer


'Open the source database
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase strFilePath, False

strFolder = Left(strFilePath, InStrRev(strFilePath, "\", Len(strFilePath)))

With objAllObjects
    .Add objAccess.CurrentProject.AllMacros
End With

'create folder for text files
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.folderexists(strFolder & "\texttmp") Then
    fs.CreateFolder (strFolder & "\texttmp")
End If

'loop through all macros in database and export them to text files
For i = 1 To objAllObjects.Count
    If objAllObjects(i).Count > 0 Then
        For j = 0 To objAllObjects(i).Count - 1
           Set objObjectGroup = objAllObjects(i)
          
           strCurrentObject = objObjectGroup(j).Name
           intObjType = objObjectGroup(j).Type
           objAccess.SaveAsText intObjType, strCurrentObject, _
           strFolder & "texttmp\" & strCurrentObject & intObjType & ".txt"
        Next j
    End If
Next i
 
End Function


To run the code, type the following command in the Immediate window:

?filterdb ("c:\PathToYourDatabase.mdb")


Hope this helps,
Kathy

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.