#Deleted
By Richard Rost
3 years ago
Fixing the #Deleted Error in Microsoft Access
In this Microsoft Access tutorial, we're going to discuss the #Deleted error message. I'll provide one specific example of when this message can appear. Following that, we'll talk about various scenarios where you might encounter this error message and what steps you can take to resolve it.
Musoke from Kampala, Uganda (a Gold Member) asks: I have a continuous form where I use a delete query to remove all data at once. Then I utilize an append query to add data back to the same form. However, the new data doesn't load instantly on the form. Instead, I immediately see #Deleted all over the form. How can I modify this so that the data loads instantly and the #Deleted text doesn't appear in the continuous form?
Prerequisites
Links
Recommended Courses
Common Causes
- Data deleted by delete query
- Data deleted by another user
- Network / Internet connectivity issues
- Conflicts in updating a record
- Corrupted database
- Incompatible data types
How to Fix
- Refresh (Me.Requery) data – F5
- Check network / Internet connection
- Close & reopen the database
- Restart Windows
- Compact & Repair
- Verify data types
- Access Learning Zone Database Troubleshooter
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, deleted error, records deleted, #deleted, network problems, conflicts in updates, corrupted data source, check network connection, check data source integrity
Subscribe to #Deleted
Get notifications when this page is updated
Intro In this video, we talk about the #Deleted error in Microsoft Access, including why it appears in forms after running delete and append queries, and how to fix it using refresh and requery methods. I show both beginner and developer-friendly techniques for resolving the issue, cover common scenarios that trigger #Deleted such as multi-user conflicts, connectivity problems, corrupt databases, and incompatible data types, and share troubleshooting steps you can take if the error persists.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor, Richard Rost. Today, we're going to talk about that dreaded pound deleted error. I'm going to show you a specific example where it comes up based on one of our questioner's questions. Then we're going to talk about some other instances in which you may see this come up where you're not expecting it.
So let's get into it. Now this is going to be a beginner and a developer video. I'm going to show you the beginner solution first - a nice, easy solution - explain what this is and why it's showing up. Then for you developers, I'm going to show you a solution to try to get rid of this if you can.
Today's question comes from Musoke in Kampala, Uganda, one of my gold members. Musoke says, I have a continuous form where I use a delete query to remove all the data at once. Then I utilize an append query to add data back to the same form. However, the new data doesn't load instantly on the form. Instead, I immediately see pound deleted all over the form. How can I modify this so that the data loads instantly and the pound deleted text doesn't appear in the continuous form?
Well, that's exactly what you're going to see if you delete all the records that go behind that form. Even if you try to replace them, those specific records are gone.
Just a couple of days ago, I did a video on importing text. You send your customer list to someone by exporting it as a text file, then they import it into their database. You'll see that problem in this database under certain circumstances. Let me show you what I mean.
Let's go back to this database for just a minute. If you haven't watched this video yet, go watch this first. Here I am in that import customers database.
If you recall from that video, what we do is we import the customers into this temporary table, then we run a delete query to delete the customers out of the customer table, and then append them from customer temp back to customer T. That's basically what Musoke has going on.
But if I hit the import customers - all right, are you sure you're going to do it? - okay, and everything works fine. No problems. Now, if I have the customer list open, the customer list open, if I do the import again, it's done. Now, watch what happens.
As soon as I click over here, Access tries to load those records. There's your pound deleted. Why? Because those actual records that were in there before that loaded into the form were deleted. Those records are gone. They were replaced with other records, but in order to see those new records, I would have to refresh this form.
You can do that manually with F5. That refreshes the form, or you can close it and reopen it. That's the beginner solution.
Now, for a more advanced solution for developers, I did actually cover this in yesterday's video when I talked about the is loaded function. You can use the is form loaded to check to see if the customer has got that customer list open, and if so, requery it.
We do a refresh in this video because if you're just refreshing a single field, like a calculated field in a form, you use refresh. But in this case, you want to reload the whole form. You want to tell it to reload all of its records. So for that, we'll use requery.
When to use refresh or requery can be a little confusing sometimes, so I put this video together that explains the difference in more detail. Go watch this if you want to learn more.
I'm going to put the is loaded function here in my global module. Instructions on how to get this are in that other video, the is form loaded video. Really easy to do. There's the code.
In this button here, where we handle the import, all you have to do down here at the end is say, if is loaded - what's that, the customer list form, I believe? - then let's make sure: customer list form. That's it.
If that form is loaded, then forms!customerlistf.requery. Make it requery that form immediately.
Now let's close it all up, try it again. Let's open up the customer list. There it is. Let's do the import again. Are you sure? Yup. And done.
You see it flash very briefly, but the deleted shows up and then goes away right away, and there are all your new records. That's how you would get rid of that problem.
That's just one instance where you'd come across that pound deleted error. There are lots of other instances where this might happen.
For example, if you have a multi-user database, you've got other users. One of the other users that's logged on tries deleting a record. Let's say you're just browsing through customers. You're in the customer form here, you're browsing through, and all of a sudden you see deleted here. Someone might have deleted Jean-Luc Picard before you got to it.
When you open the form, all 33 records loaded. But if someone deleted that, it doesn't update this entire set of records in your form, but that one's going to show deleted. So that's one other reason why you have to be careful when you're dealing with a multi-user database.
Another reason: network or internet connectivity issues. If you connect to a file server or you're getting your data off something like SQL Server online, if there's a problem connecting, it might give you the pound deleted error.
I've seen this happen a lot. You go to open up a customer record, and it's like, wait a minute - that shouldn't be deleted. Generally, closing the form and reopening it fixes the problem and it goes away unless the connection problem is still persisting. That could be another reason why you get that error.
Just like deleting data, another user deleting data, you can also get this error message if you have conflicts in updating a record. In other words, and Access is supposed to prevent this, but it doesn't always - you go to update Jean-Luc's record and at the same time, someone else goes to update Jean-Luc's record, and you both try saving at roughly the same time. It could cause a problem. The records could collide, and one of you is going to see pound deleted even though the record is still there. I've seen that happen a lot too.
Of course, a corrupted database. If there are problems with your database file, you could very easily see pound deleted when you know the record should be there. Your database is corrupted. Obviously, try to compact and repair. If that doesn't work, you might have other, bigger problems with your database.
Another reason: incompatible data types. This usually only comes into play if you're using a database server like SQL Server and you try using a data type on the server that Access doesn't support. For example, if you've got an older version of Access and you're using a big int on SQL Server, unless you update your local database to take that into consideration or you upgrade your version to a newer version that does support that data type, you might see pound deleted when the record is really there. It's just got incompatible data in it, so it confuses Access.
How do we fix the pound deleted error? Refresh the data. I just showed you, whether it's a refresh or a requery. If the button, for example, is on that form, you could do a Me.Requery. If it's on a different form, you could check to make sure it's loaded like I just showed you and do a forms!customerf.requery. Try to requery that form first, or for you beginners, hit F5.
If that doesn't work, check your network connection or your internet connection. See if other things in the database are affected as well. If no forms open, you've got a loss of connection there. If it's just that form that's not reading, maybe try something else.
What would I try first? The easiest, obvious thing: close and reopen the database. Shut it down, give it a second, restart it, and see if the problem still persists. If that doesn't do it, restart Windows. Just shut down, restart, and that fixes a lot of problems. More than you figure.
The on and off at that button - turn it off, turn it back on. Now it's working. How many of you in tech know that because you have even said my phone isn't working - well, shut it down and restart it. Now it's working.
Database still not working? Compact and repair. That's your next line of defense. If you've tried all these things, you've shut down, you restarted the database, you restarted Windows, try to compact and repair next. That will tell you at least if your database is corrupted or not. Usually works 99 percent of the time.
If you are dealing with SQL Server or another server type, again, verify your data types. Make sure that you're not using big int or any of those things. Remember that Boolean values turn into bits, that kind of stuff.
Still not working? Hit up my troubleshooter. I have a really comprehensive troubleshooter on my website. I'll put a link to it down below in the links section for you. It's got a whole bunch of extra stuff you can check to cover all the kind of weird stuff you're going to run into when you're using Access.
If something weird happens and you're not expecting it, like a pound deleted error, and you've checked all the obvious things, you didn't delete any records or whatever, try running down the troubleshooter. I tried to put these things in the order that you should try them to fix stuff. Go run down this list. There's all kinds of stuff in here. I've been building this for a few years, lots of help from my moderators too.
There you go. There is your TechHelp video. I hope you learned something. Live long and prosper, my friend. I'll see you next time.Quiz Q1. What does the pound deleted error in Microsoft Access typically indicate?
A. Records have been deleted or are inaccessible in the underlying data source
B. A syntax error in your SQL query
C. Invalid field names in your form design
D. The Access application needs to be updated
Q2. In Musoke's example, what operation on a continuous form led to the pound deleted error appearing?
A. Filtering the displayed records
B. Deleting all records and then appending new ones
C. Sorting the records in descending order
D. Locking the database table
Q3. What is the beginner solution suggested for refreshing the data to remove the pound deleted error?
A. Requery the record source programmatically
B. Refresh the form manually using F5 or by closing and reopening it
C. Switch to SQL Server instead of Access
D. Change the form view to Datasheet and back
Q4. For developers, what is the recommended method to update a form after deleting and appending records to avoid the pound deleted error?
A. Use the forms!formname.refresh method
B. Use the forms!formname.requery method if the form is loaded
C. Restart the database server
D. Change all fields to text data type
Q5. What is the main difference between Refresh and Requery in Access forms?
A. Refresh reloads all records, while Requery only reloads calculated fields
B. Refresh reloads calculated fields, while Requery reloads the entire recordset
C. There is no difference
D. Refresh only works in design view
Q6. Which is NOT a possible cause of the pound deleted error according to the video?
A. Another user deleting records in a multi-user environment
B. Incompatible data types in linked SQL Server tables
C. Invalid primary key relationships
D. Network or connectivity issues with the data source
Q7. What should you do first if you encounter a pound deleted error and simple refreshing does not work?
A. Immediately repair your Access installation
B. Close and reopen the database
C. Redesign your form
D. Migrate your data to Excel
Q8. When using an append and delete query workflow, why does Access show pound deleted on an open form?
A. The form is bound to records that no longer exist after the delete
B. The database is corrupt
C. The form does not support continuous view
D. The queries were not run in the proper order
Q9. If your database is still not functioning correctly after trying all refresh and reconnect methods, what should you try next?
A. Compact and repair the database
B. Upgrade to the latest version of Windows
C. Contact Microsoft support immediately
D. Reinstall all drivers
Q10. How might incompatible data types on a SQL Server backend cause pound deleted errors in Access?
A. They result in improper record locking
B. Access cannot interpret unsupported data types like big int
C. They prevent queries from executing at all
D. They increase network latency
Q11. In the case of update conflicts in a multi-user database, why might one user see the pound deleted error?
A. Both users accidentally deleted the record at the same time
B. Both users tried to update the same record simultaneously, causing a data collision
C. The record's auto number field changed
D. The form's layout view was not enabled
Q12. According to the video, what is often a surprisingly effective general troubleshooting step for fixing the pound deleted error?
A. Increasing your computer's RAM
B. Restarting Windows or the computer
C. Installing new fonts
D. Disabling macros
Q13. If you have ruled out deletions, connectivity issues, and form refresh problems, what resource is recommended for further troubleshooting the pound deleted error?
A. The built-in Access help documentation
B. An online troubleshooter provided on accesslearningzone.com
C. Microsoft's Excel documentation
D. Your computer's device manager
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-C; 7-B; 8-A; 9-A; 10-B; 11-B; 12-B; 13-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.Summary Today's TechHelp tutorial from Access Learning Zone covers how to deal with the dreaded pound deleted error in Microsoft Access. This is a common issue that both beginners and developers encounter, so I'll walk you through some basic solutions first before getting into more advanced options for those who are working with code in Access.
To start, let's look at a problem that comes up frequently. Suppose you have a continuous form that's displaying data from a table. If you delete all the underlying records in that table, then immediately try to append new ones, the form does not instantly display the updated data. Instead, you are often greeted with fields that just show pound deleted. This happens because the rows the form was showing have been removed and replaced behind the scenes, but Access does not automatically refresh the view unless you tell it to.
Here's how this plays out in practice. Imagine you're importing customer data into a temporary table, running a delete query to remove existing customers, then running an append query to add the new customers in. If you have the form that shows customers open while doing this process, as soon as Access tries to load what it thinks are the original records, you'll see pound deleted because those record references no longer exist. To see the new data, you need to refresh the form. This can be done simply by pressing F5 or by closing and reopening the form. That is the easiest solution for beginners.
For developers, there is an even better way to handle this. If you are running automation (such as with VBA code) to perform these import, delete, and append actions, you can add logic to check whether the customer list form is currently open. If it is, you issue a requery command to reload all its records immediately after the append operation. This ensures that as soon as new data is available, the form is updated and the pound deleted error goes away almost instantly. This step allows for a smoother experience for your users.
You might be wondering when it's appropriate to use refresh versus requery. Refresh is useful when you need to update just a calculated field on a form, but if you want to reload all records from the data source, requery is the correct choice. If you're interested in a deeper explanation of the differences between these two commands, there is a separate video that examines this topic in detail.
There are other scenarios where the pound deleted error can show up as well. In a multi-user environment, another person might delete a record while you are browsing it. The result is that even though your form initially loaded all the records, when you scroll to a record that was deleted by someone else, you'll see pound deleted appear for that row. This is something to be cautious of when multiple users are interacting with the same data.
Network or internet connectivity issues can also cause this error to appear. If you are accessing data on a file server or using SQL Server and your connection drops, Access may display pound deleted for affected records until you re-establish the connection or reload the form. Sometimes simply closing and reopening the form or database will restore normal operation, provided the connectivity problem has been resolved.
Simultaneous updates to a record from different users can also trigger the pound deleted error. Even though Access tries to prevent update conflicts, if two users change and save the same record at almost the same moment, a conflict may arise, leading to this error for one of the users.
Database corruption is another common culprit. If your Access file becomes corrupted or damaged, you may see this error even when you know a record should be present. In such cases, try compacting and repairing the database. If that fails, more serious issues with the database file may be present.
In addition, if you are working with SQL Server or another backend database, using data types that are not supported by Access, such as big int, can result in the pound deleted error showing up. This is because Access cannot interpret the data type properly. Make sure your local Access database is up-to-date and supports any special data types you are using, or adjust your schema appropriately.
To summarize, the first step when you see pound deleted is to refresh or requery your data. For a quick fix, use F5 or close and reopen the form. If you're working with VBA, use the appropriate code to requery the form automatically after updating data. If that does not work, check your network connection and try restarting the database and even Windows itself. Often, simply closing and reopening things resolves unexpected issues. If problems persist, use Access's compact and repair function. For SQL Server environments, double-check your data types for compatibility.
Finally, if none of these steps solve your problem, I have a comprehensive troubleshooter available on my website. It contains a wide range of potential solutions and checks you can perform for all kinds of unusual errors in Access.
A complete video tutorial with step-by-step instructions on everything discussed here is available on my website at the link below.
Live long and prosper, my friends.Topic List Explanation of the #Deleted error in Access forms
How deleting records triggers #Deleted in continuous forms
Using Refresh to update form data after deletions
Using Requery to reload form records
Difference between Refresh and Requery
Checking if a form is loaded with the IsLoaded function
Automatically requeriying a form after importing data
Handling #Deleted in multi-user databases
How network or internet connectivity causes #Deleted
Impact of record update conflicts on #Deleted
Dealing with database corruption causing #Deleted
Resolving #Deleted from incompatible data types with SQL Server
Fixing #Deleted with Compact and Repair
Verifying and correcting data types with SQL Server
Step-by-step troubleshooting for persistent #Deleted errors |
