how to export Access 2010 data macros

Question

I need to transfer Access data macros from my test db to my production db. Anybody know how to do that?

I know that transferring tables from one accdb to another will also transfer the data macros, but that's not an option in my case. I also know I can recreate them manually in the production accdb, but that leaves me open to errors and requires taking the production database down for a longer time than would a scripted transfer scenario.

If I only had to do this once it wouldn't be such a big deal, but I'll need to do it many times over the course of a development project.

I've tried exporting the tables to xml, but the data macros are not included.

Please note that I'm asking about Access 2010 data macros here, not regular Access macros.

Answer 1

You may want to try a pair of undocumented VBA functions

To export:

SaveAsText acTableDataMacro, "TableName", "C:\PathToFile\DataMacro.xml"

To import:

LoadFromText acTableDataMacro, "TableName", "C:\PathToFile\DataMacro.xml"

Answer 2

To expand on Martijn Pieters' / Lanik's answer (thanks Martijn and Lanik), I had a need to create virtually the same data macros on 28 different tables, with an AfterInsert, AfterUpdate, AfterDelete, and named data macro on each. So, I used the SaveAsText command

SaveAsText acTableDataMacro, "TableName", "C:\PathToFile\DataMacro.xml"

to create a template, then used that template to create the 28 xml files via a little vba code, substituting the table names, primary keys, etc. I also created 28 LoadFromText commands. I could then use the LoadFromText commands to load all the macros at once, with a repeatable process. Now that I am done testing, I can quickly update the production database this way or add the same data macros to other tables easily.

So others know, LoadFromText overwrites any previous macros, which is terrific since I didn't have my template correct on the first try.

Given the above, the next step is to have your 'updater' database application use a DoCmd.TransferDatabase command to transfer the module with a function/sub with all the LoadFromText commands to the Data .accdb. It should also transfer a macro to run the function/sub. I tried having my updater then execute the macro to load the data macros, but Access security prevented that. so, you may need to get your user to open the database and Enable it, then run the macro. This is more convoluted then if we could edit the data macros directly, but does provide a workaround that solves the problem.

Answer 3

I don't have a great answer here.

However, in the past I often made a "log book" of changes to tables and then simple used that on site.

Do note that you can cut + paste macro code. And the code saves as XML.

For example, this after update table trigger looks like this in Access:

If you cut out the above (ctrl-a, ctrl-cc),, then you can paste that into a note pad. In fact you can even paste/open it in visual studio or any xml editor, and you see this:

So you can cut + paste out of those macros.

As noted, thus in the past I often kept a "log" of changes.

So if I was working off site and I modified 2 code modules, 4 forms and 2 reports, then I had a little log of changes. I would type into this sheet what object was changed.

When I get on site, then I quick glance at that sheet and I know to import the 3-5 objects and such a import is only a few minutes tops.

However, with table triggers and store procedures, you could have more then just a few to copy.

I would either do one of two things:

Use the log idea and

a) Un-publish the development version and bring that with me to your working site. You then import new forms, reports etc. For trigger code, you cut + paste between the two applications.

b) When you make a change to a trigger, then cut + paste into a note pad doc and place them in a directory. When on site, simply take each notepad, cut + paste into production and then move or copy the notepad item into a "done" folder.

The above is certainly less than ideal. Then again in the past I not always had code to script out a table change, and using a little log sheet worked quite well.

So in the past I often just written down that such and such table was modified and I have to add such and such column.

So it really your choice as to if you want to cut + paste out the macro code as xml into separate little documents, or cut + paste right out of development into production.

I do think if you working in differnt location, then I think it likely best to un-publish the development version, and bring that with you (I assume you know/realaize that you can make a un-published copy of a web application).

So the forms, code modules, macro's etc can just be imported rather easy (you delete the forms etc, and just import from that un-published copy).

However, for the table code? You have to cut + paste from that un-published copy one at a time into some staging area, or as noted cut + paste between the applications.

There is the possibility that save-as text might work here, but I not yet had the time to cook up a better solution.

Edit: By the way, in above I assumed a web services database, but the advice still applies to non web Access databases.