Audit Update of a Field

[jdraw]

Marshall,

Well done! I agree with Pat- yours is a very good review of the Data Macro facility in MS Access. It should be helpful for anyone developing an all Access application. Data macros are great for creating audit records regardless of the source of the action invoking change to a table(s). I fully agree that the interface is "klunky" and general documentation fairly scant. I have found ways to save, load and delete table data macros to/from text.
I have had many positive comments on my Data Macro post and sample database and your documentation makes a great complement for those considering data macros.

 

[GPGeorge]

I agree with Pat and Jack. Well done.

I do disagree with a couple of assertions, though, to a certain extent. There are some work-arounds, if memory serves.

• 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.

Actually, it's not possible to do so in-line, but there is a sort of work-around. You can put StopMacro in the line before you want to omit for testing up to that point. You could move the line to the end of the macro, with that StopMacro action ahead of it, and then return it to its original position, without the StopMacro action. Clumsy, yes, but it could work.

• 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, but there is no way to copy multiple steps using Ctrl-Click, or Shift-Click, etc.

I've not done this for a while, but I think you should be able to collapse each step in the macro and use the Ctrl-Click method to select one or more in that state. Can you try that and let us know if I'm misremembering the past again?

 

[Marshall Brooks]

Thanks all for the kind words and support ...

Actually, it's not possible to do so in-line, but there is a sort of work-around. You can put StopMacro in the line before you want to omit ...

StopMacro could be useful for testing instead of incrementally developing the code. However, if you move the line to the end of the macro, now you have to remember where you moved it FROM when you need to move it back. IMHO, you would be better off creating Effort1 and Effort2 named macros for testing, which is also more cumbersome than just an in-line comment like VBA allows.

I've not done this for a while, but I think you should be able to collapse each step in the macro and use the Ctrl-Click method to select one or more in that state. Can you try that and let us know if I'm misremembering the past again?

Correct - actually it works even without collapsing the step. I edited my original post. There seems to be a slight delay on my system before it selects the additional step, which is probably why I thought it didn't work.

Also, in my defense,

Actually, my reasons for going with DM's were also somewhat flawed, but they were:

• Primarily, I had Jack's great example database and support to pull from.
• As Jack stated above, there are interactions that DM will log that VBA Audit macros will not log. I haven't heard anyone mentioning actions that VBA Audit macros can log that DM cannot log.
• I somewhat like taking the more unusual route in solutions.
• To a large extent, "I didn't know what I didn't know."
  • Either approach was uncharted territory for me, so either approach would involve a fairly steep learning curve.
  • I only wanted to log a few fields, not the entire table. Jack's database seemed to do that. Allen Browne's examples seemed to loop through all the fields of the table (which I'm not sure the federated code would support - probably it will, but Jack's example seemed more easily adapted to what I wanted.)
  • Allen has text on his page that implies that DM are clearly preferable, unless you are using an older version of Access that doesn't support them. I don't think that was truly his intent, but sadly, he has retired from Access and his pages are no longer being updated.
  • The fields that I am interested in are NOT typically directly updated. We use a datepicker so technically, VBA is being used to update the fields. I did investigate when I was frustrated and the form AfterUpdate event does trigger, so I think in MOST cases I could have made VBA audit code work for my situation, but I didn't know that when I started. (And there may be SOME instances where the DM would pick up the change and the VBA would not (without workarounds). I don't have to be concerned about that.)

 

[GPGeorge]

Thanks all for the kind words and support ...


StopMacro could be useful for testing instead of incrementally developing the code. However, if you move the line to the end of the macro, now you have to remember where you moved it FROM when you need to move it back. IMHO, you would be better off creating Effort1 and Effort2 named macros for testing, which is also more cumbersome than just an in-line comment like VBA allows.

Correct - actually it works even without collapsing the step. I edited my original post. There seems to be a slight delay on my system before it selects the additional step, which is probably why I thought it didn't work.

Actually, my reasons for going with DM's were also somewhat flawed, but they were:

