VBA to remove data macros

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

Answer 1

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...

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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!

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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...

Answer 12

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.

Answer 13

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.

Answer 14

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

Answer 15

ryguy72 -

link to MY BOOK not working ...

---

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