Editing Access Embedded Data Macro - Greyed out?

Question

I have just added a new file to an access database and wanted to see an existing embedded data macro against an existing file. The macro is working but the edit macro remains greyed out and the contents cannot be viewed or edited.

The location is trusted and I have enabled all macros. I did make a copy of a working table with data macro and the macro works fine but again I cannot edit.

Any ideas what to try next?

Answer 1

Well, as noted, the named macros are DIFFERENT then just table trigger macros.

And of course if you split the database, then you ONLY will see the table trigger macros in the back end, or better stated the database (accDB file) they reside in).

So, it not clear if you EVER at any point were using named macros. I should point out that we have 3 types of macros:

Data macro - a table trigger macro. This code runs say on before insert, or after, or on delete. This is much like most systems in which a table event can run. Those code stubs can NOT be called by any other kind of code.

Named macro. These are what we call data macros, and their feature set is VERY much limited to the features you see/find/enjoy in a data macro (trigger). The difference is these routines can be called from:

A table trigger you create.

A regular non data macro (the macro's you create in the nav pane - not attached to a table.

From VBA.

Note that if you split your database or use linked tables to a database? You can't use the named macro option from the linked table. However, named macros in that back end or existing database file can be called by table triggers in that table.

Ok, so, I am "guessing" that you probably in the past never used named macros. (you might have, but they are not data trigger macros, and they are not standard macros.

In summary:

If you import that table (say from Access 2010, or 2013 or whatever) with named macros, and table trigger (data macros for table events), then they SHOULD COPY.

Note that named macros are still attached to a given table, despite that they can be called from other table macros, can be called from regular macros, or even called from VBA.

However, the above ONLY works for a non split database. If you link to such table(s) in question, then of course in table design mode, none of these options will be available.

So, it not clear what you mean by "upgrade", but if you open say a access 2010 database that has table triggers or named macro's, then you should/will continue to see them.

And if you import that table into a newer database, once again, the table triggers and named macros for the given table should copy, and should show up.

As noted, if you using a linked table, then as noted, none of the triggers nor the named macros can be seen, but once again, if you open up the database where the given table resides, then once again, all the triggers and named macros should be intact, and viewable and editable.

>>The table triggers are still hidden and not available with all access objects in the navigation panel.

That is now "confusing". Macros created in the nav pane have ZERO to do with the so called table and data macros above!

Those standard macros appear in the nav pane here:

So, the above standard macros in the above nav pane? They NEVER show in the table designer macro area. And the data macros in the table area NEVER show in the above nav pane either!

So, the above so called "UI" macros are macros that you create in Access, they appear in the nav pane, and they WILL NOT automatic be copied to a new database if you copy over the table(s) in question's. (say by using Access and importing table(s) from another database).

So, importing of table(s) WILL copy the table designs, the trigger "data macros", and the data. However, such a table copy DOES NOT copy any macro's from the above macro area in the nav pane shown above.

And more important, the data macros from the table(s) in question NEVER did show in the above nav pane either!

You might want to open up the previous database, since I suspect the regular macro's in the above nav pane are still there, but they never had anything to do with the table trigger macros. And you probably never imported them to the new database.

It also important to note that if you use a table transfer, or VBA make table query, then the table triggers are NOT copied to the new table. So, when you say you upgraded, perhaps more details here are required?

Your comments that you don't see the macros in the regular nav pane area? You NEVER did in ANY versions of Access EVER see the data (table) macros in that nav pane area - they are very different concepts.

If you want to copy those macros from another previous database, then you use Import and link:

From here:

And then this:

So using above, you have to select the macros that you want to import from the previous database. However, from above, if you select tables and import those? The so called "table level" macros, including named macro's for a given table WILL and SHOW import and should show up (but ONLY in the table design area for macro's --- not in the main nav pane for macros. They are ALWAYS kept separate and how this works has not changed in Access).

You can also of course open up the older database, and open up a given data macro, ctrl-a to select all, and paste into the newer database.

Answer 2

anted to see an existing embedded data macro against an existing file. The macro is working but the edit macro remains greyed out and the contents cannot be viewed or edited.

The answer is in your question An "embedded macro" is NOT a named macro. Named macros are created independently of form or report objects. They can be edited, but selecting the control they are embedded in in Design mode, then going to the events Tab.

The ribbon option you are looking at is used to edit macros created independelty and saved with a name.

Answer 3

Adding to the above great answer?

The option remains grayed out until you create a named macro. Since you not created any named macros as of yet, the option remains grayed out until such time you create one, or more macros....

You can certainly edit regular macros in a form, or scroll down further in the left nav pane and edit the regular macros (assuming you have any) here:

So, when looking at tables, then the option for macro's is referring to table level macros, or often what we call table triggers. So, Access now supports you having code to run or "trigger" at the table level. And you can write shared routines (named macro's) that the table routines can call. I suppose the term would be stored procedures, since those named macro's are in fact shared routines that can be called by table macros, or what are often called data macros. And plain jane regular macros can also call those named macros.

So, that option ONLY applies to when you have table(s) selected, and as noted, the option remains grayed out until such time you create at least ONE named table macro.

For standard and "regular" macro's , you use the nav pane as per above, and scroll down to the macro's section of the nav pane. The table macro option as noted is for macro code routines that BOTH standard macros, and table macros can call.

Note that such named macro's can't be called by regular macro's if you split your database into a front end, and back end. However, table triggers in the back end can still call the named macros in the back end database you create. So, these named macros run 100% separate from forms or form code, and separate from regular macro's, and these named macros in fact run at the table and database engine level.

And those named macro's if called by table trigger macros? They will EVEN run if you open the Access accDB file from 3rd party code such as vb.net or even by odbc connection's. In other words, you don't even need Access installed on your computer for these named macros to work and run. (you do however required the ACE data engine).

Answer 4

Thanks for the replies. Not sure if I have missed something. A bit more background the table 'triggers' are the macros I am trying to view from memory they were initially created with Access 2016 which has since been updated to Access 2019 - I am in the process of upgrading to 365 moving from 32bit to 64bit which has been successful on my laptop - still can't see triggers on tables.

After reading the above I tried two things. I created a "before delete event" with a comment entry - this as expected did not remove the greyed out named macro option.

Next I created a named macro - yes this does then make the edit named macro available .....but only for the newly created on. The table triggers are still hidden and not available with all access objects in the navigation panel.

This is what I see:

Any ideas how to get to the old table "trigger" macros to view/edit .... it seems to me the structures have changed and the "older" trigger are embedded deep, possibly requiring the table to be made anew, data imported and the macro structures reapplied using current options on the ribbon.

Haven't made use of other macros at this juncture and I note the comment around front and back end - of which I have more than one BE, but I can see the valve in using them to reduce large amounts of VBA code especially with "named" ones being available across the app.

Answer 5

Frankly, I hardly ever use macros. While the macro language and interface ere greatly improved with Access 2007, it still doesn't hold a candle to what VBA can do.

A named macro is one created using the Create menu and saving with a name. As you found out such a macro does enable the edit Named macros choice.