• Primarily, I had Jack's great example database and support to pull from.
• As Jack stated above, there are interactions that DM will log that VBA Audit macros will not log. I haven't heard anyone mentioning actions that VBA Audit macros can log that DM cannot log.
• I somewhat like taking the more unusual route in solutions.
• To a large extent, "I didn't know what I didn't know."
  • Either approach was uncharted territory for me, so either approach would involve a fairly steep learning curve.
  • I only wanted to log a few fields, not the entire table. Jack's database seemed to do that. Allen Browne's examples seemed to loop through all the fields of the table (which I'm not sure the federated code would support - probably it will, but Jack's example seemed more easily adapted to what I wanted.)
  • Allen has text on his page that implies that DM are clearly preferable, unless you are using an older version of Access that doesn't support them. I don't think that was truly his intent, but sadly, he has retired from Access and his pages are no longer being updated.
  • The fields that I am interested in are NOT typically directly updated. We use a datepicker so technically, VBA is being used to update the fields. I did investigate when I was frustrated and the form AfterUpdate event does trigger, so I think in MOST cases I could have made VBA audit code work for my situation, but I didn't know that when I started. (And there may be SOME instances where the DM would pick up the change and the VBA would not (without workarounds). I don't have to be concerned about that.)

Hm. Regarding remembering where the macro line originated? How about a comment: "Step 1 belongs here" and then you know exactly where to put it back.

Regarding copy/paste. Good to know that it doesn't require collapsing the macro lines. I probably got into that habit for convenience.

 

[jdraw]

Just a couple of thoughts for consideration.
-When testing/developing/changing DM(s), this would be a developer/maintainer activity -NOT done by user and not on active operational system.
-Instead of testing with FE and BE when developing or replacing a DM, why not take a copy (or stick with single database if you are at initial stage) of the backend (or a recent backup) and work directly with DM on that database.
-do all testing, validation, acceptance and documentation. Save the macro with SaveAsText to known location
-load the DM using LoadFromText to your operational or acceptance database.
-when working with DM (save, modify or load) I would recommend you have exclusive control -I see no reason for having others signed on. I see this a maintenance( or original development) so not an active system.
-if working with single system, complete your DM testing etc and then split the database.

 

[Marshall Brooks]

@jdraw - I didn't follow where you were going with Reply #121 - possibly b/c I am basically doing most of what you suggested, i.e.
- Correct, development and testing of DM is done by the developer (me) and done using a (local - and under my username) copy of the backend.
- Makes sense if I were developing a new database. This is an existing split database. It would be difficult to rejoin it into a single database, and I would want to test it split. I could get around having to close the BE and open the FE to verify by adding GetRealUserName to the BE, but then I would know for sure that the FE would work - and the FE uses different methods for changing fields, unless I loaded all my forms and most of my code to the test BE, but then I'm setting myself up for failure when I try to update the production BE.
Other bullets - I'm extra-paranoid, so there are a couple of extra steps in the process.

• Ensure the DM seems to be working as planned in the test BE.
• Export the DM to text.
• Make a local copy of the production BE.
• Run DDL to add tables and load DM to the local copy of the BE.
• Verify the local copy of the BE works with both the current version of the FE, and the intended new release version of the FE.
• THEN - Get exclusive access to the BE. MOVE the BE to a local folder, run the DDL and DM inport, and MOVE the BE back to the production folder.

 

[jdraw]

Marshall,
I did not intend any confusion. I interpreted part of your comment on DM that working with a FE/BE set up was troublesome/tedious. I wanted to say --don't focus on FE/BE when developing the DM(s) -work with a single database and focus on the Tables since that's where the DM(s) occurs.
Since you have/are working with an existing FE/BE, I think my point is moot.

 

[Marshall Brooks]

Thanks - just wanted to make sure I wasn't missing something obvious - which is always a possibility!!!

 

[Marshall Brooks]

You should be able to do at least the preliminary testing of the DM in the BE. Just update the table directly. Don't bother with trying to use a form or a query. The DM is running in the BE anyway. Or, open a query in the BE and update via the query.

It depends on what is in the DM Code.

In my case, the DM calls a function named GetRealUserName, which in turn calls a function named GetUserName.

