> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Avoid DoCmd.RunSQL in Microsoft Access
- URL: https://nolongerset.com/avoid-docmd-runsql/
- Published: 2021-08-04T03:07:25.000Z
- Updated: 2026-05-08T13:03:14.000Z
- Description: If you are just starting out writing VBA in Microsoft Access, you may be tempted to use DoCmd.RunSQL. Don't. There is a better way.
- Author: Mike Wolfe
- Tags: Basic, Defensive Programming, #Import 2026-05-20 02:59

## TL;DR

Instead of...

```vba
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM MyTable"
DoCmd.SetWarnings True
```

...use...

```vba
CurrentDb.Execute "DELETE * FROM MyTable", dbFailOnError
```

...or better yet...

```vba
Dim Db As DAO.Database
Set Db = CurrentDb
Db.Execute "DELETE * FROM MyTable", dbFailOnError
Debug.Print Db.RecordsAffected
```

## What's Wrong With DoCmd.RunSQL?

When you call `[DoCmd.RunSQL](https://docs.microsoft.com/en-us/office/vba/api/access.docmd.runsql)`, Microsoft Access is going to show a confirmation window before executing the query. Here's an example from a DELETE query:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/08/image-13.png)

If the user clicks \[Yes\], the Delete query will execute and the records will be removed from the table. If the user clicks \[No\], Access will raise a runtime error:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/08/image-14.png)

To avoid these prompts, a quick Google search will likely lead you to the most expedient solution: the [DoCmd.SetWarnings method](https://docs.microsoft.com/en-us/office/vba/api/access.docmd.setwarnings). Simply turn the warnings off, execute the RunSQL method, then turn the warnings back on.

The problem with this approach is that it's all or nothing. Turning off the warnings hides the confirmation messages and the action canceled errors. But it also hides errors you may not be expecting.

For example, when you execute an INSERT INTO via the .RunSQL method, Access will check for the following potential issues:

- Type conversion failures
- Key violations
- Lock violations
- Validation rule violations

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/08/image-16.png)

If you SetWarnings to False, then you will not see the above error message. You would have no way of knowing that one of the records you tried to append failed due to a key violation.

### Logic errors are very bad

Ignoring these kinds of warnings amounts to a **[logic error](https://nolongerset.com/some-bugs-are-better-than-others/)**. 

Setting the Warnings to False will not interrupt the flow of the program. It won't show extra messages to the user. It won't cause the Access Runtime to crash without explanation. 

Instead, it will do something much worse. 

It will silently create bad data in your system. Like all logic errors, this could go undetected for months or even years. And that is *NOT* a good situation to find yourself in one day.

## The DAO .Execute Method

The [DAO Execute method](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-execute-method-dao) can be used in place of the RunSQL function. As the name indicates, it executes an SQL action query (`UPDATE`, `INSERT INTO`, `DELETE`). 

You should *ALWAYS* include the **dbFailOnError** option when using the Execute method. From the official documentation:

> In a Microsoft Access workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the **Execute** method won't fail — even if not a single row can be modified or deleted. Therefore, always use the **dbFailOnError** option when using the **Execute** method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.

Leaving off the **dbFailOnError** option is the equivalent of turning off all warnings on the DoCmd.RunSQL method.

With **dbFailOnError**, this is an example of the error that would be raised if you did not properly handle duplicate values:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/08/image-17.png)

## The CurrentDb Method

The [Application.CurrentDb method](https://docs.microsoft.com/en-us/office/vba/api/access.application.currentdb) returns a reference to the currently loaded database object in Microsoft Access (i.e., the front-end file). The key thing to understand about this function is that it returns a new reference *every time it is called*.

Thus, you can't use the CurrentDb method for DAO Database properties that rely on methods being called on the *same instance* of the database object, such as [RecordsAffected](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-recordsaffected-property-dao). Case in point, calling the RecordsAffected method on the CurrentDb method will *always* return zero.

Thus, it's a good habit to always assign the return value of the CurrentDb method to a DAO.Database object variable. Then use the object variable when calling the .Execute method. 

Here's a quick example:

```vba
'--== BAD ==--'
'There are five records in MyTable
CurrentDb.Execute "DELETE * FROM MyTable", dbFailOnError

?CurrentDb.RecordsAffected
 0 

'--== GOOD ==--'
Dim Db As DAO.Database
Set Db = CurrentDB

'There are five records in MyTable
Db.Execute "DELETE * FROM MyTable", dbFailOnError

?Db.RecordsAffected
 5
```

---

### External references

[DoCmd.RunSQL method (Access)Microsoft Docso365devx](https://docs.microsoft.com/en-us/office/vba/api/access.docmd.runsql)

[DoCmd.SetWarnings method (Access)Microsoft Docso365devx](https://docs.microsoft.com/en-us/office/vba/api/access.docmd.setwarnings)

[Application.CurrentDb method (Access)Microsoft Docso365devx](https://docs.microsoft.com/en-us/office/vba/api/access.application.currentdb)

[Database.RecordsAffected property (DAO)Microsoft Docso365devx](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-recordsaffected-property-dao)

### Referenced articles

[Some Bugs are Better than OthersNot all bugs are created equal. Avoid the expensive ones by making more of the ones that are easy to find and fix.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/03/car-2122580_1920.jpg)](https://nolongerset.com/some-bugs-are-better-than-others/)

*Image by [Here and now, unfortunately, ends my journey on Pixabay](https://pixabay.com/users/alexas%5Ffotos-686414/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=1971756) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=1971756)*