Most effective method to add multiple records to table (1 Viewer)

Kayleigh

Member
Local time
Today, 15:19
Joined
Sep 24, 2020
Messages
706
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

AWF VIP
Local time
Today, 15:19
Joined
Jul 26, 2013
Messages
10,354
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

Member
Local time
Today, 15:19
Joined
Sep 24, 2020
Messages
706
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

AWF VIP
Local time
Today, 15:19
Joined
Jul 26, 2013
Messages
10,354
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

Member
Local time
Today, 15:19
Joined
Sep 24, 2020
Messages
706
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?
 

Kayleigh

Member
Local time
Today, 15:19
Joined
Sep 24, 2020
Messages
706
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

Super Moderator
Staff member
Local time
Today, 11:19
Joined
Feb 19, 2002
Messages
42,970
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

Active member
Local time
Today, 15:19
Joined
Dec 31, 2020
Messages
521
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

AWF VIP
Local time
Today, 15:19
Joined
Jul 26, 2013
Messages
10,354
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

Super Moderator
Staff member
Local time
Today, 11:19
Joined
Jan 23, 2006
Messages
15,364
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..
 

Users who are viewing this thread

Top Bottom