Yes, I could and initially did put both of those in the BE and it worked, but then the DM's didn't work from the FE (b/c I did not have the functions in the FE, b/c I didn't know I needed them there).

No real harm having them in both, other than I'm no longer sure if it is working b/c the functions are now in the BE, but ...

 

[jdraw]

Marshall,
I did a little more experimenting with your sample database I downloaded and modified a bit.

A few more things that may/may not be relevant to your project, but may be useful re Data Macros (DM) generally.
Some are known, but just listed here as info.

-You can use DM without writing a separate audit log per se.
-DM can be used at the table level to insert/modify/delete Dates, User, ComputerName, FrontEnd Name etc.
In the FE include functions like

                    Public Function CurrentFE() As String '<--important to return a value
                                 CurrentFE = CurrentDb.Name
                              End Function

In the data macro use
Set Field FIELDNAME
Value = Eval("CurrentFE()")

-If you open a BE directly, when functions for use with DM are in FE, you can not complete an DM action on the table with the DM. You may get a popup message/alert that simply says Unknown. You will get an error message in USysApplicationLog. The solution is to copy those functions to the BE until development on the DM is completed. If you use this technique it is not necessary to keep those functions in the BE of the operational database.

..still learning

 

[Marshall Brooks]

Just for info - I ran into something odd - probably due to order of events.

I was troubleshooting my DM's and decided to enable GetRealUserName() in the test BE. (I had added the code previously but commented it out). I had the DM open and I went into the VBA and enabled the function.

The DM Editor gave me an error that it couldn't parse GetRealUserName() and it wouldn't let me save any changes to the DM. However, the DM still worked like it used to in the front end.

I managed to open the BE, comment out the code, save the BE, close the BE, and re-open the BE and it isn't complaining anymore, but it worried me for a few minutes.

Also - somewhat odd and I'm not sure management will care about this, but in the DM, I have a line for If Updated ("Event 1 Complete") Then ....

Let's say Event 1 Complete contains today's date - 1/17/2024. I go into the FE and select the field and select the same date - 1/17/2024. From the FE's viewpoint, I have made a change and I would get asked to save the record. The background is yellow, the Save and Undo buttons are enabled (from the onChange event), if I go to a new record, the Form_BeforeUpdate() asks if I want to save. The DM doesn't seem create a record, so it looks like to it, if the old value and the new value match, then If Updated hasn't occurred. Is there any different wording I could use that would catch it?

Like I said, I'm not sure management will want to track if a field has the same old and current value - they might prefer that it ignores it, but it I would have thought it would be logged and it doesn't seem to be.

Thanks again!

 

[jdraw]

Not certain, but if any change results in the same data value, I don't think from the database perspective that a change was made. eg You start to change something and then change your mind, or in the middle of adjusting something, you realize the value is the same, re-enter and the database sees the same value??

I did have an issue this morning with the sample database (with tblAuditLog, tblSource and tblUserDemo). I have a function GetRealUserName in the FE. The DataMacro on TblSource is on After Update and is a Run Macro tblSource.macAuditTblSourceUpdateWorks.

Now I was working in the BE, I changed a value in tblSource and the changed was made, but no audit record was produced. I did not get an Alert (Unknown), but I did get an error record USysApplicationLog. I moved the function to the BE and all was well.

I also had an issue - a bit of a mystery to me and I may have some facts mixed. I tried to make a change to the data macro on tblSource and it wouldn't let me save the change. Access informed me that I could not save my change because I didn't have exclusive access to the database. I closed the database and closed Access. Hmmm? I'm on my laptop--what gives? I went into file explorer and found there was an open/unclosed laccdb for the FE. I am unsure of why that laccdb was still there. I could not delete it from file explorer. I went to Taskmanager and found msaccess.exe and ended the Task. That released the laccdb file. Went back to saving the macro change and all went well.

 

[Marshall Brooks]

Not certain, but if any change results in the same data value, I don't think from the database perspective that a change was made. eg You start to change something and then change your mind, or in the middle of adjusting something, you realize the value is the same, re-enter and the database sees the same value??

I'm not the one to ask about this, but in the FE, the on change event and the BeforeUpdate events both fire, so it seems like the FE is seeing it as a change.

I told my immediate supervisor it's something of a "If nobody hears the tree fall, did it make a sound?" If you change the database field, but the value after the change is the same as the original one, did you make a change?

Or if you have track changes on it Word and you add a space, but then you delete the space, does it show as a change? I think not so maybe Access does not either.

I've had similar issues with the locking files in the past also. Glad you figured it out!

 

[Marshall Brooks]

I'm back with good news/bad news ...

Good - @jdraw 's suggestion with using Notepad++ with the Pretty Print extension was great. I've found it easier to Ctrl-A, Ctrl-X my code in the Access DM editor, Ctrl-V into Notepad++, make my corrections, Ctrl-A,Ctrl-C in Notepad++ and Ctrl-V back into the editor in Access.

Bad - I'm having issues with @Pat Hartman 's DDL code. I'm trying to add additional fields to one of my tables. Some of the fields has spaces (bad form), but I want the field names to match the other tables in the database, which also have spaces, the issue is with fields without spaces also, though.

This code works fine (actual code except for the table name):

ALTER TABLE [tblnamewithdash]
Add ECP_Work_Order CHAR(255),
Pre_PR_Verification DateTime,
Pre_Cust_Verification DateTime,
Pre_QA_Verification DateTime;

This code crashes with Error "3021 - No Current Record" and I get the same error if I try a field name with spaces:

ALTER TABLE [tblnamewithdash]
Add TRG_SME_Review_2 DateTime;

If I change the field name to TRG_SME_Review2, it works fine, but I don't want the field name to be that.

If I put the field name in square brackets [TRG_SME_Review_2] it fails with the same error as without them.

If I put the field name it double quotes "TRG_SME_Review_2", it works, but it creates the field name in the table with quotes around it.

The same thing happens for the fields with spaces - error with no quotes or with square brackets, works with quotes, but ends up with the field in the table enclosed in quotes.

Please help!!!

 

[jdraw]

Hi Marshall,

Glad to hear things are progressing and that Notepad++ is helping.

For your DDL, try this sort of pattern for names containing space. Works for me!
I run this right in the immediate window.

Currentproject.connection.execute "ALTER TABLE Employee ADD COLUMN [Extra Notes] TEXT(25);"

 

[Marshall Brooks]

@jdraw - Thanks! (That didn't work, but I figured out the error from it). Some of those columns were already in my table, they just weren't being used on the form, so I thought they were missing. So the DDL was trying to add a column that was already there and it didn't like that.

Thank you again!

 

[Marshall Brooks]

@Pat Hartman - But this was Jet/Ace ... (Good to know, though ...)

The main issue here was I was getting a "No current record" error message, rather than "Field Already Exists".

I modified your file to work with 64-bit, allow selection of the backend, and use a default BE location on startup. Your file originally printed an Error Report and would try to send something to my local printer - which was disconnected - if it had an Error.

I tried to correct that, but I didn't really understand your error reporting code. I'm attaching both the original and my (edited to change user names ) changed version, if you or someone else want to look into it.

Thanks again!

 

[Marshall Brooks]

@Pat Hartman - Challenge accepted. I figured out a little bit what is going on.

q001 works fine.
q002 gives me an error (as expected and designed).

The original version of the database just tried to send a file to my local printer.
The modified version pops up a msgbox with "No Current Record".
However, if I look at either tblErrLog or rptErrLog, I see the "Field Already Exists" error and the "No Current Record" error.

What I would like is a way to display that information when the error occurs, rather than having to remember to open the report.

Thanks in advance!!!

 

[ebs17]

What I would like is a way to display that information when the error occurs

I would rather that no errors occur. With the “check first, then act” method, this works in the vast majority of cases.

' Aircode
If Not FieldExists(TableName, FieldName) Then CreateField(TableName, FieldName, Attributes)

You can run something like this as often as you like and add additional instructions.

I see it as a bit clumsy to send DDL queries in constant fire and thus collect errors.
Error handling may be limited to UNEXPECTED errors and to things that cannot be verified.

 

[Marshall Brooks]

@ebs17 - You somewhat missed the point of @Pat Hartman's database.

The idea isn't to create 50 queries and run them and hope for the best.

The idea is to create one query, test in in the test backend. Create another query, test in the test backend. And then run ALL the queries in the production BE.