Access VBA run Query or run Action Query

This Access VBA tutorial explains how to run Query and run Action Query in Access VBA.

You may also want to read:

Access VBA delete Table records with SQL using DoCMD.RunSQL Method

Access VBA run Query or Action Query

Imagine you have to run 10 Action Query (e.g. Create Table, Update, Delete Table) in a row to get the final result, you have to run each Action Query one by one and the most annoying part is to click the confirmation dialogs. With Access VBA we can run a series of Action Query automatically and skip all the confirmation dialogs, saving a lot of time.

In the following examples, I will demonstrate how to run Action Query without any confirmation dialogs by using Database.Execute Method.

Create Table

Suppose you have already created a Create Query called “Create Payroll History”, now we want to run it with VBA

Public Sub createTable()
  Dim dbs As DAO.Database
  Set dbs = CurrentDb
  dbs.Execute "Create Payroll History"
End Sub

This VBA will cause an error if the table you want to create already exists, so you should either add a line of code to delete the Table first, or consider using Append Query.

Append Table / Update Table / Delete Table Records

Suppose you have already create a Query for Append / Update / Delete. To run the Action Query, simply replace the “Query Name” with your Action Query name.

Public Sub append()
 Dim dbs As DAO.Database
 Set dbs = CurrentDb
 dbs.Execute "Query Name"
End Sub

Other non-Action Query

The following examples are not Action Query but I think they are relevant to this topic so I also put them here for easy reference.

Delete Table

To delete Table using VBA, use DoCmd.DeleteObject Method. For example, to delete a Table named “Table1”, use the below code.

Public Sub delTbl()
   DoCmd.DeleteObject acTable, "Table1"
End Sub

This Method does not trigger any confirmation dialog.

Copy Table

To copy Table1 as Table2

Public Sub copy() 
 Dim dbs As DAO.Database
 Set dbs = CurrentDb
 DoCmd.CopyObject , "Table2", acTable, "Table1"
End Sub

Run SQL statement

Declare a SQL statement as a text variable, then use DoCMD.RunSQL Method to run SQL statement. Unlike Database.Execute Method, using DoCmd.RunSQL will generate confirmation dialog, use DoCmd.SetWarnings Method to disable dialog.

Public Sub updateSQL()
    DoCmd.SetWarnings False
    strSQL = "UPDATE [Schedule_Table] SET [Empl ID] = '010' WHERE [Empl ID]='001'"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub