Audit Update of a Field

[jdraw]

I split your sample database and created tblUserDemo in the BE.

tblUserDemo tblUserDemo

UserID	CompUserName	ActualUserName	OtherUserSpecificInfo
1​	kl	kanth Thpell	anndr998
4​	mc	My Kat	purr
3​	qr	Queen Resource
2​	jp	That’s Me	demo

I added this code to the top of your module1 and am using FOSUserName to get the user short name automatically and use that result to get ActualUserName for tblUserDemo.

#If VBA7 And Win64 Then
    'x64 Declarations
    Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
            "GetUserNameA" (ByVal lpBuffer As String, _
                            nSize As Long) As Long
#Else
    'x32 Declaration
    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
            "GetUserNameA" (ByVal lpBuffer As String, _
                            nSize As Long) As Long
#End If

Function fOSUserName() As String
' Returns the network login name
    Dim lngLen As Long, lngX  As Long
    Dim strUserName           As String
   
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

' ----------------------------------------------------------------
' Procedure Name: GetRealUserName
' Purpose: Demo Function to get ActualUserName from tblDemoUser via CompUserName
' Procedure Kind: Function
' Procedure Access: Public
' Return Type: String
' Author: Jack
' Date: 10-Jan-24
' ----------------------------------------------------------------
Function GetRealUserName() As String
Dim UserShortName As String

'use FOSUserName to get actual computer user name

UserShortName = fOSUserName
'Lookup ActualUserName from tblDemoUser using UserShortName
GetRealUserName = DLookup("ActualUserName", "tblUserDemo", "CompUserName='" & UserShortName & "'")
End Function


Testing:

?fosusername
JP

?GetrealUserName
That’s Me

 

[Marshall Brooks]

@jdraw - basically what I did, but I think you will find if you are using a bound form in the front end and using GetRealUsername in the data macro, you have to add it to the FE also - at least I had to do so.

 

[jdraw]

Tables in BE, modules in FE. DataMacros are with the Tables(handled by Access).
I change the macro function to show the RealUserName.

Here is the updated tblAuditLog See entry #11

tblAuditLog tblAuditLog

PK	Orig_PK	REFERENCE	EVENT	OLD_VALUE	NEW_VALUE	USERNAME	MOD_DATE
3​	2​	kl-78922	Event 1 Complete	02-Mar-23	02-Jun-23	jp	08-Jan-24 6:26:29 PM​
4​	3​	jk-9533	Event 1 Complete	19-Sep-23	19-Oct-23	jp	08-Jan-24 6:28:13 PM​
5​	4​	BG-922	Event 1 Complete		03-Jan-24	jp	08-Jan-24 6:29:14 PM​
6​	5​	BG-9223	Event 1 Complete		02-Jan-24	jp	08-Jan-24 6:29:33 PM​
7​	2​	kl-78922	Event 1 Complete	02-Jun-23	02-Jun-24	jp	08-Jan-24 6:30:15 PM​
8​	5​	BG-9223	Event 1 Complete	02-Jan-24	02-Mar-24	jp	08-Jan-24 6:30:27 PM​
9​	6​	BHT-12	Event 1 Complete	30-Jan-24	01-Jul-24	jp	08-Jan-24 6:30:47 PM​
10​	1​	K-2345	Event 1 Complete	23-Dec-22	22-Dec-23	jp	08-Jan-24 6:32:12 PM​
11​	6​	BHT-12	Event 1 Complete	01-Jul-24	01-Aug-24	That’s Me	10-Jan-24 11:44:04 AM​

 

[Marshall Brooks]

Okay - I think I know what is going on now.

I added the modules to the BE. For testing, I was working with the data module in the BE and editing the table in the BE and got an error when it couldn't find the function.

Then in the FE, I got an error when it couldn't find the function.

I can probably comment out or remove the functions from the BE, but then I would get an error if I directly edited a table in the BE, but normally I wouldn't be doing that anyway.

Thank you again!!!

 

[Marshall Brooks]

@jdraw - Really dumb question time. I was looking at the .xml files you uploaded for me earlier and you had a comment in there:
NOTE:: You can copy a named macro via the immediate window using SaveAsText acTableDataMacro, "youtTableName", "Your directory and Filename and extension" My example: SaveAsText acTableDataMacro, "tblAuditLog", "C:\users\mellon\documents\macWriteAuditRec.txt"

So I open my test BE, press CTRL-G to open the immediate window, but the code in this with my directory info and press enter and the cursor moves to the next line and nothing happens ...

Disregard - it DID save the file, it just didn't show that it did anything initially!!!

 

[jdraw]

Marshall,

To output the DM xml, you can add and use this procedure to a module in your BE to SaveAsText to a stated location.

Also, if you open the xml file with Notepad++ and then use the xml plugin - Pretty Print

' Procedure : SaveAllDataMacros
' Author    : mellon
' Date      : 16-Jun-2017
' Purpose   : Routine to output or load data macros.

'To export:
'SaveAsText acTableDataMacro, "TableName", "C:\PathToFile\DataMacro.xml"
'
'To import:
'LoadFromText acTableDataMacro, "TableName", "C:\PathToFile\DataMacro.xml"

'You can open the output xml files with notepad++ and use the XML plugin with Pretty print with line feed.
'
'**Tested for the SaveAsText 16-Jun-2017
'---------------------------------------------------------------------------------------
'
Sub SaveAllDataMacros()
          Dim db As DAO.Database
          Dim tbl As DAO.TableDef
          Dim sSaveMacro As String
          Dim sLoadMacro As String
10        On Error GoTo SaveAllDataMacros_Error

20        Dim cons As String: cons = "acTableDataMacro"
  
30        Set db = CurrentDb
40        For Each tbl In db.TableDefs
50            If Left(tbl.Name, 4) <> "MSys" And _
                  Left(tbl.Name, 4) <> "USys" Then
