delete a table-defined DataMacro from VBA: is it possible? RRS feed

  • Question

  • (tl;dr question:) Is there any way to delete a table-defined Data Macro from VBA?

    I have occasion to copy a table structure to an diagnostic Table_expected table. I populate the latter table with what I expected to be in the original table, except the data is developed a second way. The table in question, whose structure I want to copy, has a BeforeChange Data Macro. When I copy the structure, and delete the field the Data Macro operates on, the data macro lives happily on, broken, since it refers to a field that no longer exists.

    (The use case is my VBAfinder (per other DataMacro thread). The table is the ObjEffectivities table. The rows in it get populated over time as new or revised VBA is added to the database. The "second way" is I go back and re-parse all of the added VBA, and what versions of file it was found in, after the fact, and use that to go check for errors in the "accumulated over time" information. The data macro protects the key field in the original ObjEffectivities table from changes. The key field has no meaning in the diagnostic table, so I delete it in the copied structure. This causes the zombie broken data macro problem.)

    DoCmd.DeleteObject acMacro and anything I've tried with CurrentDB().AllStoreProcedures have come up empty for getting at the Data Macros...

    Tuesday, September 15, 2020 9:40 PM

All replies

  • For me, overwriting the existing macro with an empty one works: Create text file "BeforeChangeEmpty.txt" with contents

    <?xml version="1.0" encoding="UTF-16" standalone="no"?>
    <DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
    	<DataMacro Event="BeforeChange">

    Then load it into the table

    Aplication.LoadFromText acTableDataMacro, "YourTable", "YourPath\BeforeChangeEmpty.txt"
    Matthias Kläy, Kläy Computing AG

    • Edited by mklaey Sunday, October 4, 2020 6:10 PM typo
    Sunday, October 4, 2020 5:49 PM
  • Great! I bet that will do. Thanks.
    Sunday, October 4, 2020 6:50 PM