RecordsAffected
By Richard Rost
2 months ago
Count Deleted Records Using RecordsAffected in Access
In this Microsoft Access tutorial, I will show you how to find out how many records are deleted when running a delete query, using both a non-programming method and two VBA techniques. We will cover how to preview the number of records to be deleted, perform the deletion, and then count the records removed using the RecordsAffected property for an efficient VBA solution. This tutorial is aimed at intermediate and advanced Access users who want to get more accurate control and feedback when deleting data.
Camille from Fort Lauderdale, Florida (a Platinum Member) asks: I have to delete old log records from one of my tables on a regular basis. What is the simplest way to find out how many records were deleted? I'm not much of a VBA programmer, so can you show a non-VBA way first?
Members
There is no extended cut, but here is the file download:
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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, delete query record count, RecordsAffected property, delete query VBA, DCount function, delete records without programming, select query for deleted records, delete old contacts, date criteria query, SQL delete statement, CurrentDb.Execute, VBA advanced method, non-VBA solution, SetWarnings macro, query design
Transcript
In today's video, I have something for expert level people, which I consider to be intermediate and developer level people. So this is going to be a two-fer.
We are going to learn how to count the records that are deleted when you run a delete query. A lot of people need to know, "I'm going to delete a bunch of records, but I want to know how many records are going to be deleted before I do it." Or even after I did it, "How many were deleted?"
I'm going to show you a non-programming method first, and then I am going to show you two different ways to do it with some VBA. The advanced method is really, really cool. We are going to use the RecordsAffected property of the Database object. If you do not know what that is, you will in about 15 minutes. So stick around.
Before we get into today's material, if you are not sure what a delete query is, go watch this video. It is free. It is on my website. It is on my YouTube channel. There is a link right there. Watch that and then come on back.
We have not done a Q&A style TechHelp in a while. Today's question comes from Camille in Fort Lauderdale, Florida, one of my Platinum members. Camille says, "I have to delete old log records from one of my tables on a regular basis. What is the simplest way to find out how many records were deleted? I am not much of a VBA programmer. So can you show a non-VBA way first?"
There are a lot of different ways to do this. I'm going to show you both ways. I'm going to show you a simple way you can do it first without any VBA programming. Then I am going to show you how to do it with VBA programming. I am going to show you two VBA methods.
I know you said you are not a big VBA programmer, but for everybody else who is, I am going to show a little more advanced ways too.
Here I am in the TechHelp free template. This is a free database you can grab from my website if you want to. In here, let's say I have contacts. What I consider a contact is every time you talk to a customer, you put a note in the contact. So if you look at their customer form, you go to contacts and you can see all the stuff you talked about.
Now, we can pretend this is a log that we do not want to save old records from, like everything, let's say, before three years ago.
Again, I have said this in many videos before. I recommend archiving data, not deleting it, because you never know a year from now when someone might say, "Wait a minute. What about all that stuff we had from..." Oh wait, we deleted it.
Now you have to go through your backups and hope it is there. So archive, do not delete. I have a whole video on it. But yes, there is some data you might want to delete. Old logs, like I download my web logs - traffic on the website. I download it, analyze it, keep it for a year, and then I delete it.
Might I possibly need some of that in the future? Yes, maybe, but not everything. I think maybe once in 20 years, I have needed that.
Anyways, let's say you want to delete old contacts. Let's say before 2004. Most of these are 2003, 2002.
So, what is today? Today is September 2025. Let's say you have to delete everything that is more than two years old.
The non-VBA way to do it would be to create a select query first that is going to show you the records that would be deleted, and then make a second query that actually does the deletion.
If you have your system warnings on, then you do not need this step because Access will warn you before you run the delete query. It will say, "Hey, I'm about to delete 15 records. Are you sure you want to do that?" So that could be your delete warning.
But I personally find all those warnings and reminders and stuff annoying. I teach in one of my early videos, and I show this early in my classes too, to turn all that stuff off. It is annoying every time: "We are about to append four records. Are you sure?" "Okay, four records were appended." "We are about to delete 16 records. Are you sure?" I turn those off. I do not like them.
So I am assuming since you are not seeing any warnings, you have probably already followed the instructions in this video and turned off the warnings. And that is fine. Just again, make sure you have good daily backups.
Assuming you have warnings turned off, let's come in here. Let's create a query and bring in the contact table. All you really need to do is bring in the field that you are basing it on, and then put your criteria in down here.
For contact, we want two years old. For the criteria, you could use an exact date if you need to. For stuff like this, for just deleting, I use basic simple date math. So this would be less than today's date minus 730. 365 times 2 is 730. That is roughly two years. Good enough.
Save, and then we will just call this one contact old Q. I try to keep everything singular, but sometimes it doesn't.
Now, if you run this query, there is all your old stuff. There are 104 contacts. How many are in the table total? What do we have? There are 104. We are going to delete all of them. I do not want to delete all of them. Let's put a couple of older ones in here. Here are some from now.
Now if I run this query again, let's see. We will throw 104, even though we have 107. So that is correct.
Let's go three years. Three years would be what? Math in my head - 1095. This is old stuff over three years.
There are 16 that are over three. I know I loaded a whole bunch at once when I made this database. But these old ones will be deleted.
Now, we have contacts old Q. Save that. So what you are going to do is, if you want to know how many records will be deleted, you are just going to open up contacts old Q. Or if you know how, you could make a little field in here with a DCount. That is a whole separate video. You wanted a non-programming solution.
Now all you have to do is base your delete query off of that guy. This is an editable record set. You can see the new record blank down here. You can go to a new one and add a new one if you want to. You are just going to delete the stuff from that query.
So go to Create Query Design. We are going to bring in contacts old Q. We are going to make this a delete query and then just bring down the star. Save this as contacts old delete Q, and then run it.
I have warnings turned off, so you don't see anything happen. But if you open up your contacts old Q now, there is nothing in there. That is the blank new record.
So there is a two query solution for it. I suggest doing this instead of putting the criteria in a second query. Make this query and delete the results from this one. That way you do not change it in one and forget to change it in the other.
For those of you who are thinking that you could turn those warnings back on temporarily with a macro, I tried that too and it does not work. I tried to create a little macro because macros are simpler than VBA if you do not want to be a programmer. You can do SetWarnings: Yes, which turns the set warnings back on, then run your delete query, and then turn the warnings back off again. But guess what? It does not work. That system setting is under File, whatever it is. That takes precedence. So no matter what you do with macros, that still has all the warnings off. This only works if the warnings are already on.
So that is the non-programmer way. It is not too bad. You just run two queries. If you know how to make buttons, just open this one up first and you will get the count. Then you run the second one. It is the two-button solution. Two clicks.
For everybody else who is into some VBA, let me show you some stuff that way. Let's say one is the same thing, but we are going to use a little VBA.
Let me give you the VBA slide first. Hold on. If you have never done any VBA before, Camille, and you want to learn a little bit, go watch this video. It is not scary. It is about 20 minutes long. It will teach you everything you need to know to get started.
For this example, we are going to use a little SQL. Go watch this for a little SQL primer and how it works with Access. We are going to make a delete query in SQL. One trick I will teach you is when I first started out, I could not remember SQL to save my life. All you have to do is just look at the SQL of one of these guys. If you want to see what the syntax is for a delete query, build it here first, like I just showed you, right click and go to SQL View. That is all you need: DELETE whatever FROM whatever. We are going to do that now in code.
There are two ways to do this with VBA code. The first way, which I am going to call the slow way, is the way that is more logical if you do not know the trick. I am going to teach you the trick in a few minutes. I am all about the tricks. But I like to show you the other way first so you can appreciate the trick a little bit later.
Let's call this Delete Old Contacts. We will do the same thing we did before. Right click, Build Event. We do not need this guy open.
We are not going to Status "Hello World". We are going to Status "Deleting old contacts." If you do not know what the Status function is, that is something that I wrote. If you do not know what that is, go watch this. Basically, just put stuff in a box here instead of popping up message boxes all the time.
You will find links to all the stuff in the description box below the window if you are watching on YouTube.
We are going to delete old contacts. My algorithm here, what I am thinking in my head is: first I need to count the number of records that are in the table, save that as a value - let's call it BeforeCount, run the delete query, then count the records that are in the table after that, and subtract the two. Pretty straightforward.
So we need a couple of variables to store those counts. So: Dim BeforeCount As Long, and AfterCount As Long. Those are called variables. If you do not know what those are, go watch this video.
Now to count the number of records:
Count records in table before deletion. We can use the DCount function, so:
BeforeCount = DCount("*", "ContactT")
That says just count the number of records in the contact table. If you do not know DCount, go watch this. I have lots of videos for everything, folks.
Now we can Status it:
Status "Total count before is " & BeforeCount
Now we perform the deletion. That is going to be:
CurrentDb.Execute
There are a lot of ways to run SQL statements. There is DoCmd.RunSQL. I like this one. I have videos that explain why; you can search for those if you want to see the difference. The DELETE statement is going to look like this:
DELETE FROM ContactT WHERE ContactDate <
And then inside of dates it is going to...So you need a little pile of symbols. There are the actual thorps or the hashtags, whatever you call them, whatever the kids call them these days. And then it's going to be date minus - let's go two years this time because we already deleted some records.
In fact, I'm going to restore that table off camera. And then we'll put this guy at the end. And there we go. And I didn't do it right. Let's see. We don't need that guy at the end.
So it's going to be: DELETE FROM contact_t WHERE the contact date is less than - and this becomes a date. Right, date minus 730 days. The date gets put in here between those symbols, and now that is your SQL statement.
Now we need to count the records after we do that. So again, I'm going to copy that whole block and just stick it here. Count the records in the table after the deletion.
So now we do after_count equals that total count after. And then that's after_count. And then we can say, do some math: records deleted will be the before_count minus the after_count. And then we're done. Easy enough.
All right. The bug compiles and a while back out here now. I'm going to restore that from another backup copy. Hold on. I'm going to do that off camera real quick.
Well, this is a teaching video. I might as well show you what I'm doing. I'm just going to delete contact_t. Are you sure you want to delete it? I'm going to open up another copy of the database because I keep all kinds of copies. I always remember when I'm recording a video, I was always using a copy of the original. So here's the original. Grab you and drag you over there. There we go. That's that simple. Close it up.
So now we got all the original records in here. So now when I click my button - ready? Click. And there we go. Total count before, total count after zero. I must have deleted all of them. We deleted all of them. Oh no. What do we do? Delete everything before two years ago. I bet they were all at least two years old.
So let's do that one more time. I deleted everything. Put you back there one more time. This is why you keep backups, folks. I know this is just a teaching example, but that's why we keep backups.
So let's say instead of mine, I said certain date. Let's see what these dates look like. Let's go before these. Mostly are like April of 2023. So let's go everything before April of 2023. Let's do that.
So I actually put a date in there. We'll put the real date in there. I'll just copy that so it will delete all that old stuff. Change is sure.
So now instead of date minus 730, I'll just put in there an actual date or like that, same. And I'm using ISO dates. If you don't know what that means, that's the year-month-day format. I am on a mission to get the entire world to use this date format because the other ones are all stupid, just like the change in the clocks toys used.
So now we should get some valid data. Ready? Here we go. Delete all contacts. Go. Ah, there we go. There's my - remember the status box, the results come in backwards.
So total count before 104. Total count after 85. Records deleted: 19. And let's double check. And yes, all those old ones are gone.
So that works. That's a valid solution. You can use that if you like it. And it's pretty straightforward and simple to piece through in your head exactly what we did.
It's like counting jelly beans. You got a jar full of jelly beans. You do the contest and see who can guess it the closest. Well, you don't mall out. You count them all, put them all back in the jar. Now you reach in and take a handful of them, eat them, and you don't want to see how many you ate, but oh, wait a minute, I didn't count the number that I ate. I just grabbed a handful.
Get the jelly bean jar out again and count them. And now you know how many you ate. You just subtract the two. Now wouldn't it be nice if the jelly bean jar itself could be like, hey, you just took 18 jelly beans out of the jar? Well, it can if you know how to speak jelly bean jar-ease.
So like I said, this is the logical way that makes sense, but I'm going to show you a trick now. All right. You ready?
Well, first we got to put the contact table back one more time because we deleted all the records. So come here, get the database, and we'll stick that back in there like that. Close that up.
So now this is getting more hardcore. This is for the advanced students here. Got to understand a little bit more about objects and stuff, and recordsets and things like that. So we're going to get rid of most of this.
What we need here is a database object, and I'll explain why in a minute. So, dim db as a database. And then we can say set db equals the current database. So we got a pointer to the database we're working with.
Now we run our delete query. So db.execute. That's important. It's got to be db, not currentdb. It's got to be the same database object.
db.execute "DELETE FROM contact_t WHERE contact date is less than" - what did we do before, I forgot now, 2023 June 1st, whatever.
Now once that's done, we still have that database object open, and we can talk to it. We can say how many records were affected by that last SQL statement. So here we can say status records deleted. Watch this. It's going to be db.recordsaffected. That's the title of the video. I finally got to it after all this setup.
db.recordsaffected. And that's it.
As some astute students have caught in one of my previous videos, yes, you probably should say set db = nothing. No, you don't have to close the object. You don't have to though, and I explained why in the last Quick Queries video.
But this really is all you need. And like I said, it's four lines of code as opposed to all the stuff we did before. If you know this trick, save it.
Debug, compile. Let's take a peek at the table. We've got June 1st, 2023. Let me sort these. June. So most of them, basically.
Let's do - okay, so it should leave these July ones intact. I just want to make sure some are left. All right. Ready? And let me clean up this window. Click.
I also like to, when I'm using a status box, if I'm doing something like this, I like to say status box equals blank. It blanks the box.
Let's get to it already. Ready, click. And oh, deleted 100 records. Is that right? Let's see. Yeah, I think that was right. I got to see how many were in the table originally now.
All right. The table's got 104. Yeah. And they left 4 at the end, right? Yep. There's the 4 that are after that date. Perfect.
There you go. There's your 100 records deleted. And that's how you do it: db.recordsaffected.
If you like stuff like this, if you enjoy my classes, if you like my teaching style, or if you just want to listen to more of my voice, I got lots of developer lessons on my website.
In addition to the free TechHelp videos that I release, I've got tons and tons of Microsoft Access developer courses available. Check them out. There's a link on my website.
But that is going to do it for your TechHelp video for today. I hope you learned something, people of all skill levels. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Counting records to be deleted with a select query
Creating a select query to view records for deletion
Applying date criteria in a query to filter old records
Calculating days for date criteria in a query
Using a delete query based on another query
Saving and running a delete query in Access
Turning Access system warnings on and off
Limitation of SetWarnings in macros
Step-by-step two-query method for safe deletions
Using DCount in VBA to count records
Counting records before and after deletion in VBA
Running a delete SQL statement in VBA
Calculating records deleted by subtraction in VBA
Using the RecordsAffected property in VBA
Creating and using a Database object in VBA
Executing a delete SQL statement with db.execute
Retrieving the number of deleted records with db.RecordsAffected
COMMERCIAL:
In today's video, we're going to learn how to count the number of records deleted by a delete query in Microsoft Access. First, I'll show you a simple, non-programming way to find out how many records will be deleted before you even run the query. Then, we'll look at two different methods using VBA, including an advanced trick using the RecordsAffected property of the Database object. You'll see step-by-step how each approach works, and learn some useful Access tips along the way. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the simplest non-programming method shown in the video to find out how many records a delete query will remove in Access?
A. Create a select query with the delete criteria to preview affected records, then use a separate delete query based on that selection
B. Rely on Access system warnings exclusively to count deleted records
C. Manually count records in the table before and after running the delete query
D. Use a form with a DCount field embedded
Q2. Why does the presenter recommend turning off Access's built-in system warnings for queries?
A. It prevents queries from running automatically
B. The warnings can become annoying and repetitive, especially for experienced users who take regular backups
C. It increases database security
D. It improves the accuracy of delete and append queries
Q3. What is the typical sequence in the non-programming two-query solution?
A. Run the delete query first, then the select query
B. Run the select query to preview, then the delete query to remove the records
C. Combine select and delete into a single query
D. No query is needed, just manual deletion
Q4. When using VBA, what is the logical but slower way to determine the number of records affected by a delete action?
A. Use db.RecordsAffected directly after Execute
B. Count records manually outside Access
C. Count records in the table before and after the delete, then subtract to get the number deleted
D. Use a macro to count deleted records automatically
Q5. Which VBA function is recommended to count the number of records in a table or query?
A. CountRecords()
B. DLookup()
C. DCount()
D. RecordCount()
Q6. What is the advantage of using the db.RecordsAffected property in VBA after executing a delete query?
A. It automatically rolls back the deletion if too many records are affected
B. It efficiently returns the exact number of records affected by the last query, saving coding steps
C. It permanently disables delete queries
D. It allows you to preview records to be deleted
Q7. When constructing dates directly in SQL for delete queries in Access VBA, which format does the presenter recommend?
A. Month/Day/Year (e.g., 04/20/2023)
B. Day/Month/Year (e.g., 20/04/2023)
C. Year-Month-Day (ISO format, e.g., 2023-04-20)
D. Any date format is fine as long as it matches your Windows region
Q8. Why does the presenter recommend archiving rather than deleting old data, whenever possible?
A. Because deleting data is always slower
B. Because archiving eliminates the need for regular backups
C. Because data might be needed for future reference and restoring from backups can be difficult
D. Because Access requires a minimum number of records per table
Q9. In the video, what is the reason that temporarily turning Access warnings back on with a macro does not work if you have turned them off in Options?
A. Macros are unable to change system settings
B. The RunSQL action disables all warnings regardless of settings
C. The Access application-level setting for warnings takes precedence over macro actions
D. Delete queries ignore all custom macros
Q10. In VBA, why is it important to use the same database object (db) to call both Execute and access RecordsAffected?
A. Because using different objects can cause the count to reset, returning incorrect results
B. It does not matter, as all database objects share state in Access
C. The db object automatically creates a backup
D. The db object is only needed for select queries
Q11. What analogy does the presenter use to explain the before-and-after counting method for deleted records?
A. Filling and emptying a bathtub with water
B. Counting jelly beans in a jar before and after eating some
C. Depositing and withdrawing money from a bank account
D. Turning on and off lights in a room
Q12. If you want to see the SQL for a delete query built in Access Query Designer, what should you do?
A. Look in the table design view
B. Open the macro window
C. Right-click the query and choose SQL View
D. Check the form properties
Answers: 1-A; 2-B; 3-B; 4-C; 5-C; 6-B; 7-C; 8-C; 9-C; 10-A; 11-B; 12-C
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 is aimed at those who are at an intermediate or developer level with Microsoft Access. In this lesson, I am going to demonstrate how to determine the number of records deleted when running a delete query in Access. Many users have asked how to find out how many records will be removed by a delete query, either before actually running the deletion or right after it happens. I will walk you through a solution that does not require any programming, followed by two VBA-based approaches for those who are comfortable writing and working with code. The advanced technique will make use of the RecordsAffected property of the Database object, which might become one of your favorite tricks for streamlining these tasks.
Before jumping into the details, let me quickly say that if you are not familiar with what a delete query is or how to use one, I recommend you go review my introductory content on delete queries, which is freely available on both my website and YouTube channel. Once you have the basics, you can come back and continue with this lesson.
The topic for this lesson comes from a typical support question. Let me lay out the scenario: suppose you regularly have to remove old log records from a table and want to know, as simply as possible, how many records have been deleted. First, I will provide a method anyone can use in Access with no programming at all. Then, for those interested in VBA solutions, I will present two different ways you can approach the problem programmatically.
So let's work through this with a database that tracks contacts. Each record is essentially a note about a customer interaction. Imagine this table grows over time and, after a few years, you want to clean out records that are more than two years old. I always recommend archiving data rather than deleting it, since you never know when you might need old data in the future. However, there are situations, like with web server logs, where deleting is reasonable. For example, I analyze my web server logs, keep them for a year, and then delete them, seldom needing the really old entries.
Suppose our job is to delete all contacts before a certain year, say before 2023. The non-VBA way to determine how many records you are about to delete is quite straightforward. Create a select query that displays the contacts meeting your criteria, such as those older than two years. This lets you see exactly how many records will be deleted. Then, create a delete query that is based on the select query you just made. This two-query approach provides clarity and separation: the first shows what will be removed, the second actually performs the deletion.
If you have not disabled system warnings in Access, then you will get a prompt before a delete query runs, indicating how many records are about to be deleted. If you find these warnings as annoying as I do and have them turned off, then you will not see that count, but the two-query approach will still provide what you need. Make sure your backups are in good order before running any deletion operations, especially without warnings.
To summarize the manual, non-programming approach: first, build a select query with the criteria matching the records to be deleted, then simply open that query to see the count. Afterwards, run the delete query to remove those records. If you are familiar with adding buttons to forms, you could use one button to open the select query for the count and another to execute the deletion query.
For anyone wondering about toggling the warnings back on temporarily using a macro, unfortunately, this will not override alert settings that are disabled at the application level. Setting warnings on and off via macro only works if you have not disabled them globally in Access options.
Now, for those interested in using VBA, I will discuss solutions involving code. If you are new to VBA and want to get started, I have beginner-friendly content on that as well. Let's look at two VBA solutions.
The first VBA approach is straightforward and mirrors the manual method: count the records in the table before deletion, execute the delete query, then count the records again and subtract the two counts to determine how many were deleted. This is a logical and transparent technique. Store the initial count, run the delete query using an SQL statement in VBA, then get the new count and calculate the difference. This approach works well and is easy to follow, especially for those becoming comfortable with variables and functions like DCount.
However, there is a more direct method available for advanced users, and it saves several steps. The trick involves using the Database object's RecordsAffected property. Here is the concept: after preparing a reference to your database and running the delete query using that database object, you can immediately read how many records were affected by the last action. This allows you to know exactly how many records were deleted with just a few lines of code. It is essential to use the same Database object for both executing the query and checking the RecordsAffected property.
To review, the simple VBA solution involves counting before and after, while the advanced approach leverages the RecordsAffected property for a direct answer. When using the advanced method, just make sure you use a pointer to your Database object, execute your delete SQL through it, and then immediately check RecordsAffected. It saves you several steps and provides the desired value without a separate count operation.
These VBA methods are not only efficient but also open the door to more advanced automation and reporting in your Access applications. As always, maintaining good backups is vital whenever working with deletion queries and code that modifies your data.
If you enjoy these types of lessons and want more in-depth knowledge, I offer many developer-level Access courses and resources on my website. There are plenty of free TechHelp videos as well as complete, paid training options for those who want to take their skills further.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Counting records to be deleted with a select query
Creating a select query to view records for deletion
Applying date criteria in a query to filter old records
Calculating days for date criteria in a query
Using a delete query based on another query
Saving and running a delete query in Access
Turning Access system warnings on and off
Limitation of SetWarnings in macros
Step-by-step two-query method for safe deletions
Using DCount in VBA to count records
Counting records before and after deletion in VBA
Running a delete SQL statement in VBA
Calculating records deleted by subtraction in VBA
Using the RecordsAffected property in VBA
Creating and using a Database object in VBA
Executing a delete SQL statement with db.execute
Retrieving the number of deleted records with db.RecordsAffected
Article
If you work with Microsoft Access and need to delete a batch of records from a table, it is often important to know exactly how many records will be deleted before running the query, or at least find out afterward. There are a few different ways to do this, both with and without programming. I will walk you through a practical approach using Access queries, and then show how to do the same task with a bit of VBA code, including a more advanced method that takes advantage of the RecordsAffected property of the Database object.
Let's start with the non-programming (no VBA) approach. Suppose you regularly remove old log records from a table called ContactT and you want to see how many will be deleted. The best practice is to first create a select query in Access that shows you the records which qualify for deletion. For example, if you only want to keep records from the last two years, you can create a select query for records where the ContactDate is less than today minus 730 days (since 2 years = 730 days).
Create a new query in design view and add the ContactT table. Bring in the relevant fields, then, in the criteria row for ContactDate, put:
Give this query a name such as ContactsOldQ. When you run it, it will display all contacts older than two years. You can see the record count in the navigation bar at the bottom of the results, which matches the number of records that would be deleted. Once you have confirmed the result, you can use this query to drive your delete query.
To perform the deletion, create a new query based on your ContactsOldQ select query. Change it to a delete query, bring down the asterisk (*) to delete entire records, save it as ContactsOldDeleteQ, and run it. If you have system warnings on in Access, it will display a message warning you how many records will be deleted. If you have warnings turned off (many power users do this to avoid being interrupted by repeated warnings and confirmations), you will not see this message, but the delete will still happen. After deleting, you can re-open the ContactsOldQ query; if it returns no results, the records were successfully deleted.
Now, let's make this process more efficient with VBA. If you want to add a button or write code that deletes records and shows you the number deleted, you have a couple of approaches.
The straightforward way is to count the records before and after deletion, then find the difference. In the click event of a button, you would use something like this:
Dim BeforeCount As Long
Dim AfterCount As Long
BeforeCount = DCount("*", "ContactT")
CurrentDb.Execute "DELETE FROM ContactT WHERE ContactDate < #2023-06-01#"
AfterCount = DCount("*", "ContactT")
MsgBox "Records deleted: " & (BeforeCount - AfterCount)
Here, BeforeCount stores the total number of records in your contacts table before deletion. The delete SQL statement then removes the relevant records. Afterwards, AfterCount gives the new total, and by subtracting AfterCount from BeforeCount, you know how many records were deleted. You can swap out the date in the SQL to match your cutoff. Using ISO date format (year-month-day) inside the hash ( # ) symbols is recommended for clarity and compatibility.
While this works, there is a more efficient and advanced way using the RecordsAffected property of the Database object. Here is how you do it:
Dim db As Database
Set db = CurrentDb
db.Execute "DELETE FROM ContactT WHERE ContactDate < #2023-06-01#"
MsgBox "Records deleted: " & db.RecordsAffected
Set db = Nothing
With this method, instead of doing two separate record counts, you only run the delete statement using db.Execute. The db.RecordsAffected property then tells you exactly how many records were deleted by the last action. It is more efficient and reliable because you get the number directly from Access itself, which is especially helpful if there could be other concurrent changes to the data during your process.
If you want to use today's date minus 730 days for dynamic deletion, you can build the SQL string like this in VBA:
Dim db As Database
Set db = CurrentDb
db.Execute "DELETE FROM ContactT WHERE ContactDate < #" & Format(Date - 730, "yyyy-mm-dd") & "#"
MsgBox "Records deleted: " & db.RecordsAffected
Set db = Nothing
This approach is especially useful in automated tasks or admin utilities. Just remember to always back up your data before doing bulk deletions. Even better, consider archiving records rather than deleting them outright, in case you need old data in the future.
In summary, to count the number of records to be deleted in Access, you can use a select query first, or, with VBA, use either the difference between DCount results before and after deletion, or, more efficiently, use the RecordsAffected property of the Database object immediately after executing your delete statement. The advanced VBA method provides the cleanest and most direct result if you are comfortable working with code.
|
