2

I have several Access files with data from a group of users that I'm importing into one master file. The tables in the user files are each configured with a Before Change data macro that adds a timestamp each time the user edits the data.

("Data macros" are similar to triggers in SQL Server. They are different from UI macros. For more info, see this page.)

I'd like to import these timestamps into the master file, but since the master file is a clone of the user files, it also contains the same set of data macros. Thus, when I import the data, the timestamps get changed to the time of the import, which is unhelpful.

The only way I can find to edit data macros is by opening each table in Design View and then using the Ribbon to change the settings. There must be an easier way.

I'm using VBA code to perform the merge, and I'm wondering if I can also use it to temporarily disable the data macro feature until the merge has been completed. If there is another way to turn the data macros off for all files/tables at once, even on the users' files/tables, I'd be open to that too.

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
EJ Mak
  • 799
  • 1
  • 8
  • 29

2 Answers2

3

Disable the code? No. Bypass the code? Yes.
Use a table/field as a flag. Set the status before importing. Check the status of this flag in your event code, and decide if you want to skip the rest of the code. I.e.

If [tblSkipFlag].[SkipFlag] = false
  {rest of data macros}
EndIf
Tom Collins
  • 4,069
  • 2
  • 20
  • 36
1

Another answer here explains how you can use the (almost-)undocumented SaveAsText and LoadFromText methods with the acTableDataMacro argument to save and retrieve the Data Macros to a text file in XML format. If you were to save the Data Macro XML text for each table, replace ...

<DataMacro Event="BeforeChange"><Statements>

... with ...

<DataMacro Event="BeforeChange"><Statements><Action Name="StopMacro"/>

... and then write the updated macros back to the table then that would presumably have the effect of "short-circuiting" those macros.

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418