locked
Export an Access Macro from a database that is NOT the CurrentDB RRS feed

  • Question

  • OK, I've been looking all over the place with no (good) luck. I FINALLY found a couple instances where someone asked the question, but alas, NO ANSWERS!!!

    I'm trying to automate the process of "exporting" (whether it's using an "export" command, "save as text", etc doesn't matter to me) the CONTENTS of an Access macro. That is, the CODE, not some data from a table, etc. The key is that I'm NOT doing this from the DB from which I want to export it.

    Consider something like this:

       SaveAsText acMacro, <the macro>, <a href="file://\\\<share>\<text">\\<server>\<share>\<text file>

    The above works fine when the code is running from the SAME DB. But now consider that I'm actually iterating through hundreds of databases. So the VBA code is more like this (pseudo-code):

    For Each FullDBName in RS

       Set dbX = OpenDatabase(FullDBName)

       <???>

    Next FullDBName

    So what do I put in the <???> above to basically same Macro "XYZ" to a text (or equivalent) file "MacroExport"?

    Thanks in advance!

    Mark Stepan

    Friday, March 23, 2012 2:58 PM

Answers

  • SaveAsText is an Access command, not a DAO command, so you have to use Automation to create another instance of Access, open the database, export the macro, and close the database. Here is an example:

    Sub ExportMacro(DBName As String, MacroName As String, TextFileName As String)
        Dim app As Access.Application
        Set app = CreateObject("Access.Application")
        app.OpenCurrentDatabase DBName
        app.SaveAsText acMacro, MacroName, TextFileName
        app.CloseCurrentDatabase
        app.Quit
    End Sub

    Use like this:

    ExportMacro "C:\Access\Test.mdb", "MyMacro", "C:\Docs\MyMacro.txt"

    For efficiency, you don't have to create and quit the Access.Application object each time in the loop. You can create it before you loop through the databases, and quit it when the loop end. You only need to open and close each database in the loop.

    Regards, Hans Vogelaar

    • Proposed as answer by danishani Friday, March 23, 2012 3:56 PM
    • Marked as answer by MStepan Friday, March 23, 2012 11:41 PM
    Friday, March 23, 2012 3:49 PM

All replies

  • SaveAsText is an Access command, not a DAO command, so you have to use Automation to create another instance of Access, open the database, export the macro, and close the database. Here is an example:

    Sub ExportMacro(DBName As String, MacroName As String, TextFileName As String)
        Dim app As Access.Application
        Set app = CreateObject("Access.Application")
        app.OpenCurrentDatabase DBName
        app.SaveAsText acMacro, MacroName, TextFileName
        app.CloseCurrentDatabase
        app.Quit
    End Sub

    Use like this:

    ExportMacro "C:\Access\Test.mdb", "MyMacro", "C:\Docs\MyMacro.txt"

    For efficiency, you don't have to create and quit the Access.Application object each time in the loop. You can create it before you loop through the databases, and quit it when the loop end. You only need to open and close each database in the loop.

    Regards, Hans Vogelaar

    • Proposed as answer by danishani Friday, March 23, 2012 3:56 PM
    • Marked as answer by MStepan Friday, March 23, 2012 11:41 PM
    Friday, March 23, 2012 3:49 PM
  • Thanks Hans!!! I was so close (yet so far)!!! I had the pieces, but not assembled correctly (obviously) ... Thanks again. Mark
    Friday, March 23, 2012 11:42 PM