This Access VBA tutorial explains how to run Query and run Action Query in Access VBA.
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.
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.
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.
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