Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting records in A2K (A2K SR1)

    If I delete a reord in dBase-compatible databases/tables, the records isn't really zapped but only marked for deletion, and I can undelete it later if I need to. In A2K, a record/row deleted is lost permanently unless I wrapped it up in a transaction which will be lost too if I terminate the transaction. It is only good for rollback while the transaction is in process. Do you think it a good idea if I created a deletion marker field ala dBase so that I can use recordsets that aren't marked for deletion? Is there a better method to keep deleted records for later retrieval if the need arises?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting records in A2K (A2K SR1)

    My understanding was in rdbms, once you did the delete, and then the commit, the data is gone. Ok in Access the data is still in the mdb file, until you do a compact, but I don't know of any 'easy' way to get it back. If you want users to have the option to "delete", but not actually delete the record, then your suggestion of a deletion marker seems fine. Maybe have the users look at the data using a query with a check "deletion maker = False"
    You could even do a move operation, and append the "deleted" records to another table

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting records in A2K (A2K SR1)

    Here is my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>. I'm curious myself what others will say.

    If you want to keep old records, using a marker is a good idea in itself. It's fairly easy to build queries that filter out "deleted" records and use these for forms and reports. And it's easy to restore a deleted record.

    I have used the idea of a deletion marker in databases where the number of deleted records is relatively small.
    I use 3 fields for this:
    <UL><LI>A boolean field Deleted.
    <LI>A string field that holds the name of the user who marked the record for deletion.
    <LI>A date/time field that holds the date and time the record was marked.[/list]If the usage of your database involves adding and deleting lots of records all the time, the database might grow too much and performance might deteriorate if you just mark records.

    Alternatives might be to migrate records to a separate "Deleted records" table - in the same database or in a separate database. You must take special care with parent-child relations.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Deleting records in A2K (A2K SR1)

    FWIW, we use a "deleted" marker in records if there is any question about ever wanting to restore things, and we set security such that users cannot actually do the delete of a record. It takes a pretty large table to see any significant performance impact, so we don't normally worry about that. And if we do get a really large table, we normally move it to SQL Server where we have some other tools. In that case, we typically set up an archiving process so we can see a complete history of who has changed a record, where it was changed, and when. In some cases we force a user to give us a reason for the change as well. In that case the archive table can contains multiple copies of a given record, so it can get really large. But with storage being inexpensive, it's a great tool for users and for managing user training or performance problems.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting records in A2K (A2K SR1)

    In dBase, etc., there was still a chance to retrieve deleted stuff until the database had been packed. In Access, compacting removes temporary objects created by the application and otherwise reclaims resources, as well as permanently deleting objects you have "deleted". The process is very different from an xBase pack.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting records in A2K (A2K SR1)

    I generally don't bother with the date and user fields, but the Deleted field is a pretty standard method for enabling "deletions" while allowing the user to have second thoughts. Of course, you have to take extra steps to make sure they can't really delete a record from a form, and there's no way to do that in a table or query except by use of Access security.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting records in A2K (A2K SR1)

    Mostly, storing the user name and date of deletion is not very important. But it can be very handy at times. In one of my databases, "standard" users have the right to delete records (by using a custom command button that issues dire warnings etc.), but can't see or restore "deleted" records themselves. A user came complaining to me that Access had deleted a record spontaneously. She was absolutely certain that she hadn't deleted it herself. But the record proved that she had - three days earlier. I would have had a hard time convincing her without having the name, date and time.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Deleting records in A2K (A2K SR1)

    That's one of the very reasons we track user name, date and so forth. In particular, we had problems with people editing the address for the wrong person, and by doing archiving, we were able to spot a problem person, and we were also able to restore the correct data. It can be a resource hog, but it sure is useful on occasion.
    Wendell

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting records in A2K (A2K SR1)

    I created an application that tracks employee vacation. I use a status field to keep track of whether they are active terminated or deleted. When the employee leaves the firm their status changes to terminated. After two years their status changes to deleted and they no longer appear on the form. This is in case they are rehired and they want there old accrual levels. The back end is on SQL server so storage is not an issue and there are not huge amounts of records.

  10. #10
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting records in A2K (A2K SR1)

    This is something I've been wanting to implement in my time tracking application. I get calls from users on occasion that the application mysteriously changed something and I expect it is a training issue. It would be great to be able to go into a table and see exactly what was changed by whom etc. Can you share any more info on the subject? Or should I make this a separate post?

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Deleting records in A2K (A2K SR1)

    If you are using SQL Server as the back-end of your database, triggers are the slickest way to do this kind of thing. They operate independent of the front-end, and are executed (if they exist) each time a record is inserted, changed or deleted. The details of writing triggers are beyond the scope of this forum, but you can pick up any recent SQL Server book and find out more about them.

    If, on the other hand, you are running just Access, it's far more difficult. In Access, you would need to run BeforeUpdate events on the form modifying a record, and be sure you prevented users from getting at data at the table level. Given the level of difficulty, we don't do that much in Access back-ends. In those cases, we typically use the BeforeUpdate event to set who changed the record and when at the form level. Hope this helps.
    Wendell

  12. #12
    Autumn
    Guest

    Re: Deleting records in A2K (A2K SR1)

    I have decided to add a boolean field in the table and another field that keeps the date the record is deleted, and move the record marked for deletion into another linked table. Whenever I want to retrieve a deleted record, I will just go to the table containing the deleted records and move it back to my main table. Thanks for all the advice and discussion.

  13. #13
    Autumn
    Guest

    Re: Deleting records in A2K (A2K SR1)

    Charlotte you have mentioned something which I wanted to know for some time.

    In dBase/Clipper, I can zap or pack the records within the program, as the data is separated from the codes. In A2K, you can compact a linked table, but you can't compact the main database within the database itself. I have to exit to Access main window to do that. Am I right?

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting records in A2K (A2K SR1)

    There is a problem with that plan if you use autonumber keys. If you remove records at the end of the table and then compact the database, the autonumber gets reset to the next highest number, which may already belong to one of the records you moved.
    Charlotte

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting records in A2K (A2K SR1)

    In Access, compact applies to the entire mdb, not just to a table or even all tables. Compacting a database may also get rid of oddities in code, and it's the first thing to try if a database starts misbehaving. If by your question you mean that you have to close the database in order to compact it, that isn't completely true. You can use the Database Utilities menu to compact the currently open database, although many, if not most, books recommend the other method. I've never had any problem with compacting from the open database from the menu though. Does that answer your question or did you mean something else?
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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