Audit Update of a Field

[jdraw]

Marshall,

I'm not sure why the table events with Data macros are not highlighted when opening the table in data sheet view. I found this last week and mentioned it to some others ---no body has an answer. I noticed that the NorthWind 2.0 ver 3 also with data macros does not have the table events highlighted??

You can check for data macros with

SELECT Len([LvExtra]) AS Expr1, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((Len([LvExtra]))>32) AND ((MSysObjects.Type)=1));

result:


Query1 Query1

Expr1	Name	Type
2483​	tblAuditLog	1​
3947​	tblSource	1​

 

[Marshall Brooks]

@jdraw - it still doesn't seem to be working for me with your latest #160 changes, but ...

Not sure how to interpret this. There were 3 DM's in the source DB TblSource in the source Database.

And if it DOES work and copies the DM's, it isn't much help for the future if the DM's are not visible in Design View in the interface.

More to come.

 

[Marshall Brooks]

Copied latest routines from #157 to module2

In MarshallDM_Test_Target.accdb?

I found an issue with filename and corrected,

Corrected that, Line 230.

Module2.DataMacroFiles "C:\Users\jp\Documents\" <=====invoke routine

Shouldn't the DB name be inside the quotes also?

I just had the path also ...

Basically, I had the strfolder as Optional and I hard-coded it in the FunctionDataMacroFiles. But I tried commenting that line out and passing it the value from the subroutine and that didn't work either.

This is run from the MarshallDM_Test_Target.accdb, correct?

'''''' messages ''''''
Table tblAuditLog has datamacro in Text file at C:\Users\JP\Documents\macrosFor_tblAuditLog.xml
---Macro was loaded from text
Table tblSource has datamacro in Text file at C:\Users\JP\Documents\macrosFor_tblSource.xml
---Macro was loaded from text

I am not seeing ANY messages other than the final message that all DM's were loaded, even though they don't seem to be. Should I be seeing more?

 

[jdraw]

Marshall,

Just ran SaveAllDataMacros on the Source database

saving data macros for: tblAuditLog in file: C:\Users\jp\Documents\macrosFor_tblAuditLog.xml
tblAuditLog has no associated data macro(s)
saving data macros for: tblSource in file: C:\Users\jp\Documents\macrosFor_tblSource.xml

When I look at the Saved xml with Notepad++ I see the following
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Event="AfterUpdate">
<Statements>
<Action Name="RunDataMacro">
<Argument Name="MacroName">tblSource.macAuditTblSourceUpdateWorks</Argument>
</Action>
</Statements>
</DataMacro>
<DataMacro Name="macAuditTblSourceUpdateWorks">
<Statements>
<ConditionalBlock>
<If>
<Condition>Updated("Event 1 Complete")</Condition>
<Statements>
<CreateRecord>
<Data>
<Reference>tblAuditLog</Reference>
</Data>
<Statements>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.Orig_PK</Argument>
<Argument Name="Value">[tblSource].[PrimaryKey]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.REFERENCE</Argument>
<Argument Name="Value">[tblSource].[REFERENCE]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.EVENT</Argument>
<Argument Name="Value">"Event 1 Complete"</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.NEW_VALUE</Argument>
<Argument Name="Value">[tblSource].[Event 1 Complete]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.USERNAME</Argument>
<Argument Name="Value">GetUserName()</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.MOD_DATE</Argument>
<Argument Name="Value">Now()</Argument>
</Action>
</Statements>
</CreateRecord>
</Statements>
</If>
</ConditionalBlock>
</Statements>
</DataMacro>
<DataMacro Name="mactblsourceAuditUpdateFails">
<Statements>
<ConditionalBlock>
<If>
<Condition>Updated("Event 1 Complete")</Condition>
<Statements>
<CreateRecord>
<Data>
<Reference>tblAuditLog</Reference>
</Data>
<Statements>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.Orig_PK</Argument>
<Argument Name="Value">[tblSource].[PrimaryKey]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.REFERENCE</Argument>
<Argument Name="Value">[tblSource].[REFERENCE]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.EVENT</Argument>
<Argument Name="Value">"Event 1 Complete"</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.OLD_VALUE</Argument>
<Argument Name="Value">[tblSource].[Old].[Event 1 Complete]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.NEW_VALUE</Argument>
<Argument Name="Value">[tblSource].[Event 1 Complete]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.USERNAME</Argument>
<Argument Name="Value">GetUserName()</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">tblAuditLog.MOD_DATE</Argument>
<Argument Name="Value">Now()</Argument>
</Action>
</Statements>
</CreateRecord>
</Statements>
</If>
</ConditionalBlock>
</Statements>
</DataMacro>
</DataMacros>


