Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: record backup feature

    I want to copy whole records from one table to a backup table whenever the actual record is being edited and when it is created the first time.

    I am thinking about creating an empty table with the same fields, adding a separate index, changing the original autonumber field to number, and adding user name and date now fields so that the changes can be traced.

    On the form, I guess the after update property is the desired one , but how should the code be?

    Moreover, is it difficult to implement "roll-back functionality to a table in Access? (after several other changes, pick a certain record for "restore").

  2. #2
    Join Date
    Dec 2003
    Posts
    172
    a big question with lots of options for sure. depends on the purpose of what you are trying to accomplish...

    case #1
    you want a backup of a currently edited record in case the user screws up.

    for this situation you want to be able to restore the original record in the underlying table. in this case you might consider using the Form.Undo event to roll back the changes, or create a temporary editing environment by copying the good data to another table somewhere and once the data is verified posting that data back to the main table.

    case #2
    you want an audit trail of changes to a customer profile.

    a lot of people don't realize they are losing data from their database on a daily basis when they make a change to a customer record and overwrite one of more fields.

    for example, if a customer's preference was to buy red widgets and now you edit their profile because they like to buy blue widgets, the history of the customer liking red widgets would be gone forever.

    for this situation you would want to create a backup or historical table of customer profile information. perhaps something like: starting and ending dates and customer likes and dislikes.

    then you could run a report/query to pull, by date range, customer preferences so you could do an evaluation of how your customer's needs have changed over a period of time.

    case #3
    you are concerned about transactions not making it through (as in credit card orders) or you may want to roll back a previous transaction for some reason (like an order was cancelled or entered improperly).

    for this scenario you might consider using transaction processing in Access in which you can process table entries one by one, and if needed, in some cases you could roll back/cancel these transactions.

    case #4
    if you are concerned about users' inadvertently screwing up form data.

    the solution here is that you can make the form read only by locking some or all of the controls or making the form allow-edits property=false.

    case #5
    sometimes we want a backup just for the sake of covering our... assets. maybe you might consider a query or export method that copies one or more records to an archive database or table. it could be keyed from a backup menu option.

    case #6
    but if you want to copy only the current record in the form to a new table you have two ways to do this:

    if you want to back up the original record before it's been changed, use the current event of the form which fires when the user navigates to a record. you can open a recordset of the table where you are storing backups and just write out all the values of the form controls to each .Field() of the recordset using addnew and update. you could also use an append query, keyed to the current record id (whatever you determine this should be), and fire this off each time the current event occurs.

    if you want to back up the data once it's been changed by the user, then you use the after-update event in the form and again, you can use the recordset addnew and update method to write data to the backup table, or use an append query to write the current record id.

    the current record id should be something that never changes. you could use the autonumber but this number may not be reliable at all times. i have seen instances where autonumber does not remain the same for any given account. i would consider using a customer account number or social security number or something that is verifiable and unchanging.

    as far as timestamping you can use the Now() and/or Date() functions to add this information to your backup table data.

    if you want a sample of the code for writing out the records to a recordset using addnew/update or want a query to accomplish this task for the current record, post the request here.

    hope this helps in your mission

    joeg
    Last edited by JoeG; 01-03-04 at 12:08.

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by JoeG
    if you want a sample of the code for writing out the records to a recordset using addnew/update or want a query to accomplish this task for the current record, post the request here.
    Yes, please, some code/example would be great.

    The following extract from your reply is relevant:
    - want to be able to restore the original record
    - backup: query or export method to an archive table, for example a linked table stored in another database on a different PC.
    - audit trail of changes, create a backup or historical table

    Maybe (too much work? See below.):
    - want to roll back a previous transaction for some reason (like an order was cancelled or entered improperly), consider transaction processing in which you can process table entries 1 by 1

    I think - apart from technical curiosity about the options in general :-) - that what I need is to use a history table. Every time a change happens to a record, I want that whole record ADDED to the history table with timestamp and username logged. The roll-back is where I am a bit uncertain. As it will not be critical to have it automated, it may be sufficient to Just get hold of the data and add them manually again if needed. Moreover, since I will be using AutoNumber, will it be possible to "roll back" using a ID that has previously been deleted from the original table?

    kedaniel

  4. #4
    Join Date
    Dec 2003
    Posts
    172
    Hi Kedaniel,

    I'd be happy to help you. Unfortunately, I'm leaving now for an all day trip and won't be at the hotel (and Internet access) until tonight. If your question is still open and you want me to help you, send me a private message. I will also log in and see if you've gotten any posts since this morning.

    Talk to you soon.

    JoeG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •