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?
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
Deleting records in A2K (A2K SR1)
Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Deleting records in A2K (A2K SR1)
- This topic has 18 replies, 6 voices, and was last updated 22 years, 10 months ago.
Viewing 5 reply threadsAuthorReplies-
WSsculshaw
AskWoody LoungerJuly 19, 2002 at 11:56 am #602180My 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 -
WScharlotte
AskWoody LoungerJuly 19, 2002 at 2:26 pm #602264In 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.
-
-
WSHansV
AskWoody LoungerJuly 19, 2002 at 12:07 pm #602186Here is my
. 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:- A boolean field Deleted.
- A string field that holds the name of the user who marked the record for deletion.
- 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.
-
WScharlotte
AskWoody LoungerJuly 19, 2002 at 2:32 pm #602268I 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.
-
WSHansV
AskWoody LoungerJuly 19, 2002 at 2:40 pm #602272Mostly, 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.
-
WBell
AskWoody_MVPJuly 19, 2002 at 7:34 pm #602360That’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.
-
-
-
-
WBell
AskWoody_MVPJuly 19, 2002 at 1:34 pm #602221FWIW, 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.
-
WSPaulK
AskWoody LoungerJuly 19, 2002 at 8:17 pm #602374This 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?
-
WBell
AskWoody_MVPJuly 20, 2002 at 2:55 pm #602464If 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.
-
WSPaulK
AskWoody Lounger -
WScharlotte
AskWoody Lounger
-
-
-
-
-
WSPaulK
AskWoody LoungerJuly 19, 2002 at 8:12 pm #602372I 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.
-
WScharlotte
AskWoody Lounger -
WSlesoch
AskWoody LoungerJuly 23, 2002 at 1:37 am #602950I am under the impression that autonumber once given, is given, even if a record with that autonumber is removed, leaving gap in the autonumbering. I do use autonumber in my database. If I have a 100 records with the same sequential autonumber, and I removed record number 50 (also autonumber 50), the autonumber 50 will not be issued again. Or does it affect only the highest autonumber, meaning the 100th reord? If I remove record with autonumber 100, compact the database, then the first new record wii have autonumber 100. Is it what you said?
If I use autonumber as my row ID, but keep the records marked with deletion in the same table as the main one, then there will not be any problem?
-
WScharlotte
AskWoody LoungerJuly 23, 2002 at 2:06 am #602961If you remove the last record or records from the table and then compact the database, those number at the end will be reused. You could easily wind up with a record 100 in your “deleted” table and a different record 100 in your active table. If you simply mark the records as deleted using a Yes/No field and leave them in their original table, you won’t have any autonumber problems.
-
-
-
WScharlotte
AskWoody LoungerJuly 22, 2002 at 2:53 am #602707In 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?
-
WSlesoch
AskWoody LoungerJuly 23, 2002 at 1:44 am #602953 -
WScharlotte
AskWoody Lounger
-
-
Viewing 5 reply threads -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Windows 11 Insider Preview build 27863 released to Canary
by
joep517
8 hours, 19 minutes ago -
Windows 11 Insider Preview build 26120.4161 (24H2) released to BETA
by
joep517
8 hours, 20 minutes ago -
AI model turns to blackmail when engineers try to take it offline
by
Cybertooth
1 hour, 4 minutes ago -
Migrate off MS365 to Apple Products
by
dmt_3904
4 hours, 29 minutes ago -
Login screen icon
by
CWBillow
2 hours, 47 minutes ago -
AI coming to everything
by
Susan Bradley
8 hours, 29 minutes ago -
Mozilla : Pocket shuts down July 8, 2025, Fakespot shuts down on July 1, 2025
by
Alex5723
23 hours, 59 minutes ago -
No Screen TurnOff???
by
CWBillow
1 day ago -
Identify a dynamic range to then be used in another formula
by
BigDaddy07
1 day ago -
InfoStealer Malware Data Breach Exposed 184 Million Logins and Passwords
by
Alex5723
1 day, 12 hours ago -
How well does your browser block trackers?
by
n0ads
22 hours, 48 minutes ago -
You can’t handle me
by
Susan Bradley
10 hours, 20 minutes ago -
Chrome Can Now Change Your Weak Passwords for You
by
Alex5723
15 hours, 24 minutes ago -
Microsoft: Over 394,000 Windows PCs infected by Lumma malware, affects Chrome..
by
Alex5723
1 day, 23 hours ago -
Signal vs Microsoft’s Recall ; By Default, Signal Doesn’t Recall
by
Alex5723
1 day, 3 hours ago -
Internet Archive : This is where all of The Internet is stored
by
Alex5723
2 days ago -
iPhone 7 Plus and the iPhone 8 on Vantage list
by
Alex5723
2 days ago -
Lumma malware takedown
by
EyesOnWindows
1 day, 12 hours ago -
“kill switches” found in Chinese made power inverters
by
Alex5723
2 days, 9 hours ago -
Windows 11 – InControl vs pausing Windows updates
by
Kathy Stevens
2 days, 9 hours ago -
Meet Gemini in Chrome
by
Alex5723
2 days, 13 hours ago -
DuckDuckGo’s Duck.ai added GPT-4o mini
by
Alex5723
2 days, 13 hours ago -
Trump signs Take It Down Act
by
Alex5723
2 days, 21 hours ago -
Do you have a maintenance window?
by
Susan Bradley
1 day, 1 hour ago -
Freshly discovered bug in OpenPGP.js undermines whole point of encrypted comms
by
Nibbled To Death By Ducks
1 day, 23 hours ago -
Cox Communications and Charter Communications to merge
by
not so anon
3 days ago -
Help with WD usb driver on Windows 11
by
Tex265
11 hours, 45 minutes ago -
hibernate activation
by
e_belmont
3 days, 9 hours ago -
Red Hat Enterprise Linux 10 with AI assistant
by
Alex5723
3 days, 13 hours ago -
Windows 11 Insider Preview build 26200.5603 released to DEV
by
joep517
3 days, 16 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.