Update: I then deleted the macros in the target manually. Then with the Target database I ran with the just saved data macros.

Module2.DataMacroFiles "C:\Users\jp\Documents\"
tabTest macrosFor_tabTest.xml
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 in Text file at C:\Users\JP\Documents\macrosFor_tblAuditLog.xml
---Macro was loaded from text
Table tblSource has datamacro in Text file at C:\Users\JP\Documents\macrosFor_tblSource.xml
---Macro was loaded from text
----------------------------------
And checked for data macros with query

SELECT Len([LvExtra]) AS Expr1, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((Len([LvExtra]))>32) AND ((MSysObjects.Type)=1));



Query1 Query1

Expr1	Name	Type
2483​	tblAuditLog	1​
3142​	tblSource	1​

 

[Marshall Brooks]

Export/Save seems to be working - if that is what you are telling me ...

I'm about to leave, but could you look at DM_TestTarget below and see if you see anything incorrect and if it loads for you (which I guess it was earlier.)

Thank you again.

 

[jdraw]

Marshall,

I copied your database from Downloads to Documents
Renamed the copy to "Jan30_165_DM_Test_Target.accdb"

?currentdb.Name
C:\Users\JP\Documents\Jan30_165_DM_Test_Target.accdb


Then invoked the following from your module1

DataMacroFiles "C:\Users\jp\Documents\" <======invoked here

==below are the debug.prints in immediate window==
tabTest macrosFor_tabTest.xml
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

Here are the data macros based on query

Query1 Query1

Expr1	Name	Type
2483​	tblAuditLog	1​
3142​	tblSource	1​

Here is the display from looking at Named Macros Rename/Delete



So it seems to be working as expected from what I can see.

ALSO:
As for Debug.Print see this link--it has more than debug.print, but helps put debugging into context.

 

[Marshall Brooks]

@jdraw - so it seems to be working for you and not for me.

DataMacroFiles "C:\Users\jp\Documents\" <======invoked here

You also had MacrosFor_tblSource.xml in this folder, correct? I have it in a different folder that it was saved to, but it should still work. ??? It does not HAVE to be in the same folder as the database, does it?

Tried running it from my desktop where I had both the DB and the .xml file and that didn't work either - same results ...

==below are the debug.prints in immediate window==

That would help and I don't know what lines you are using to generate it.

Could you maybe upload the code with the debug.print commands included? (sorry to get bothering)

