none
Access 16 - Using Data Macros to Archive Records on Delete RRS feed

  • Question

  • Hi Everybody....is it possible to use a data macro in Access 2016 to archive the data when a user deletes a record? I've tried to use the After Delete Table macro, but that just inserts an empty record into the archive table, I believe because the After Delete event fires after the record has been removed from the table, so there's nothing to copy across to the archive table.  So it looks as if the Before Delete event would be the correct event, as that fires after the user has confirmed delete and just before the record is removed....however, the macro options are more limited in the before delete event and there is not the CreateRecord action available.  Any ideas folks?
    Thursday, October 12, 2017 4:19 PM

Answers

  • Hi, I can't test it right now, but you might be able to use the After Delete event and use [Old].[FieldName] to make sure your archive table is not blank. Hope it helps...
    • Marked as answer by JDubya13 Thursday, October 12, 2017 8:20 PM
    Thursday, October 12, 2017 4:31 PM
  • Hi,

    As far as I know, the changes to the table using data macros will be performed by the back end database engine, so no network traffic "should" occur.

    You will have to experiment on the number of fields and concurrent users to find out the impact on performance as I don't have any data regarding your specific (or any really) situation.

    Good luck!

    • Marked as answer by JDubya13 Friday, October 13, 2017 8:11 PM
    Friday, October 13, 2017 7:39 PM

All replies

  • Hi, I can't test it right now, but you might be able to use the After Delete event and use [Old].[FieldName] to make sure your archive table is not blank. Hope it helps...
    • Marked as answer by JDubya13 Thursday, October 12, 2017 8:20 PM
    Thursday, October 12, 2017 4:31 PM
  • hi theDBguy, yes that worked an absolute treat.  I must admit, I assumed (tsk tsk) that as the record had already been deleted, so too would any old values! We live and learn.  Cheers!
    Thursday, October 12, 2017 8:31 PM
  • Hi,

    You're welcome. I was able to test it afterwards and came to the same conclusion as you. Good luck with your project.

    Thursday, October 12, 2017 8:41 PM
  • Hi .theDBguy

    One other question for you on this.  Ok, so I'm now able to successfully archive a record if a user deletes said record direct from a table, but I would also like to record in the archive table which user deleted the record.  If the user was deleting the record via a Form then this would be easy enough to sort with VBA, but I cant see how I could achieve this using a data macro.  Any ideas?

    Friday, October 13, 2017 9:26 AM
  • Instead of trying to out-guess users (you'll always lose that one), don't let them have access to the tables. Users should only see data in forms. Also they should not be able to deleted records. add a Yes/No field named Active to every table. When a user wants to get rid of a record that new field is marked as No. All forms have record sources that filter out all Active=No records. That way, you have a true audit trail.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, October 13, 2017 2:55 PM
  • I agree with everything you've said there Bill and any databases that I cobble together never allow the end user to interact with data except via Forms and Reports.  However, one of the companies that I help out now and then  have a Database that tends to be administered by a couple of the chiefs and although they mostly stick to accessing the data via the forms, they sometimes feel the need to edit data directly in the tables, seeing as they're so important and all.  Plus they like to run ad hoc update queries and often forget to back the table up first and there have been a few occasions where they've got it wrong and made a mess of things.  So I get a call and repair what I can from the previous night's back-up, but that doesn't help with any legitimate changes that have occurred during the day prior to the "accident"!<o:p></o:p>

    That's why I was looking at Data Macros to see if I could solve the problem by writing any changes to key data in the main data tables to a single Audit table, as well as ensuring that any deleted records were saved to the relevant archive.  So it would be nice to be able to identify the user in both the audit and archive tables, but it's not essential.  

    What i will say is that using Data Macros for this purpose is really cumbersome and due to the restricted functionality, I've had to repeat many actions that I would have been able to handle in VBA with a simple nested loop......grrrrrr!

    Cheers, John




    • Edited by JDubya13 Friday, October 13, 2017 5:14 PM Forgot to sign off
    Friday, October 13, 2017 5:13 PM
  • Hi John,

    Data macros use the database engine (ACE); whereas, VBA uses something else. Anyway, the point being is the only available command in ACE for the data macro is the CurrentUser() function. However, without user-level security (ULS), all you get is "Admin," which is the default user.

    One possible workaround, is to force the users to use the front end to access the tables wherein you make sure a VBA code can run to store the person's username in a table for the "current" user. You might be able to use this information in your data macros.

    Just a thought...


    • Edited by .theDBguy Friday, October 13, 2017 5:25 PM
    Friday, October 13, 2017 5:25 PM
  • Hi .theDBguy

    I did consider that solution but I couldn't work out how the user info gets into the Audit table.  I can get the user info into a separate table called tblUser no problem, but how do I use the After Update data macro, attached to the data table, to update the audit table with the user details from the User table when the macro throws across details of changes to the data table??

    Friday, October 13, 2017 6:14 PM
  • Hi,

    I was able to use LookupRecord to assign the current user data from a separate table using the following AfterUpdate data macro.

    Hope it helps...

    Friday, October 13, 2017 6:57 PM
  • Cheers .theDBguy does exactly what i need it to do!

    In general with Data Macros, would I be right in thinking that as they are attached to the tables, and if said tables are in the back end of a split database, then all this additional processing will not be dragged over the network?

    Also are you aware of any issues that might arise if I have data macros writing to an audit table at every field edit in a data table, if there are 10 to 20 users making edits to the table at any moment or is Access robust enough to handle this?

    Friday, October 13, 2017 7:30 PM
  • Hi,

    As far as I know, the changes to the table using data macros will be performed by the back end database engine, so no network traffic "should" occur.

    You will have to experiment on the number of fields and concurrent users to find out the impact on performance as I don't have any data regarding your specific (or any really) situation.

    Good luck!

    • Marked as answer by JDubya13 Friday, October 13, 2017 8:11 PM
    Friday, October 13, 2017 7:39 PM
  • Thanks very much for all your help and advice on this thread, it's much appreciated!

    Cheers, John.

    Friday, October 13, 2017 8:14 PM
  • Thanks very much for all your help and advice on this thread, it's much appreciated!

    Cheers, John.

    Hi John,

    You're welcome. Glad we could assist. Good luck with your project.

    Friday, October 13, 2017 8:23 PM