Audit Table/ Log Help (1 Viewer)

[BWP]

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.

This also shows no difference to the speed...

--maybe it will when other users are online...will have to check tomorrow...

 

[The_Doc_Man]

As far as speed goes, if you are not writing a lot of data, it might be difficult to see any difference. However, using the INSERT INTO bulk update is "atomic" in the sense that SQL will treat it as a single transaction rather than that loop across all audit-tagged controls, which is however many controls are tagged. The overhead of a single transaction is always less than the overhead of multiple transactions.

On the other hand, if there is no obvious speed difference, then the problem may be in the speed of your network or the speed of SharePoint or even the physical speed of the disk device to which you are writing (though honestly, I doubt that one.) For the speed of SharePoint, I defer to the Gent. At my shop before I retired, we were just beginning to activate some SharePoint apps so I didn't get to play with them.

 

[Mark_]

BWP,

If time is the issue, your audit file could contain a STRING field to hold the audit information rather than using fields.
When you open the form you would have a variable for the Form to store changes

DIM asLogText As String

In each field with .audit you would write changes to the log

asLogText = vbCrLf & ctl.ControlSource & ": " & ctl.OldValue & " -> " & ctl.Value

When you are ready to save the record that is when you write ONE audit record holding who made changes and the contents of asLogText.

That way when you look at the audit file you can see EACH field that was changed in the order they were changed.

Hopefully it will reduce your processing time enough to make it useful.

 

[NauticalGent]

BWP,

With the test you have done, I agree with your assessment that it is the actual update of the table/SP List that is the culprit.

I would suggest running an append query from the immediate window - the data could be anything you can delete later - and see how it performs. If you are pleased with the performance then we could move on to test a parameter query. It is very similar to Doc’s suggestion about an Insert Into SQL statement.

Edit: I didn’t see the posts before where you have tried this already before I posted.