|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Restore Deleted AutoNumber By Richard Rost 
6 years ago
Restore AutoNumbers for Deleted Records In this Microsoft Access tutorial, you will learn how to restore AutoNumbers for records that have been deleted. Whether you accidentally deleted them, or the records became corrupted, you may need to get their IDs back. This video will show you how. Samantha asks, "I’m in tears. I accidentally deleted a customer. I have a backup and can add his record to the table, but he gets a new AutoNumber. Is there a way to restore his old Customer ID so I don’t have to re-enter ALL of his orders, contacts, etc." MembersI'll show you how to restore a bunch of records from a backup table, how to write SQL and VBA to automate the process, how to increment your "final" AutoNumber, and we'll see how to use Referential Integrity to prevent accidental deletion in the future. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! Links
IntroIn this video, I will show you how to restore a deleted AutoNumber value in Microsoft Access using an append query. If you have accidentally deleted a record or experienced table corruption, you will learn how to re-insert a specific AutoNumber ID from a backup so all related records continue to match up correctly. I will demonstrate the process step by step with an example involving linked customer and order tables, review how this method affects your database's numbering sequence, discuss the risks involved, and explain when this solution can be used as a last resort.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I am going to show you how to replace an auto number with an Append query. Whether you have deleted it by accident or if the record has become corrupted, there is a way to get it back. I will show you how. Today's question comes from Samantha. She said, I am in tears. I accidentally deleted a customer. I have a backup and can add his record back to the table, but he gets a new auto number. Is there a way to restore his old customer ID so I do not have to re-enter all of his orders, contacts, etc.? Yes, Samantha, you are in luck. There is a way that you can reuse that ID. Normally, in my classes, I tell my students not to get used to being able to do this, because you really should not. However, in an emergency, or if you have database corruption, it happens, there is a way you can reuse those IDs. It is not easy, but I will show you how to do it. This is a last resort. Normally, what I would tell people is if you have a backup, restore the backup, but sometimes people do not make backups on a regular basis. Hold on, I have got a slide for this. Wait a second. Yes, this is my favorite slide. I always put this up whenever I am showing how to do append queries, or update queries, or anything that can change the database in a major way. Normally, what I would do is I would tell people if you have some corruption, restore your backup. If your backup is a few days old and you have added lots of new data since then, you can go in and try to repair it and copy things here and there. However, if you have deleted some customers and you want to copy them over from your backup table, you have to re-link them to all their child records. Customers have to link up to their orders and the contacts and all the other stuff that is related to your customer table. Or, if you have records in your order table that are corrupted or deleted and you have to link them up to your order details and your products and all the foreign keys, you do not want to have to change all those. Now you can. You can run update queries and change all of those values because they are just long integers. However, if you can simply replace that ID, that auto number in the original parent table, that is a lot easier. Let me show you an example of what I am talking about. Here is an example of two tables. This is a customer table. It has all your basic fields: customer ID, first name, last name, email, address, city, state, phone number, all that good stuff - simple customer stuff. In fact, this is in my basic customer template. You can download it from my website. I will put a link in the description below the video. I have added a second table here to represent a related order table. I cover setting up relationships between multiple tables in my expert classes, Access Expert Level 1 and 2. What this represents is you have an order ID as your primary key. It is the auto number for this table. Each order gets its own unique identifier. The customer ID is called a foreign key. This refers back to the auto number in the customer table. For example, these three orders here and this order down here were all placed by customer one. In fact, if you sort this column, it makes it easier to see. Let us sort smallest to largest. There are customer one's orders, customer two, customer four, and customer five's orders. Yes, you would have more fields in here, like the order date, where it was shipped to, if it is paid. However, for the purposes of class, here are the three fields I put in here: the primary key (order ID), the foreign key (customer ID), and the order amount. Suppose the customer table has become corrupted, or you accidentally delete Bill Jones. Goodbye. Right-click, delete. Now if you go to add Bill Jones back, notice he is now customer 12. That is because Access normally does not let you reuse these numbers and you cannot come in here and change them by typing over them. That is not allowed. Now, you could fix this situation by opening up the order table and changing all these fives to 12. This is the foreign key - it is only a long integer. Normally, if you are using a form and subform in your database, Access makes that relationship for you and it automatically puts this foreign key in there. See Access Expert 1 and 2 or my Relationship Seminar if you do not know how relationships work. In this particular instance, I could just come in here and change these to 12. Now everything is fixed, assuming I have all of Bill's information. However, let us pretend for the purposes of class - this is an oversimplified example - that Bill has 300 records in here. Now, you could do it with an update query; you could run through the order table and put an update query in there to change all the fives to 12s, sure. However, let us say there are 15 tables that are related to customers. Now you have to do that update query for 15 different tables, and it just becomes a lot of work. It would be much simpler if there was a way to change this to five, but you cannot change it. However, you can delete Bill Jones and then add him as a new record using that five. You cannot just add it directly at the table level. You have to use something called an append query. Again, if you do not know what append queries are, I have a free tip video that shows you how to use one. Go watch that now if you do not know what an append query is. For those of you who do, let us continue. So I go to my backup, because I have got a backup that has Bill Jones's information. Now I know what all his information is. Bill Jones's customer ID was five. I have his email, address, all that stuff. I can type that one record back in, no problem. Members, in the extended cut video I am going to show you how to do a bunch of records at the same time. For the purposes of this free TechHelp video, I am going to show you how to do it with one record. Again, this is an emergency case. Let us close and save changes, then close these tables. Let us create an append query. Come up here to Create, Query Design. For this example, do not bring any tables into the query. Leave it blank. We are going to change this to an append query. What table do we want to append into? We are going to append into the customer table. Hit OK. You have nothing up here to pull data in, so come down here and type in some records. I want to append into the CustomerID. What do I want to append? The number five. That is Bill's customer ID, the one I am replacing. At this point, you could just run that. I am going to put one more field in here just so you can see what is going on. I am going to add FirstName and put in "Bill" (put "Bill" inside quotes). Now, run the query. Nothing appears to happen - it is an append query. Nothing happens at this point. It all happens in the background. In fact, if you try to run it a second time, it yells at you because that record exists now, since we just added it. Let us move this off to the side and open up the customer table. There is Bill, just added with an append query. Access lets you get away with that if you are using an append query. You cannot type it in. You cannot do it in a form or a table or even a regular query. You have to use an append query. Now, there is one thing you have to be very careful about at this point. This is why I do not normally teach this, only for emergencies. If you come down here and add another record, notice the auto number is set to six. The auto number is set to whatever is after the record you just put in there. So be very careful. You have to make sure you bump that auto number up past where it needs to be. So if you have 5,000 records in here, you have to either put in 5,000 or more records, or run the append query again. Come back over to this guy and close the table. Come back over here. Put something in here that you know is high enough, like 200, and then just X or whatever you want to call this. Run it. I have put a run button up here on my toolbar. Go to the Design tab and you will see "Run." Now I ran it. Now, if I open up the customer table again, there is my new record 200. Now, if I delete this guy and add another one, you can see I am at 201. This is how you can get around Access's auto numbering rules and you can reinsert records back into the table that you might have deleted. Now you just have to go in here and type in Bill's information. Members, if you are a member of the YouTube channel, in the extended cut video I will show you how to do this from a backup table and pull all of Bill's information back in from your backups. For now, you can type this all in or copy and paste it if you want to. Now that you have this five in here and the record is inserted in the table, you can go ahead and copy it and paste it from your backup table if you want to. So that is how you do that with the append query. You might be thinking that if you are using referential integrity, you cannot accidentally delete a record if it has children. So if Bill had orders in the system and you went to delete Bill, it would yell at you. That is all fine and dandy, but referential integrity - make sure you do not have cascade deletes on. I hate cascade deletes. If you delete Bill, it deletes all of his orders too. That is very dangerous. I almost never use it. However, if you have a split database, which most big databases are, if you are working for a company and you have multiple users with a split database solution, you cannot use referential integrity with linked tables. So again, you will need to be able to do something like this if you accidentally delete a record. Of course, I recommend not letting people play around in your tables at all. Use forms. In there, have multiple "Are you sure?" prompts on a button before you let them delete stuff. That is a whole different video. I cannot emphasize this enough. Back up your database. Back up your databases at least every night. Keep multiple rolling backups. I have a database that I built that I will be posting on my website soon. I am just running it through the paces and making sure it is working properly so it backs up the data on a nightly basis. You can use any backup service that you want. The problem with Access is if it is open, if someone on your network leaves the database open, it locks the file and most backup software cannot back it up. Members, stick around for the extended cut. I will post a link below for the members-only video. I will show you how to restore a bunch of records from a backup table. So if you have records 5, 10, 15, 35, I will show you how to pull all them in at once. We will do it with SQL and VBA, the append and delete queries. We will actually build queries, do a one-click solution to run the import, and then bump the ID up automatically. Then I will show you that referential integrity trick to prevent deletion if your tables are all local. If they are not local, then you cannot do it. How do you join? Click on the join button right under the video. See it? There is a little join button right down there. You will see a list of all the different types of memberships that are available. Silver members and up get access to the extended cut TechHelp videos. Thank you for watching. Do not worry, I am still going to keep posting free TechHelp videos just like this one to help everybody free of charge. The members are just people who get extra little benefits. Make sure you subscribe to my channel. Subscribing and joining are two different things. Subscribing just means that you will get notifications whenever I post new free videos. Also, make the trip over to my website and check out my Access forum. It is quite active. I am very happy with that. If you want to see your question answered, send it to me on my TechHelp page. If you have not already, check out my free Access Level 1 class. It is a three-hour tutorial. You can find it on YouTube or on my website. If you like it, Level 2 is just one dollar. Thanks for watching, folks. Hope you learned something, and one more time: Back up your data. Every day. QuizQ1. What is the main reason you might want to reuse an auto number (ID) in an Access database?A. To reduce the size of the database file B. To restore a deleted record while keeping existing relationships intact C. To make the data appear more sequential D. To avoid using append queries Q2. Why does Access normally not allow you to reuse auto number values directly? A. To avoid data redundancy B. To maintain uniqueness and data integrity in relationships C. To increase database performance D. To make sorting easier Q3. What tool does Richard recommend using to restore a deleted record with its original ID? A. Update query B. Delete query C. Append query D. Make-table query Q4. What must you do to insert a record back into a table with a specific auto number value? A. Type it into the table directly B. Use a form to manually set the ID C. Use an append query and specify the ID value D. Change the properties of the field to allow manual entry Q5. If you restore a deleted parent record (like a customer) with a new auto number, what additional step is usually required? A. Nothing, Access fixes all relationships automatically B. Change all child table foreign key values to match the new parent ID C. Delete all related child records D. Export the data to Excel and import it back Q6. What can happen if your auto number value is restored and you do not adjust the seed after re-inserting the lost ID? A. Access automatically corrects it B. The auto number field starts duplicating IDs C. New records will re-use existing IDs, causing conflicts D. New records will continue from the highest existing value Q7. Which of the following is a key recommendation Richard makes regarding table manipulation in Access databases? A. Let users edit tables directly for accuracy B. Always use forms with confirmation prompts for sensitive actions C. Enable cascade deletes to simplify record removal D. Give everyone access to all tables Q8. What is a risk of enabling cascade deletes in relationships? A. It makes the database read-only B. It could delete related child records unintentionally C. It prevents record deletion entirely D. It disables referential integrity Q9. When your Access database is split with linked tables, what is a limitation Richard points out regarding referential integrity? A. Referential integrity is enforced more strictly B. Referential integrity can not be used with linked tables C. Cascade updates are required D. Linked tables cannot be queried Q10. What is Richard's primary recommendation to prevent accidental data loss or corruption? A. Increase record locking levels B. Use only single-user databases C. Back up your database regularly and keep multiple backups D. Avoid using auto numbers as primary keys Q11. Why might standard backup software have trouble backing up an Access database? A. Access databases are encrypted by default B. Access files are too large C. The file is locked if someone has it open D. Access databases are invisible to the operating system Q12. What should you do if you have to restore multiple deleted records and preserve their original IDs? A. Add them back one at a time manually via the table B. Use append queries to insert them with the original IDs C. Use delete queries on existing records D. Only restore records with even-numbered IDs Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-D; 7-B; 8-B; 9-B; 10-C; 11-C; 12-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers how to replace an AutoNumber value in a Microsoft Access table using an append query. This becomes important if you've accidentally deleted a record or if a record's data has become corrupted and you need to restore it using its original AutoNumber value. I am going to show you exactly how this process works and offer guidance for those emergency situations where simply re-entering the record is not enough.This topic comes from a question about restoring a customer record after accidental deletion. When you re-add that customer from your backup, Access automatically assigns a new AutoNumber value. The challenge is that all related records, such as orders, contacts, and other linked records, reference the original customer ID. If you simply insert the new record with a fresh AutoNumber, none of the links work. The main question is whether it's possible to reuse the old AutoNumber value so everything stays connected. Now, while I don't recommend making this a regular habit, it is possible to reuse an AutoNumber in an emergency when no better options are available. Restoring from a recent backup is always the preferred approach, but not everyone has up-to-date backups. When you need to get a specific deleted or corrupted record back with its original AutoNumber, there is a way to do it. The fundamental problem is that Access does not normally allow you to type over or directly change an AutoNumber field. When you add a new record, Access simply gives it the next available number. This creates issues if lots of other tables (like orders, contacts, etc.) reference that original ID, and you want everything to link up seamlessly just like it did before. You could theoretically go through all your related tables and manually update every foreign key to match the new customer ID. If you only have a single related table or a handful of linked records, that approach works. However, most real databases have many tables with dozens or hundreds of linked records, which makes manual updates a nightmare. Running separate update queries for every table gets tedious fast, and it can leave room for mistakes. Instead, there's a much easier solution: use an append query to insert the record back into the main table and specify the original ID you want to restore. Access will allow this if you use an append query instead of entering the record directly into the table or form. This comes in handy when all you need is to get that one record back with its original AutoNumber. Here's a general example. Imagine you have a customer table with fields like CustomerID, FirstName, LastName, Email, Address, and so on. You also have an order table with its own primary key, OrderID, and a foreign key, CustomerID, which ties orders to customers. If a customer is deleted and then re-added, he gets a new CustomerID, breaking the connections with all his orders. Updating every related record to point to the new ID is not practical if there are multiple connections. To solve this, you can create an append query and directly insert a new record using the original deleted CustomerID. The process involves starting a blank query, converting it to an append query, and specifying the target table (like Customers). You then manually enter the values you want, including the original AutoNumber value for the ID field. When you run the append query, Access accepts the value and creates the record with the original ID. Trying to type in the value directly in the datasheet view or a form won't work—an append query is required. Once you restore the record, be cautious with the AutoNumber sequence. Adding a record manually after restoring an old ID might create conflicts or cause Access to use a duplicate value. To avoid this, you may need to insert a dummy record with a higher number to move the AutoNumber sequence beyond any restored values. After doing so, you can delete the dummy record, and the AutoNumber will continue from the correct place. For those who are members, in the extended cut of this video I demonstrate how to handle restoring multiple records at once from a backup table, rather than just a single record. I will also show you how to use SQL and VBA to automate these steps, manage the append and delete queries, and cover how to handle referential integrity. There are some important details when dealing with split databases or tables with established relationships, as referential integrity and cascade deletes can complicate the process. In systems where referential integrity is enabled, Access won't let you delete a parent record that still has child records, which helps protect your data but also means you must plan your actions carefully. Finally, I cannot stress enough the importance of keeping frequent backups of your database. Daily backups are essential in any environment, especially when working with multi-user databases or mission-critical data. Relying solely on repair or restoration techniques like this should always be a last resort, not standard practice. If you would like to see a complete video tutorial with step-by-step instructions and demonstrations for everything covered here, you can find it on my website at the link below. Live long and prosper, my friends. Topic ListReplacing deleted auto number records in AccessRestoring deleted records using append queries Appending a record with a specific auto number ID Using append queries to bypass auto number restrictions Bumping the auto number seed past replaced records Precautions with auto numbers after append queries Handling related tables and foreign keys after deletion Manually entering and appending missing records Restoring parent table records to preserve relationships Limitations and dangers of cascade deletes and referential integrity Recommendations for backing up Access databases |
||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access Replace AutoNumbers Restore AutoNumbers Deleted AutoNumbers PermaLink Restore Deleted AutoNumber in Microsoft Access |
