RunSQL v Execute
By Richard Rost
2 years ago
Difference Between RunSQL & Execute in Access
In this Microsoft Access tutorial, I'm going to teach you the difference between DoCmd.RunSQL and CurrentDb.Execute and when you would want to use each.
Mike from Newcastle, England (a Platinum Member) asks: I've been following your Microsoft Access videos for years, and you used to always use DoCmd.RunSQL in your code. But recently, I've noticed you using CurrentDb.Execute instead. What's the difference between the two, and what should I use?
Members
Members will learn how to trap errors in db.Execute commands with the dbFailOnError parameter. We will also learn how to count how many records were affected with any SQL command by using the RecordsAffected property.
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
Rick's Picks
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, What is the difference between docmd.runsql and currentdb.execute, Execute vs RunSQL, CurrentDb.Execute vs. docmd.runsql, suppress warnings, handle errors, dbFailOnError, RecordsAffected, run-time error 3061, too few parameters expected 1
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today we're going to answer that age-old question for Access developers: What's the difference between DoCmd.RunSQL and CurrentDb.Execute? Inquiring minds want to know. And by inquiring minds, I mean Mike from Newcastle, England, one of my Platinum members, and a few other people. I get asked this all the time.
Mike says, "I've been following your Microsoft Access videos for years, and you used to always use DoCmd.RunSQL in your code, but recently I've noticed you using CurrentDb.Execute instead. What's the difference between the two, and what should I use?"
Well, Mike, like everyone else, I keep learning more about Access myself. In fact, I consider it a bad day if I don't learn something new. I try to learn something new every day. I read a lot of other Access websites and blogs like NoLongerSet and a few others. I'll put some links down below to some of my favorites. As time goes on and I continuously learn new things, I change my technique from time to time.
When I first started programming in Access VBA and using SQL, I used the RunSQL command a lot. It's easy and good enough for most developers. However, the more experience you get, the more you realize that Execute is where the real power lies. So let's talk about the differences.
Of course, I'll start by saying, if you're currently using RunSQL and you're happy with it, and it works for your database, stick with it. It's fine. There's nothing wrong with it. There are just some extra capabilities that Execute can offer that can really take your database to the next level.
So, RunSQL operates within the Access user interface. It's interpreted by Access before the database engine gets it. There's a database engine that sits kind of below the Access front end, the interface that you work with. And Execute sends commands directly to the database engine.
Why is this important? Well, RunSQL, since it's working within the Access interface, knows about the different objects in the interface like forms and their fields. So if you format a RunSQL statement like this and put Forms!CustomerF!CustomerID inside the SQL statement, it can handle it. If you try that with Execute, you're going to get a "too few parameters" error message. Now, you can still reformat the code. You can still say "WHERE CustomerID = " and then use some string concatenation. This will essentially figure out what that CustomerID is at the VBA level before sending it to the Execute command, so you just have to structure things a little bit differently.
The reason why RunSQL is better for beginner developers is that it will give you warning messages. You can turn these messages off either at the system level under the File Options, Access Options, or you can turn them off with the SetWarnings command. Execute is completely silent; it doesn't give you any pop-ups or dialog boxes. It just runs your SQL.
And of course, if you want to learn how to turn off those warnings for RunSQL, I have a video for you. You'll find a link down in the link section below.
Continuing on, RunSQL can prompt the user for parameters. For example, you could say "SET FamilySize = " and then put a parameter inside of your SQL, and it will prompt you for it. I personally would never use this. I would get that value from a form field myself. I don't like those prompt parameter value pop-ups, so go watch this video to learn how to do that.
RunSQL will give you a progress meter down below on the status bar, at the very bottom of the Access window. If you're running a query that takes a while, you'll see the little progress meter go across the bottom. Execute does not give you that. It is completely silent. But this will be the topic of a future video. There is a way to get some kind of visual feedback, and I'll talk about that later.
RunSQL is relatively slow, and by that, I mean for small queries with a few dozen or hundred records, we're talking milliseconds. But if you've got a big query, Execute might crunch that down to a fraction of that time. So if you've got 100,000 records to crunch through, you could be talking about the difference between 10 seconds and two minutes.
RunSQL can be used in a macro; Execute cannot. But I almost never use macros, so I'm not worried about that. I will add that, yes, you can use a wrapper function and use RunCode in a macro, and then use that to run your Execute command. But why go through all that? Just don't use a macro.
RunSQL can be canceled by the user when they get that prompt up front that says, "Hey, you're about to run an action query." If they say no, it will cancel the RunSQL command, but it will generate an error. You'll see it right there: "Operation canceled by user." So you'll have to trap that error with some error handling on your own. And of course, error handling is covered in this video.
Execute commands cannot be canceled by the user but they can be rolled back using what's called a transaction. Again, this will be covered in a future video. You can execute the SQL, check some conditions, and if you don't like what happened, you can roll it back if the whole thing is encapsulated inside of a transaction.
The problem with RunSQL that I don't like is that it cannot programmatically handle errors. If an error happens during RunSQL and you've turned off those nuisance warnings, you won't see anything. With Execute, you can trap errors using a parameter called dbFailOnError. So, if errors occur while Execute is running, you can then use error handling to see what happened. And I'll be talking about this more in the Extended Cut for the members.
Basically, with RunSQL, once you turn off those warning messages, you're completely blind. RunSQL cannot programmatically tell you how many records were affected. Execute has a RecordsAffected property that will tell you exactly how many records were affected.
So in summary, RunSQL runs at the Access interpreter level, whereas Execute goes directly to the database engine. RunSQL can see forms; Execute cannot. RunSQL will give you warning messages; Execute does not. RunSQL has the option for parameters; Execute does not. RunSQL has a progress meter; Execute does not. RunSQL can be used in macros; Execute cannot. RunSQL can be canceled; Execute cannot. RunSQL is slow; Execute is faster. And Execute can be rolled back in transactions, whereas RunSQL cannot.
If you want to learn more about programming and VBA with Microsoft Access, check out my Developer lessons. I also earlier promised you a list of some of my favorite Access sites, the ones that I read on a regular basis. Here they are, in no particular order, although I do like NoLongerSet the best. I'll put links to these down below as well.
In the Extended Cut for members, we're going to take a look at trapping errors using dbFailOnError. I'm also going to show you how to count the number of records that were affected. You're going to see how the RecordsAffected property can be used in Execute commands. Silver members and up get access to all of my Extended Cut videos, including this one.
|