60                Debug.Print "SaveAsText " & cons & " , """ & tbl.Name & """, ""C:\Users\jp\Documents\macrosFor_" & tbl.Name & ".xml"
70                Application.SaveAsText acTableDataMacro, tbl.Name, "C:\Users\jp\Documents\macrosFor_" & tbl.Name & ".xml"
80            End If
90        Next tbl

SaveAllDataMacros_Exit:
100       Exit Sub

SaveAllDataMacros_Error:
110       If Err.Number = 2950 Then
120           Debug.Print vbTab & tbl.Name & " has no associated data macro(s)"
130           Resume Next
140       End If
150       MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure SaveAllDataMacros of Module basModule1"
160       Resume SaveAllDataMacros_Exit
End Sub

 

[Marshall Brooks]

@jdraw - Gives me "The system cannot find C:\Users\jp\Documents" - J/K ....

Seriously - Amazing stuff as always!!! Works like a champ. I'm even further indebted to you!!!

You don't, per chance, have a LoadAllDataMacros that would load from the same folder to the production/clean/vanilla test BE, do you?

(I know, I'm greedy .. )

Again - much thanks!

 

[jdraw]

Marshall,
Here's a routine to Load Data macro(s) associated with 1 table from text.

' ----------------------------------------------------------------
' Procedure Name: LoadADataMacro
' Purpose: Routine to Load a TableDataMacro from Text
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter tblWithDatamacro (String): Name of Table to receive tableDataMacro
' Parameter FileNameWithMacroText (String): Full path and filename to the TableDtaMacro xml
' Author: Jack
' Date: 11-Jan-24
' ----------------------------------------------------------------
Sub LoadaDataMacro(tblWithDatamacro As String, FileNameWithMacroText As String)

10        On Error GoTo LoadaDataMacro_Error
20     LoadFromText acTableDataMacro, tblWithDatamacro, FileNameWithMacroText
30        On Error GoTo 0
LoadaDataMacro_Exit:
40        Exit Sub

LoadaDataMacro_Error:

50        MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure LoadaDataMacro" _
         & "  Module  TestLoadDataMacro "
60        GoTo LoadaDataMacro_Exit
End Sub

Routine to test LoadADataMacro

Sub testLoadDataMacro()

LoadaDataMacro "tblSource", "C:\users\jp\documents\tblSource_DataMacros.xml"
End Sub

A few things I have stumbled upon:
-I can run the LoadADataMacro on a table multiple times with no error. So it may delete the existing macros or overwrite or bypass the request?? In any event, the macro does get loaded from text.

-I have not found a way to delete a TableDataMacro via code.
When I use DoCmd.DeleteObject acTableDataMaco... Access says the the objectType is invalid even though I chose it from the intellisense dropdown?

- to delete a tableDataMacro, go to the DataMacro screen as normal the select the Rename or Delete option, click on Delete and Save the change related to the Macro, then Save the change to the Table. see this video

Note: You could process (LoadFromText) multiple TableDataMacros if you set up a list of Tables and their associated DataMacros. You would call the subroutine LoadADataMacro passing in the parameter values for each Table.

Further: To put this in perspective - I created a new database. Then designed a table named tblSource with same structure as original table. I have not added any data to the table, but did run LoadADataMacro.

 

[GPGeorge]

@jdraw - Gives me "The system cannot find C:\Users\jp\Documents" - J/K ....

Seriously - Amazing stuff as always!!! Works like a champ. I'm even further indebted to you!!!

You don't, per chance, have a LoadAllDataMacros that would load from the same folder to the production/clean/vanilla test BE, do you?

(I know, I'm greedy .. )

Again - much thanks!

In addition to Jack's version, you can find how we got around a serious problem in the Northwind Developer Edition template by loading data macros as part of the initialization of the new accdb from the template. You can find the code in the accdb generated by the template. In that case, IIRC, the data macros were removed from tables by the process of creating the template, requiring a method to put them back.

 

[Marshall Brooks]

@jdraw - Thank you. Will reply in a new reply.

All:

I have a quick question: On my frmSwitchboard, in Form_Activate(), I have code to change the background color of the form depending on whether I am using the test or the production backend, and I have a button to change the linked BE. It works fine on startup, and it works fine if I open a different form and then click back on the Switchboard, but I'd like it to change color immediately.

How can I run the FormActivate command again?

Me.Repaint and Me.SetFocus do not work. I'm not sure if I can call "Form_Activate". (Didn't seem to work, busy cursor - but did finally work). Docmd.OpenForm Me.Name would likely work, but seems like probably overkill for what I want (and doesn't work since the form is already open).

I could just copy that portion of the form_Activate code to the button code - easiest solution, but seems redundant.

Update - I'm not sure why, but FormActivate ran really slowly on my system, but eventually DID set the background correctly (after pressing CTRL-Break). Copying the code did the same thing.

I have it working by opening a form hidden and then closing it - awkward, but it does work.

 

[Marshall Brooks]

@jdraw - much appreciated again.

I also stumbled on the Delete/Rename option button.

Question - would I use and run this from the open target BE. https://www.access-programmers.co.uk/forums/threads/best-use-of-macros.297238/page-2#post-1851253 Seemed like he was recommending opening the BE in a new Access instance from a different database.

Might be a potato/potahto thing. Right now, the code isn't in the BE, so it would be easier to do from a separate database. Next time the BE is updated, the code would be there, so it would be easier to run it from the BE directly.

Also - there is no rush and I could likely figure out how to do it, but I was hoping for a one-step process that would be the reverse of the save routine.

I.e. the save routine saves all the files to a folder with SaveAs MacrosFor_<tablename>.xml.

So I was hoping for something that I could specify a target folder and targetdatabase (if not the current database) and the code would loop through each file in the folder, get the table name by stripping out MacrosFor_ and .xml. Check if that table exists in the database, load the .xml file, and loop to the next file/table.

 

[jdraw]

Seems reasonable. Caution would be if you have different databases with data macros, then, as you say, must check table exists in database before doing the load from text.

Have you decided on which macros you'll have (Create, Update/Modify, Delete)? Each field or just record?

 

[Marshall Brooks]

@jdraw - Can/Do I run your macros from the target BE with it open or from a different database?
Also - could you look at Reply #105. I think it should be easy, but I'm having a hard time getting it to work properly.

Seems reasonable. Caution would be if you have different databases with data macros, then, as you say, must check table exists in database before doing the load from text.

Not likely to happen, but possible and good to verify.
I have a lot of work ahead, but I might try to create a macro to do that if I run ahead of schedule. Would be really nice to be able to run one button and update the tables and minimize downtime with the production BE offline.

Then again - it's seven tables and I know the table names and I know the file names and locations, so it's just adding your procedure and calling it 7 times. For an occasional thing, probably simpler to do that.

Have you decided on which macros you'll have (Create, Update/Modify, Delete)? Each field or just record?

All three for each table, similar to what I posted earlier.

The biggest one will be update and will cover 15-20 fields in each table. Basically, the original PK (so I can query results to only the currently displayed record), the reference field so we can verify the results match the correct record (the PK is not displayed and wouldn't mean anything to anyone else), the event (b/c the field name might not mean anything to anyone), then old value, the new value, and who made the change and when.

The good thing is the If-Then block is very similar and you can copy/paste that in the interface, so it is relatively easy to build.

The delete macro is similar, but only at the record level, so no If-Then blocks and no field values, just the old original PK value, original reference value, Event of "Record Deleted" and who made the change and when. The last two are the important things: Who b/c there are only supposed to be two people and one of them is me, who can delete records, so if someone else is logged in when it is deleted, I want to find out how it is happening. I don't think it's anything intentional, but I need to eliminate it. The when b/c that allows me to restore the record (hopefully) from the previous nights backup, rather than "It wasn't here last night, or the night before, or the night before that, okay, here it is".

Same with the add macro - same data at the record level, except an event of "Record Added" and current values instead of old values. More superfluous than the delete macro, but again, there are only the same two people that are SUPPOSED to be able to add a record, so especially if I find "What is this garbage that doesn't even make sense in the database?" I can refer to that and try to figure out how it happened.

Some of it is wishful thinking also b/c - even if I didn't think I'd potentially get in trouble for answering honestly - if someone came to me and said "Our audit shows this record was deleted under your login at 9:47:33 yesterday, what exactly were you doing in the database at that time.", I wouldn't have a good answer. I have the database open all day, and I could have been doing anything ...

 

[jdraw]

I would think the Save and Load Data macros would be a developer( possibly Admin/DB) related activity. I'd probably have them in a module in the BE that could only be accessed using special account or form. Who would normally be logging in to the BE?? I can't envision a need to modify a Data Macro unless there was some major change in a process and/or table structure.

 

[Marshall Brooks]

I would think the Save and Load Data macros would be a developer( possibly Admin/DB) related activity. I'd probably have them in a module in the BE that could only be accessed using special account or form. Who would normally be logging in to the BE??

I think we got signals crossed somewhere.

I was just asking if the load tables sub could be run from the currently open BE file, like the save macro is, or do I run it from a separate db like I do with Pat's DDL queries. From your reply, it looks like the answer is yes, the open database.

Nobody but me logs DIRECTLY into the BE, and I'll most likely copy it to a local folder and modify it and copy (actually move) it back. I'm not sure who besides me knows where it is located, but some other users have admin privileges on the FE, which means (if they know where to find the .accdb FE - in which case they probably know where to find the BE file, they could figure out the location.

There are two of us (I'm one) who can add/delete records out of the BE while accessing it from the FE, but if we can do it, probably others can - but not via an obvious method, so we want to figure out how that might be happening.

 

[Marshall Brooks]

I have Reply #105 working through a combination of factors ...

• First off, I think this is related to working over VPN instead of hard-wired. I.e. it takes 10-20 seconds to update at home, as opposed to probably 1-2 seconds if I were in the office.
• Second, it's probably a perception thing, but it seems to update quickly if I manually open another form and close it. - but it may just be that I know I am waiting for the new form to load and close, so I don't notice the time it is taking to load the backend.
• Third, I added a progress bar for when the BE was being relinked and some DoEvents to take care of black screens. That has worked in the past and actually, the PB is a pop-up form, so technically that IS opening and closing a pop-up form. It made waiting feel better, but it didn't confirm the changes by refreshing the page.
• Last, I copied the code to change the colors to run while the PB was displayed and ran a Me.Repaint afterward and that seems to have worked.

 

[Marshall Brooks]

Have an odd glitch which is just enough to annoy me!!! See above.

When I am on the test BE, the switchboard has a light gray background for the detail pane and a maroon background for the footer. When I am on the production BE, the switchboard has a dark gray background for both.

When I switch, there is still a maroon line between the detail pane and the footer. However, if I do almost anything, the line disappears. (If I show or hide, the ribbon, the line disappears. If I restore or maximize the window, the line disappears. If I open another form, the line disappears while the other form is opening before the display switches to the other form. If I come back to the form from a different form, the line is gone.)

Any idea what causes the line and how to get rid of it?

 

[jdraw]

Marshall,
Here is a routine that reviews the folder where DataMacros were saved using SaveAsText.
It parses out the table names referenced in the saved macro xml. It then compares those
table names with the table defs in the database. Where there is a match, the associated data macro is loaded from the xml file.

I created it and tested it in the BE of the test database you posted.

' ----------------------------------------------------------------
' Procedure Name: DataMacroFiles
' Purpose: Routine to review xml files in a specified folder where datamacros were SaveAsText.
'          Filter filenames for "macrosFor_".
'          Compare tables identified in xml files with tables in this database. Where names match
'          Load the dataMacro(s) from text to the associated table.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter strFolder (String): Fullname of folder to be reviewed
' Author: Jack
' Date: 11-Jan-24
'
' Calls:
' LoadaDataMacro
'
' Requires:
' Reference to Microsoft Scripting Runtime
'
'Sample execution in immediate window
' DataMacroFiles "C:\Users\JP\Documents\"
' ----------------------------------------------------------------
Function DataMacroFiles(strFolder As String)

10        On Error GoTo DataMacroFiles_Error
        
         ' strFolder = "C:\Users\jp\Documents\"  'used for testing
          Dim filt As String
20        filt = "macrosFor_"     'unique characters within filenames from SaveAsText
          Dim tblName As String   'table associated with TableDataMacro
          Dim col As Collection   'collection from review of DataMacro xml files
          Dim fol As Folder       'folder
          Dim fil As File         'file
          Dim fso As FileSystemObject

30        Set fso = New FileSystemObject
40        Set col = New Collection
50        Set fol = fso.GetFolder(strFolder)

          'List  Tables and Filenames in the existing DataMacros stored via SaveAsText

60        For Each fil In fol.Files
70            If fil.Type = "xml File" Then          'datamacros store as xml files when SaveAsText
80                If InStr(fil.Name, filt) > 0 Then
90                    tblName = Mid(fil.Name, Len(filt) + 1)
100                   tblName = Left(tblName, Len(tblName) - 4)
110                   Debug.Print tblName & String(38 - Len(fil.Name), " ") & fil.Name
120                   col.Add tblName & "|" & fil.Path  'store table name "|" filename in collection
130               End If
140           End If
150       Next
        
          'Review NonSystem Tabledefs in this database
          Dim X As Integer
          Dim db As DAO.Database
          Dim tbl As DAO.TableDef
160       Set db = CurrentDb
        
          ' Compare xml files and associated tables with Current database Tables
          ' where there is a match, Load the DataMacro from the related xml file
        
170       For X = 1 To col.Count                'xml datamacro info is in Col
180           For Each tbl In CurrentDb.TableDefs
190               If Left(tbl.Name, 4) <> "MSys" Then
200                   If tbl.Name = Left(col(X), InStr(col(X), "|") - 1) Then
210                       Debug.Print "Table " & tbl.Name & " has datamacro Text at " & Mid(col(X), InStr(col(X), "|") + 1)
220                       LoadaDataMacro tbl.Name, Mid(col(X), InStr(col(X), "|") + 1)
230                       Debug.Print "---Macro was loaded from text"
240                   End If
250               End If
260           Next
270       Next
        
280      MsgBox "Data macros for tables in this database have been LoadedFromText", vbOKOnly
      
290       On Error GoTo 0
DataMacroFiles_Exit:
300       Exit Function

DataMacroFiles_Error:

310       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure DataMacroFiles" _
         & "  Module  Module1 "
320       GoTo DataMacroFiles_Exit
End Function

This is the debug.print showing how I invoked the function and the info printed to immediate by the function.

DataMacroFiles "C:\Users\JP\Documents\" <-----------Invoking the Routine
tblAuditLog macrosFor_tblAuditLog.xml
tblSource macrosFor_tblSource.xml
tCourse macrosFor_tCourse.xml
tCourseContent macrosFor_tCourseContent.xml
tStudent macrosFor_tStudent.xml
tStudentSession macrosFor_tStudentSession.xml
tStudentTestResults macrosFor_tStudentTestResults.xml
tSubject macrosFor_tSubject.xml
Table tblAuditLog has datamacro Text at C:\Users\JP\Documents\macrosFor_tblAuditLog.xml
---Macro was loaded from text
Table tblSource has datamacro Text at C:\Users\JP\Documents\macrosFor_tblSource.xml
---Macro was loaded from text

Notice: tblAuditLog has a macro loaded --because I had saved it using SaveAsText when testing my database previously, and your tblAuditLog has the same name. You may have to store the database name with the macros when saving to prevent such instances.

 

[Marshall Brooks]

@jdraw - I haven't tested it (and it might be a while before I do ..., but ...

SIMPLY AMAZING AND VERY MUCH APPRECIATED!!! THANK YOU!!!

Notice: tblAuditLog has a macro loaded --because I had saved it using SaveAsText when testing my database previously, and your tblAuditLog has the same name. You may have to store the database name with the macros when saving to prevent such instances.

Good info and good to be aware of, but that won't be an issue. It could be if I had multiple backends, and in that case, I would probably store the text files from each database in a different folder.

The plan is update the test backend, save all the macros when it is finished updating, then use a copy of the unmodified backend and load the files to the same-named tables in that and make sure it works. Then I'll take the production front end, add the module code to it, run the DDL to add new tables and fields, and load the data modules from text.

But you saved me a LOT of time. Greatly appreciated!!!!!!

 

[Marshall Brooks]

A few things I have stumbled upon:

I decided to do a write-up on things I learned with data macros - hopefully it helps others:

Disclaimer: I've never actually used the Allen Browne style VBA Audit Macros.

Bluf: Data Macros are somewhat more versatile and generally more difficult to set up.

Things that might trip up a new user:

• Like most of the federated Access Macros, you can only call functions, not subroutines from a data macro. Also, there is no MsgBox command. I often use MsgBoxes for debugging. You COULD use them with federated macros, but you would have to do something like:

Function MsgBox()
     MsgBox "I'm here."
End Function

• "Old".values are retained. This somewhat simplifies datamacros. For example, I can have an After Delete data macro that writes all of the previous values to a record in the tblAudit. With VBA Audit Macros, I think I would have to have a BeforeDeletion macro that wrote all of the values to variables and then an AfterDeletion macro that wrote all of the variables to tblAudit.
• DataMacros handle most of the ways your table can be changed - i.e. Update queries, VBA code, manual changes, etc.
• DataMacros are stored in the backend, but are called from the front end (in a split database). Therefore, if you have a function like GetUserName(), it must be included in EVERY FE that uses the BE table. If you also may edit the table directly via the BE, the function must ALSO be included in the BE.
• The Data macro must be tied to one of the actions: After Insert, After Update, After Delete, Before Delete, and Before Change. There are three ways to accomplish this:
  • In jdraw's example, he calls a named data macro to gather his parameters, then he calls a different named data macro to write the parameters to his tblAudit.
  • In my example, I called a named data macro from the action and used the named data macro to create the record in tblAuditLog. I did not use a named data macro to write the changes and I did not use parameters.
  • It would also be possible to not used NAMED data macros at all and simply write the steps to the After Insert action, etc. The drawback to this is it is somewhat harder to copy partial parts of the step, if you are troubleshooting. (With NAMED data macros, you can create Macro1, Macro2, etc. and experiment with calling different ones from each step.)
• Errors are written to UsysApplicationlog and MAY or MAY NOT have any additional error message.
• It is helpful to create DM's incrementally - i.e. record one field, test, record another field, test, etc.
• Especially in a split-database, creation and testing is NOT user-friendly. Primarily for four reasons:
  • You cannot comment out steps like you can with VBA using the '. There is a comment function, but not an easy way to comment/uncomment a step.
  • It is possible, but somewhat difficult to copy steps. You can click in a blank area and Ctrl-A and Ctrl-C and copy the entire macro, or you can select and copy single steps or single blocks (like an If-Then) block. Also - Shift-click and Ctrl-Click can be used to copy multiple steps, which you cannot easily do with VBA - Update 16-Jan-2024.
  • Syntax is not consistent, for example, in my example data macro, I use:
    • If Updated ("Event 1 Complete") <Must be in quotes, even though the interface uses square brackets.>
    • Create record in tblAuditLog
    • SetField Name tblAuditLog.Reference <Must have the tablename, even though I specified the tablename in the create record step>
    • Value = [tblSource].[REFERENCE] - <Must have the tablename, even though I am creating the data macro in this table>
    • Or value = [Old].[Event 1 Complete] - <Must NOT have the source table name, even though current field values DO require this.>
  • The interface is extremely clunky and mouse-intensive when troubleshooting. For example, as opposed to VBA where I typically write my code and run the code with F8 to execute line-by-line, I have to:
    • Open the BE File.
    • Open the tblSource in DesignView.
    • Select Table Design>Create Data Macros> Create Named Macro or Table Design>Create Data Macros> Edit Named Macro
    • Add the steps.
    • Save the macro.
    • Close the interface.
    • Save the table <The DM is technically not saved until you save the table.>
    • Close the BE <Otherwise I will get an error about the FE not being able to open b/c the BE is locked by me.>
    • Open the FE.
    • Test the DM.
    • Close the FE and open the BE if changes are required.
    • Repeat.

UPDATE 16-Jan-2024 - Adding two more bullets:

• As @Pat Hartman and @jdraw clarified, I forgot to mention that DM also only work with MS Access (Jet/Ace) BE files, so if you are using SQL Server or other formats, or think you may migrate the BE to these formats, you would have to remove the data macros and convert to VBA Code audit macros.
• Data Macros require MUCH more co-ordination between the BE and the FE updates. For example:
  • If I am performing an update with VBA Code audit macros, as I understand it, I have to make two changes:
    • An update to the BE to add the new tblAudit. However, since this is not used, it will be dormant until the FE is updated. In addition, since it is a brand new table, it doesn't require exclusive access to the BE to add the table.
    • I can then work at leisure to develop and release the new FE supporting the VBA audit macros.
      So two updates, neither one requiring exclusive access. Note that if users have an older version of the FE, and you don't force them to update, they can avoid your audit history.
  • With DM, my update plan is as follows:
    • I'll release a FE update with the new GetRealUserName function. This MUST be released prior to adding the DM to the BE or the user will get errors when they update a field. I won't change anything else related to the DM in this release (linking to the tblAudit which doesn't exist yet.)
    • After everyone updates, I'll update the BE to add the new DM, which will require exclusive access to the BE. Pat's DDL database and Jack's save and import from text macros will be GodSend's for this, and it shouldn't be much more than 5 minutes, if that, of downtime. Note that the tblAudit will start to be populated at this point, even though I can't easily view the changes.
    • I'll then release the SECOND updated FE that actually makes use of the new DM's. So three updates, one of which requires exclusive access, and might cause errors to the user.