Renaming Macros in External Microsoft Access Databases Using VBA

I thought I’d simply share a function I devised to help someone out on UtterAccess in case it could help someone else out there.

The question that was originally asked was:

I’d like to be able to disable the autoexec macro in an external database by renaming it using VBA

So, basically asking how can we rename a macro in an external database.

Well, this is remarkably simple to achieve and only necessitates a couple lines of code.

'---------------------------------------------------------------------------------------
' Procedure : RenameMacroInExternalDB
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Rename the specified macro in an external database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs:None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb               : Fully qualified path and filename of the external database in
'                       which the macro resides
' sCurrentMacroName : Current Macro name which is to be renamed
' sNewMacroName     : New name to be assigned to the Macro in question
'
' Usage:
' ~~~~~~
' RenameMacroInExternalDB("C:\Temp\Clients.accdb", "AutoExec", "AutoExecDisabled")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2025-01-03              UA Help
'---------------------------------------------------------------------------------------
Sub RenameMacroInExternalDB(ByVal sDb As String, _
                            ByVal sCurrentMacroName As String, _
                            ByVal sNewMacroName As String)
    On Error GoTo Error_Handler
    Dim oAccess               As Access.Application

    ' Create a new instance of Access
    Set oAccess = New Access.Application

    ' Open the external database
    oAccess.OpenCurrentDatabase sDb

    ' Rename the macro
    oAccess.DoCmd.Rename sNewMacroName, acMacro, sCurrentMacroName

Error_Handler_Exit:
    On Error Resume Next
    'Cleanup
    oAccess.CloseCurrentDatabase
    oAccess.Quit
    Set oAccess = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: RenameMacroInExternalDB" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Reviewing this code, you can probably deduce that the same approach could just as easily be used to rename any object, and isn’t limited to Macros.

2 responses on “Renaming Macros in External Microsoft Access Databases Using VBA”