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

G'day Albert,

Great explanation and clarification for my issue which has now been solved.

You are correct in that I never used named macros resulting in my confusion. I only ever had "table trigger macros" and I now see the before change event is greyed and that provides the editing access I was seeking.

I will have to investigate and trial the other macro options as it could save much repeated coding behind forms and alike.

Thanks once again to all contributors ....case closed :)