none
VBA to remove data macros RRS feed

  • Question

  • I've inherited an accdb database with lots of tables and lots of data macros enabled for each table.  I'd like to delete all the data macros from all the tables.  Using the Access UI to do it one at a time is extraordinarily tedious.  Is there a way to script this using VBA?  I can't figure out where these things fit in the Access object model.  Thanks.

    -Bruce

    Friday, May 20, 2016 3:35 PM

Answers

All replies

  • Hi Bruce. This may be a little far fetch but you might be able to use LoadFromText to replace the data macros with an empty macro. Just thinking out loud...
    Friday, May 20, 2016 4:34 PM
  • Hummmm, inserting question.  Maybe the place to get started is with database compare.

    https://support.office.com/en-us/article/Compare-two-versions-of-a-database-Database-Compare-a969a77a-5251-4b59-84ea-74e12a135a05?ui=en-US&rs=en-US&ad=US&fromAR=1

    Just thinking about it.  Try it and see how you get along.  Of course, post back with specific questions. 


    MY BOOK

    Sunday, May 22, 2016 1:15 PM
  • Bruce –

    I’m using Access 2010 and have not used macros at all. I know there's a difference between macros and data macros, … does the switch in the macros IDE: “Convert Macros to Visual Basic” apply in this case?


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Sunday, May 22, 2016 5:29 PM
  • Hi Bruce Hulsey,

    yes Data macro and macro are 2 different things.

    I try to find a way using VBA to directly delete the Data Macro but unfortunately I did not find any function or property that do this task.

    The data macro created for the tables will not be display in the list of Macros.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 23, 2016 9:38 AM
    Moderator
  • Database compare only works with Access 2013 and higher.  I'm not sure how it would help me get rid of data macros at any rate, but thanks for the reply.

    -Bruce

    Monday, May 23, 2016 2:42 PM
  • Hi DB guy,

    There may be some merit to this approach but it will take me as long to investigate the possibility and code it (if it works) as it would to sit down for a long session of mouse clicks and "y" key presses :) I was hoping someone had already done the heavy lifting.  But thanks!

    -Bruce

    Monday, May 23, 2016 2:47 PM
  • Hi DB guy,

    There may be some merit to this approach but it will take me as long to investigate the possibility and code it (if it works) as it would to sit down for a long session of mouse clicks and "y" key presses :) I was hoping someone had already done the heavy lifting.  But thanks!

    -Bruce

    Hi Bruce. Since you have the need now, I would like to request you go ahead and put forth the effort if not for yourself in the future, then maybe for the rest of us when we run into the same situation - you can share your work (if you're so inclined). If I have the time, I might give it a try.

    Just my 2 cents... Good luck!

    Monday, May 23, 2016 3:28 PM
  • Have you tried to loop through the All Macros Collection?

    Also the Object viewer shows: 

    Const acTableDataMacro = 12
        Member of Access.AcObjectType

    Perhaps you can loop through all objects to identify these?


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Monday, May 23, 2016 9:39 PM
  • I have, but data macros don't appear to be part of AllMacros.  At least my cursory attempt to loop through this collection didn't enumerate any data macros.

    -Bruce

    Friday, May 27, 2016 3:53 PM
  • I found code at utteraccess that dumps all the data macros to xml files but I'm not sure how to create an empty version of one of these that I could possibly use to overwrite them via loadfromtext.

    -Bruce

    Friday, May 27, 2016 3:57 PM
  • I found code at utteraccess that dumps all the data macros to xml files but I'm not sure how to create an empty version of one of these that I could possibly use to overwrite them via loadfromtext.

    -Bruce


    Hi Bruce. Try creating one data macro with just a comment in it. Hope it helps...
    Friday, May 27, 2016 4:29 PM
  • Hi Bruce Hulsey,

    did the suggestion given by the DB guy worked for you?

    did you able to make it with single comment?

    can you share the link of that documentation so that all community members can see that and try to help you if they know any work around.

    also I would recommend you to give your feedback regarding this issue to Access User Voice so that they came to know about this.

    please visit the link below to give Feedback to uservoice.

    Access User Voice

    Regards

    deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 30, 2016 8:48 AM
    Moderator
  • Hi Bruce Hulsey,

    currently there is no any way available to remove data macros using VBA.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 6, 2016 8:53 AM
    Moderator
  • Yes, this is the right solution. You can create an empty file and load it for all tables to remove the data macros like this:

    Public Sub delete_datamacros()
        Dim rec As Recordset
        
        Set rec = CurrentDb.OpenRecordset("SELECT [Name] FROM MSysObjects WHERE Not IsNull(LvExtra) and Type =1")
        
        Do While Not rec.EOF
            LoadFromText acTableDataMacro, rec!Name, "c:\empty.xml"
            rec.MoveNext
        Loop
        
    End Sub

    Monday, November 18, 2019 3:10 PM
  • ryguy72 -

    link to MY BOOK not working ...


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Monday, November 18, 2019 6:30 PM