Most effective method to add multiple records to table

[Kayleigh]

I am looking to keep a detailed record of all records which have been input/edit/deleted in VBA code.
One approach I considered is creating an array of the field names and another array of the values and iterating these arrays to call the audit function. However this would mean that I would need to add considerable extra code to all VBA functions.
Are there any more effective methods to achieve this goal?

 

[Minty]

Have a search here for Audit Trail - it's a very common topic.
Or are you trying to keep track of records added via code ? e.g. an db.execute "UPDATE myTable ..."

 

[Kayleigh]

I have a comprehensive audit procedure on the before update of every form but it would not catch records added programmatically.
Using an Update SQL statement would mean that it would need to be executed after every field is add/changed. Some tables have a considerable number of fields so was looking for a solution to save lines of code.

 

[Minty]

The only reliable way to capture changes made through code would be a Data macro applied to the table, or a load of code on each procedure.

It sounds an odd request though - the code will only run when called, so why not simply record that the code has been run with a timedate stamp and a user ID.

 

[Kayleigh]

We use the audit trail quite frequently and often search for particular value. Since each value is in a unique record, it makes it much easier to do.
Can you tell me more about data macros?

 

[Minty]

I've not used them as most of my development is with SQL/Azure backends for the data.
This thread may help you https://www.access-programmers.co.u...h-data-macro-and-user-login-from-form.312314/

 

[Kayleigh]

Thanks I have looked into this option in detail. However we are shortly moving onto SQL server. Correct me if I'm wrong but I don't believe data macros are supported in the same way as ACE database. So I'm looking for a more code-friendly approach.

 

[Pat Hartman]

Data macros are the ACE version of triggers. You would need to convert your data macros manually. I don't think there is any automatic conversion but then I've never used data macros so that is just a guess.

SQL Server et al keep change logs. They are complicated but they don't require code. So, if you learn how to use the logs, you can eliminate some of the application code that duplicates the built in feature.

 

[Cotswold]

Hi Krayna, Personally, I'd add three fields, either Y/N or a Byte which you'd set to 1 from zero.
One for each Input, Edit Delete. Then when the users completes one of the options, record it in
the appropriate field. I suppose you could have an Integer instead of Byte for Edit and increment
it each time. I'm not sure why about Input, it that Add? If so the number of records in the table
will give you that, so I don't just see why you need that. If it's there, then it has been added.

As far as the Delete is concerned, I often retain the deleted records but hide them from the user.
The standard user access to the BE data is filtered with all deletes hidden. I'll also record who did
the delete, which can be a useful check on fraud in some applications. The user is usually unaware
that when they delete it isn't actually deleted. The fact that deleted records can be viewed I only
make available to management. When you do deletes make sure you don't leave orphaned records.

You can then run a simple query on the table counting the numbers of each of these fields.

In the olden days, I'd reuse deleted records to save space.

 

[Minty]

Thanks I have looked into this option in detail. However we are shortly moving onto SQL server. Correct me if I'm wrong but I don't believe data macros are supported in the same way as ACE database. So I'm looking for a more code-friendly approach.

As @Pat Hartman suggested, if you are moving to SQL server it can pretty much do all of this for you without masses of code overhead.

 

[jdraw]

There is an example of using data macros for audit log in this thread. But, as has been stated, there is no way to convert them to vba. As Pat and Minty have said, SQL Server (and others) have logging.
There are examples of using vba and forms to do "partial logging" in Access, but the Martin Green version (often mentioned) has some issues---- reporting wrong record number, not handling subforms..