Audit Table/ Log Help (1 Viewer)

[BWP]

Hi All

I have a audit log setup using the following code and really just need some help working out if this is the best thing for my needs...and if i can speed this up in anyway....:

Sub AuditChanges(IDField As String, UserAction As String)
  On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")


MainFormFocus:
            Select Case UserAction
                  Case "EDIT"
                      For Each ctl In MainForm.Controls   'Screen.ActiveForm.Controls
                          If ctl.Tag = "Audit" Then
                              If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                                  With rst
                                      .AddNew
                                      ![DateTime] = datTimeCheck
                                      ![UserName] = strUserID
                                      ![FormName] = MainForm.Name    'Screen.ActiveForm.Name
                                      ![Action] = UserAction
                                      ![RecordID] = MainForm.Controls(IDField).Value   'Screen.ActiveForm.Controls(IDField).Value
                                      ![FieldName] = ctl.ControlSource
                                      ![OldValue] = ctl.OldValue
                                      ![NewValue] = ctl.Value
                                      ![RiskID] = RiskNum
                                      
                                        If ctl.ControlType = 111 Or ctl.ControlType = 110 Then
                                            If ![FieldName] = "RiskTeamWorkstream" Or ![FieldName] = "RiskSubTeam" Or ![FieldName] = "RiskTeamThree" Then
                                                    ![lookupValNEW] = ctl.Column(2)
                                                Else
                                                    ![lookupValNEW] = ctl.Column(1)
                                            End If
                                              
                                        Else
                                            ![lookupValNEW] = ctl.Value
                                        End If
                                      
                                      .Update
                                  End With
                              End If
                          End If
                      Next ctl
                  Case Else
                      With rst
                          .AddNew
                          ![DateTime] = datTimeCheck
                          ![UserName] = strUserID
                          ![FormName] = MainForm.Name     'Screen.ActiveForm.Name
                          ![Action] = UserAction
                          ![RecordID] = MainForm.Controls(IDField).Value     'Screen.ActiveForm.Controls(IDField).Value
                          ![RiskID] = RiskNum
                          .Update
                      End With
              End Select

NextSt:
    
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    'MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

My main issue is when it gets here in the code - i.e. to update the record with the changes:

.Update

My BE is SharePoint 2010 lists...the update for one simple change in Access (using Access BE) takes a split second, but when the BE is SharePoint - it takes about 10-15 seconds (too long in my opinion) - is there something i can do to speed this up?

I also have a question about this line of code:

rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic

- it seems it is opening up ALL records in the table - if so why - should it not simply be up adding one new row to the table?

Thanks in advance

 

[1268]

Hi All

I have a audit log setup using the following code and really just need some help working out if this is the best thing for my needs...and if i can speed this up in anyway....:

Sub AuditChanges(IDField As String, UserAction As String)
  On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")


MainFormFocus:
            Select Case UserAction
                  Case "EDIT"
                      For Each ctl In MainForm.Controls   'Screen.ActiveForm.Controls
                          If ctl.Tag = "Audit" Then
                              If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                                  With rst
                                      .AddNew
                                      ![DateTime] = datTimeCheck
                                      ![UserName] = strUserID
                                      ![FormName] = MainForm.Name    'Screen.ActiveForm.Name
                                      ![Action] = UserAction
                                      ![RecordID] = MainForm.Controls(IDField).Value   'Screen.ActiveForm.Controls(IDField).Value
                                      ![FieldName] = ctl.ControlSource
                                      ![OldValue] = ctl.OldValue
                                      ![NewValue] = ctl.Value
                                      ![RiskID] = RiskNum
                                      
                                        If ctl.ControlType = 111 Or ctl.ControlType = 110 Then
                                            If ![FieldName] = "RiskTeamWorkstream" Or ![FieldName] = "RiskSubTeam" Or ![FieldName] = "RiskTeamThree" Then
                                                    ![lookupValNEW] = ctl.Column(2)
                                                Else
                                                    ![lookupValNEW] = ctl.Column(1)
                                            End If
                                              
                                        Else
                                            ![lookupValNEW] = ctl.Value
                                        End If
                                      
                                      .Update
                                  End With
                              End If
                          End If
                      Next ctl
                  Case Else
                      With rst
                          .AddNew
                          ![DateTime] = datTimeCheck
                          ![UserName] = strUserID
                          ![FormName] = MainForm.Name     'Screen.ActiveForm.Name
                          ![Action] = UserAction
                          ![RecordID] = MainForm.Controls(IDField).Value     'Screen.ActiveForm.Controls(IDField).Value
                          ![RiskID] = RiskNum
                          .Update
                      End With
              End Select

NextSt:
    
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    'MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

My main issue is when it gets here in the code - i.e. to update the record with the changes:


My BE is SharePoint 2010 lists...the update for one simple change in Access (using Access BE) takes a split second, but when the BE is SharePoint - it takes about 10-15 seconds (too long in my opinion) - is there something i can do to speed this up?

I also have a question about this line of code:

rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic

- it seems it is opening up ALL records in the table - if so why - should it not simply be up adding one new row to the table?

Thanks in advance

Share point is just slow imo. Use and append sql to only add a single record.

Sent from my SM-G950U using Tapatalk

 

[BWP]

So what kind of work would be required to change that from what it is now to an append SQL query? Do I just get rid of all the recordset stuff?

And any idea what this is doing:

rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic

And I absolutely agree that SP is not the best means, but it is actually my only hope at present...

 

[NauticalGent]

I use SharePoint as a BE as well. Really no way to actually speed things up with your code - your connection speed is dependent on your infrastructure.However, you can give the appearance of speed by using the "Use the Cache format that is compatible with Microsoft Access 2010 and later" option.Also, just for curiosity sake, is there a particular reason you are using ADO and not DAO?

 

[BWP]

No reason - is ADO better/faster and if so - how do i change it?

-> and can you expand on the "use cache format"? - is this under backstage/options/current database?

 

[NauticalGent]

I really do not know if in this case one would be faster than the other. I do know that the Jet engine is designed with DAO in mind. From the AC2010 Developer's Handbook:

WHY USE DAO?Applications written in other programming languages, such as Visual Basic.NET, C#, and the like,must explicitly connect to the data source they intend to manipulate. That’s because, unlike Access,these environments do not have an inherent connection to the data source. When used in Access, DAOenables you to manipulate data and schema through an implicit connection that Access maintains towhichever Access database engine, ODBC, or ISAM data source it happens to be connected to.DAO has evolved right alongside Jet and the Access database engine, and is often the best modelfor accessing and manipulating Access database engine objects and structure. Because of its tightintegration with Access, DAO also provides better access to Access databases than ADO or the JetReplication Objects (JRO). Here are some of the advantages of using DAO:‰ ADO connections can be applied to only one database at a time, whereas DAO enables youto link (connect) to multiple databases simultaneously.‰ Using the OpenRecordset method’s dbDenyWrite option, DAO enables you to open a tablewhile preventing other users from opening the same table with write access. The ADOConnection object’s adModeShareDenyWrite constant operates at connection level — not attable level.‰ Using the OpenRecordset method’s dbDenyRead option, DAO enables you to open a tablewhile preventing other users from opening the table at all. The ADO Connection object’sadModeShareDenyRead constant can only be set at connection level.‰ You can dynamically link an updatable ODBC table in DAO, but not in ADO.‰ DAO enables you to create replica databases that prevent users from deleting records;JRO does not.‰ In DAO, you can return information about Exchange and Outlook folders and columnsusing the TableDef and Field Attributes properties. ADO does not pass this informationthrough to Access.New Features in DAO x 357‰ Using the DBEngine’s GetOption and SetOption methods, DAO enables you to set andchange Access database engine options without requiring you to make Registry changes.‰ DAO enables you to create, change, and delete custom database properties.‰ You can force the database-locking mode with the DAO.LockTypeEnum constants againstCurrentDb, but you can’t do the same thing in ADO using ADO.LockTypeEnum againstCurrentProject.Connection.‰ DAO enables you to run a separate session of the Access database engine, usingPrivDBEngine; ADO does not.‰ DAO enables you to create multi-value lookup fi elds using new complex data types. A multivaluelookup fi eld is a single fi eld that can store multiple values in an embedded Recordset.You explore this new fi eld type in more detail later in this chapter.‰ DAO enables you to create and insert data in an Attachment fi eld. Attachment fi elds are anew data type in the Access database engine and will be examined in more detail later in thischapter.The current version of DAO is a very mature, well-documented, and easy-to-use Object Model foraccessing database services. You can use DAO from any VBA environment such as Word, Excel, andso on, and a variety of other programming languages such as Visual Basic, C++, and even managedlanguages such as C#.Finally, it’s fairly safe to say that DAO will be around as long as Access or Jet databases are used.

 

[BWP]

Thanks - but how do i change what i have to use DAO instead?

Also - re: cache format - can you let me know more about that?

 

[NauticalGent]

The cache option is a check box you can find by navigating to the Current Database section. Give this a read:https://social.msdn.microsoft.com/F...ache-options-with-access-2010?forum=accessdev

 

[BWP]

ok thanks - appreciate it...

Any idea on other question getting it changed from ADO to DAO?

 

[The_Doc_Man]

How do you speed up what you are doing? It depends on what you WANTED to do. With audit logging, you have at least two major kinds of logs.

1. The "reconstructive" logs - that allow you to see every change to every field in the database. You use this to somehow "roll back" your records to a given date/state. Since Access won't automatically do this, it means you would have to write some code to achieve this goal.

It is possible for you to write code to do this kind of logging, but take a good look at what you are writing! You create one new audit record per tagged control and store its value. That's a LOT of data. But will you ever use it? (Rhetorical question.)

2. The "forensic" logs -that allow you to know who touched the tables (and when), perhaps know what form they used, and depending on how deep your analysis would be, know specifically (by PK, perhaps) what record they touched.

It is possible to do this easily enough, but I think you are looking at something perhaps a bit TOO generic. If your changes are done via forms, then drop a call into each form that makes a form-specific log for you because at the moment just before the Form_Update event, your form "knows" everything that was done. You could certainly call a particular logging support routine to fill in time/date, but the form can provide several arguments to define what should appear in the log.

Why does it take so long? Well, for one thing, multiple detailed reconstruction logs for every change means multiple BE updates. How many controls are on this form that you would audit? (Again, food for thought and not a criticism.)

I'll offer this thought as well, though you don't actually have this choice based on your description. Reconstructive audits are done commonly using products like ORACLE and SQL Server and SYBASE and quite a few more. But there, it is built in to the BE Engine. With Access, you have to "roll your own" audit. I'm not saying "do it" or "don't" but the point is, when this was designed, the question of auditing should have been investigated. A passive BE means "roll your own." So part of your decision process is to look at what KIND of auditing you need and let that determine your course of action.

 

[BWP]

Thanks - so let's assume the way i have it now is what the requirements are - i.e. every change to any field (not every single field, but around 8/10 fields (the form has roughly 15-20 controls).

I've gone through the code step-by-step and the only place it "lags" is when it gets to

.Update

line...?

 

[Minty]

How up to date do they need to be - You could write the audit logs to a local table.
Then you could simply update them every time a form or menu was closed, and check on db opening if they had been uploaded in case of a crash and save them then if not already saved?

You would need some unique ID per User / Event to ensure no duplication. A time stamp would proabably suffice.

 

[BWP]

Minty - i really like that idea...

Wouldn't the time it takes to update from local to SharePoint (when form closes) still take the same time?

 

[The_Doc_Man]

While there can be issues of "crashes" where you would lose data, the idea of having a local "temporary" table that accumulates "stuff" and then bulk writes it would be simpler since you could use an SQL statement like

INSERT INTO AuditLog (...list of fields...) SELECT ...list of fields ... FROM LocalAudit ;

followed by a

DELETE * FROM LocalAudit ;

(to prevent double-dipping.) Using a multi-row INSERT INTO means that when you are doing your update, you are doing it wholesale rather piecemeal. Whatever speed you have between FE and BE, I can assure you that using a single SQL statement implying multiple records to be inserted at once is faster than multiple recordset.Update calls, each for a single audit record.

 

[BWP]

OK - so that I understand this correctly:

StaffID: 1
[Name]:"John Doe" and [DOB]: "08/08/1945" = old values.
[Name]:"John Doney" and [DOB]: "08/09/1945" = new values.

in my current setup - before update event will write old value and new value in two new records in auditTable.

---update takes 15 seconds---


If i adopt the local table option: the same data and process does exactly the same, except that the data is written to a temp local table "tempAuditTable" (essentially a copy of the SharePoint auditTable).

When the form closes or on the on-click event of the save button - i docmd.runsql "insert into auditTable all values from tempAuditTable"?

If i got that right - i am just thinking will it not take the same amount of time (of course i can try - but like to hear from people here before i spend another 4 hours changing things around)...

 

[Minty]

As the DocMan suggested, this will be a one time run and depending on your needs could be made to run only when the database is closed.

So you would only have the wait once per run of the application.

Also the running of a single simple insert query will probably be a lot quicker than the recordset updating method you are currently using.

EDIT - Run a test - create a dummy single record insert query and run it. (In the query editor don't mess with all that ADO gubbins.)

 

[NauticalGent]

ok thanks - appreciate it...

Any idea on other question getting it changed from ADO to DAO?

Sorry, I was unable to get back to you for so long, but it seems you are in good hands.

As far as your question goes, I really do not see a reason to convert - no value added.

I DO think the lag is that you are opening a large(?) recordset for the sole reason of adding a new record.

The cache feature is supposed to negate the performance hit of opening potentially large recordsets.

But...

As 1268(post #2) alluded to, if all you are doing is adding a record, a parameterized append query would do the trick much more quickly and efficiently.

 

[Minty]

As another thought with regard to the size of the recordset you should be using

rst.Open "SELECT * FROM tblAuditTrail Where YourPrimaryKey= 0", cnn, adOpenDynamic, adLockOptimistic

To open a "empty" recordset to then add a new record to.

 

[NauticalGent]

As another thought with regard to the size of the recordset you should be using

rst.Open "SELECT * FROM tblAuditTrail Where YourPrimaryKey= 0", cnn, adOpenDynamic, adLockOptimistic

To open a "empty" recordset to then add a new record to.

Brilliant actually...

 

[BWP]

As the DocMan suggested, this will be a one time run and depending on your needs could be made to run only when the database is closed.

So you would only have the wait once per run of the application.

Also the running of a single simple insert query will probably be a lot quicker than the recordset updating method you are currently using.

EDIT - Run a test - create a dummy single record insert query and run it. (In the query editor don't mess with all that ADO gubbins.)

So i tried this and honestly - very little change. I can hardly notice the difference. Both occurrences took roughly 5 seconds to update (so i'm guessing this has to do with the server/architecture setup of the SP or something)...