I did look at the CPearson link and learned some things (Didn't know Shift-F8 would run a procedure and then return to line-by-line. I use Enhanced Msg Box and it takes 100 lines to display a MsgBox - this will be helpful for that.), but it explains what the debug.print statement does, but doesn't tell me what to put in it.

 

[Marshall Brooks]

Just for confirmation: LoadFromText acTableDataMacro, "tblSource", "C:\Users\<Me>\Desktop\macrosFor_tblSource.xml" from the immediate window works fine, so it is something with the VBA method that isn't working correctly for me.

 

[Marshall Brooks]

Your code DOES have debug.print lines, but I'm not seeing anything in the immediate window when I run it directly. I'm going to try stepping through it now!!!

 

[Marshall Brooks]

Yay - MORE PROGRESS!!!

70 If fil.Type = "xml File" Then 'datamacros store as xml files when SaveAsText

I think I need to change line 70 to "If extension is .xml" - Air Code: If Mid$(InStrRev(Fil.name, ".") = "xml" then ...

If Mid$(fil.Name, InStrRev(Fil.Name, "."))= ".xml" Then

Haven't tested it yet, but ...

 

[Marshall Brooks]

HOORAY!!!!!

Thank you @jdraw!!!! - Sorry it took so long to figure it out!!!

 

[jdraw]

Glad you have got things working.

Yes, for my testing I had the Saved data macro .xml in the same folder as the Database. That is because the code to do the saveAsText wrote it to that folder. It could be any other folder. The only condition is that the SaveTo folder must also be the LoadFrom folder to use the Save and Load statements for the data macros. If you are loading a modified version of a data macro from a special folder, then you would use the folders you are using--but this seems a rare condition in my view.

The immediate window is very helpful when debugging/testing-- you get a semi-permanent record up to ~100+ lines.
Msgbox is useful, but is temporary. I tend to use both MsgBox and Debug.print when testing/debugging. I use the Debug.print output to show the sequence of events--just a personal preference.

310           Debug.Print "No user tables exist in this database: " & CurrentDb.Name
320           MsgBox "No user tables exist in this database: " & CurrentDb.Name

Debug.print is quite basic. Debug.print "Print this message" will print "Print this message" in the immediate window.
Make sure that you have the immediate window available.
Here's an example from function DataMacroFiles

110                   Debug.Print tblName & String(38 - Len(fil.Name), " ") & fil.Name

this prints the current table name and a series of spaces followed by the file name

Here is a sample output from line 110
tblSource macrosFor_tblSource.xml

When I use fso, the file type is "xml file"
70 If fil.Type = "xml File" Then 'datamacros store as xml files when SaveAsText

You can use extension if you wish, but fil.type will be "xml file" for the saved data macro file.

More about debugging:
-you can put a breakpoint on lines in the vba. When you run your code, it will stop at a breakpoint if that breakpoint is on the code being executed
-you can add a Msgbox or a debug.print with text such as "made it to this point" to see that you have processed to/thru a specific line in your code.
-you can put a breakpoint early in your code, and after hitting that breakpoint, continue "stepping" thru your code 1 line at a time with F8.
-the active line when stepping will be highlighted yellow, you can move/drag that yellow line to a different line and then use F8 to step/execute a line.

Another thing re data macros, if you add Rename Delete Macro to your Quick Access Toolbar (QAT), you can immediately see what data macros you have by clicking the symbol.

 

[Marshall Brooks]

@jdraw - Thanks - I know most of that. What kept tripping me up:

• I didn't see that your code had debug.print lines already. I was getting no messages in the immediate window and you were, so I thought you had added them to your personal file for debugging but not the code you posted.
• For future-proofing, I'm not going to worry about it b/c it is working now, but your code doesn't report if no .xml file was in the folder. Essentially, that was what was happening, the code ran, didn't find anything to import, and then said "All DM's were imported."

You can use extension if you wish, but fil.type will be "xml file" for the saved data macro file.

Not going to argue with you (well, I guess I am), but that didn't seem to be what was happening. See #170. I think for me, fil.type was "Microsoft Edge HTML Document)

Proof:

60        For Each fil In fol.Files
Debug.Print "File named " & fil.Name & " is File Type: " & fil.Type

Had to hide the table name since I wasn't using the demo file, but:

But once sorted out, the code works great!!! VERY much appreciated, and sorry we were both pulling our hair out yesterday and this morning!

It might be fil.type "XML File" if I changed the default opening program, but it might also just as likely be "Notepad++XML FIle" and still not work ...

 

[jdraw]

You are correct, I do not check if there are no xml files in the folder. That is an oversight on my part. I will change to do so.

I don't use Edge, I use Firefox so that may be responsible for some differences. Wist FileExplorer when I list files in Documents folder I see the following and FSO/Access recognizes it.

 

[Marshall Brooks]

Edge and Chrome DO tend to monopolize things. I'd just be happy if they would save my PDF files as PDF instead of HTML.

My point is valid though: The file extension will always be ".xml". The file type might be "XML File" or "Microsoft Edge HTML File." or something else.

It's similar to what I get told on here. In some case I have a textbox on a form that was called txtClient. For whatever reason, I wanted a label instead of a text box, but I didn't want to global replace txtClient with lblClient and I wasn't sure I would keep the change, so I changed the textbox to txtClientOld and created a label named txtClient. That works, but it's going to be a pain for anyone else who has to work with it.

Fil.type works on your system, but Fil.Name ends in ".xml" would make the code more portable and save you and me having to try to figure out why it doesn't work for them.

 

[jdraw]

Marshall,

Here is the revised code for DataMacroFiles.

' ----------------------------------------------------------------
' 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
' CheckForNonSystemTables<------new  30-Jan-24
'
' Requires:
' Reference to Microsoft Scripting Runtime
'
'Sample execution in immediate window
' DataMacroFiles "C:\Users\JP\Documents\"check f
'--REVISION:
'Revision:31-Jan-24 added
'check XMLFilesExist -to ensure LoadFromText folder contains file(s) with extension .xml
'
'Revision:30-Jan-24 added
'  CheckForNonSystemTables  -to ensure there were user tables in the database
' ----------------------------------------------------------------
Function DataMacroFiles(Optional strFolder As String)

10        On Error GoTo DataMacroFiles_Error
          
          ' strFolder = "C:\Users\jp\Documents\"  'used for testing
          '        strFolder = "C:\Users\mb\Documents\Access Database Development\DataMacrosTextFiles\"
        
          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 XMLFilesExist As Boolean   'Check if folder has xml file(s)
30        XMLFilesExist = False          'initialize to False
          Dim FSO As FileSystemObject

40        Set FSO = New FileSystemObject
50        Set col = New Collection
60        Set fol = FSO.GetFolder(strFolder)

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

70        For Each fil In fol.Files
80            If fil.Type = "xml File" Then          'datamacros store as xml files when SaveAsText
90                XMLFilesExist = True
100               If InStr(fil.Name, filt) > 0 Then
110                   tblName = Mid(fil.Name, Len(filt) + 1)
120                   tblName = Left(tblName, Len(tblName) - 4)
130                   Debug.Print tblName & String(38 - Len(fil.Name), " ") & fil.Name
140                   col.Add tblName & "|" & fil.Path  'store table name "|" filename in collection
150               End If
160           End If
170       Next
                  'CHECK if there are .xml files (saved data macros) in the folder
                  '      if not, then exit the program
180       If XMLFilesExist = False Then
190           MsgBox "There are no xml files in folder: " & strFolder & vbCrLf & " *** Exiting program  !!!", vbCritical
200           Debug.Print "There are no xml files in folder: " & strFolder & vbCrLf & " *** Exiting program  !!!"
210           Exit Function
220       End If

          'Review NonSystem Tabledefs in this database
          Dim X As Integer
          Dim db As DAO.Database
          Dim tbl As DAO.TableDef
230       Set db = CurrentDb

          'CHECK that there are nonSystem tables in the current database
                
240       If CheckForNonSystemTables Then
              ' Compare xml files and associated tables with Current database Tables
              ' where there is a match, Load the DataMacro from the related xml file
        
         
250           For X = 1 To col.Count                'xml datamacro info is in Col
260               For Each tbl In CurrentDb.TableDefs
270                   If Left(tbl.Name, 4) <> "MSys" And _
                          Left(tbl.Name, 4) <> "USys" Then
280                       If tbl.Name = Left(col(X), InStr(col(X), "|") - 1) Then
290                           Debug.Print "Table " & tbl.Name & " has datamacro Text at " & Mid(col(X), InStr(col(X), "|") + 1)
300                           LoadaDataMacro tbl.Name, Mid(col(X), InStr(col(X), "|") + 1)
310                           Debug.Print "---Macro was loaded from text"
320                       End If
330                   End If
340               Next
350           Next
           
360           MsgBox "Data macros for tables in this database have been LoadedFromText", vbOKOnly
       
370       Else
380           Debug.Print "No user tables exist in this database: " & CurrentDb.Name
390           MsgBox "No user tables exist in this database: " & CurrentDb.Name
400       End If
          
410       On Error GoTo 0
DataMacroFiles_Exit:
420       Exit Function

DataMacroFiles_Error:

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

 

[Marshall Brooks]

Minor point, but it's tripped me up a few times so far.

If you are editing the DM files using the User Interface (Access, not Notepad II), the changes are made similar to AfterUpdate on a form.

i.e. if you change a field, you MUST click enter of click in a different field before saving. If you just paste or type into the field and then click SAVE, Access will save the DM with the old value, not the updated value.

I discovered it two ways:

• I had a change that I knew that I made, but the change was not being saved as I still saw the incorrect value when I ran the DM.
• I had a change that I pasted in to the Set Field block and tried to save the DM and got an error that "The Set Field Item requires a value.", but if I clicked a different field without changing anything, it saved fine.

Hope this helps someone!!!!

 

[Marshall Brooks]

I have some DDL questions now ...

I confused myself reading these links:

Difference between VARCHAR and TEXT in MySQL

When we create a table in MySQL with a VARCHAR column, we have to set the length for it. But for TEXT type we don't have to provide the length. What are the differences between VARCHAR and TEXT?

stackoverflow.com

When to use CHAR, VARCHAR, or VARCHAR(MAX) - Simple Talk

It’s important to choose the right datatypes when designing a database. Greg Larsen explains the differences between CHAR, VARCHAR and VARCHAR(MAX).

www.red-gate.com

In her example database, @Pat Hartman used code like this:

CREATE TABLE [tmp_tblListValues]
(
    [ListValueID]       COUNTER            Primary Key,
    [AuditParmsID]  Long            NOT NULL,
    [ListName]          text(50) NOT NULL,
    [ItemName]          text(50)  NOT NULL,
    [Seqnum]            Integer       NULL,
    [ITOnly]            Byte            NOT NULL,
    [UpdatedBy]         text(20)    NULL,
    [UpdatedDT]         DateTime       NOT NULL,
    [upsize_ts]         timestamp      NULL
)

That seemed to work, and I used similar, but I'm reading that text is supposed to be similar to manually creating the field as long text, i.e it is up to 65536 characters and you can't set a max length - although it does seem to set one.

For some reason (and maybe I just have the structure/syntax wrong, it didn't work when adding new fields to an existing table. I got that to work by using similar to:

ALTER TABLE [MyTable]
Add NewField CHAR(255);

That adds the NewField, but it seems like the length is always padded to 255 characters and it is causing issues.

I'm not sure if it should be "Add NewField text(255);" or "Add NewField VARCHAR(255);" nor whether I should be using TEXT(50) or VARCHAR(50) for the new tables, or does it matter?

Thanks again!!!

text(x) seems to work when adding a field also, so I probably had the syntax wrong before.

 

[Pat Hartman]

This DDL was created a very long time ago and I don't know if the rules have changed but I will tell you that I did not create the DDL. The DDL was generated by SQL Examiner which is a tool that compares two versions of a SQL Server database and generates DDL to make one version = the other. Where possible, I used the exact DDL that was generated for SQL Server in the app I posted but in the app I posted, the DDL is modifying ACE tables and not SQL Server tables. So, there may be subtle differences. I may have had to change the DDL to make it work for Access.

 

[Marshall Brooks]

@Pat Hartman - Thank you!!!

The code that you are using was text(50) or text(255).

That seems to be working. Somehow, that wasn't working for me, and I looked up code that suggested CHAR(255). That works, but it makes the field length AS 255 instead of UP TO 255 characters.

I'm changing all my queries to text(x) and hopefully it will work properly. I should know pretty soon!!!