Audit Update of a Field

[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!!!

 

[Pat Hartman]

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.

 

[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!

 

[Pat Hartman]

SQL Server uses single quotes rather than double as the delimiter. It also accepts the square brackets.

 

[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!

 

[Pat Hartman]

without a database to link to and the new query,there is no way to test what you sent. Therefore, YOU need to identify where in the code you are getting an error so we can look at it.

The error code adds a row to a table for each error.

 

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

 

[ebs17]

And then run ALL the queries in the production BE.

What happens if the measure is carried out again?
Are you 100 percent aware of the exact status of the working backend?
What happens if an error occurs in the middle of executing the measure and only some of the DDL queries were executed?

The working backend, where the customer's irreplaceable data is located, deserves special care. Any problem there can be expensive.