Microsoft Access is a Database Management System which helps to store and manage data, reporting using forms/queries/various other wizards. As discussed in older posts and articles, queries can be built in Microsoft Access using design view/SQL view/any available wizard.

In another post I explain the use of OpenQuery method of Docmd object. In this article, we will discuss another straightforward method for using queries in VBA.

DoCmd.RunSQL Statement in VBA

Just like how the Docmd.OpenQuery statement runs the named query in VBA, the DoCmd.RunSQL method can directly execute queries passed as a string parameter.

Syntax:

DoCmd.RunSQL( <string expression> )

Where the <string expression> is the query to be executed.

Some rules to be followed while framing the query (the string parameter):

1. Values for string datatypes should be within single quotes
2. Values for integer datatypes should not have any quotes
3. Table names and field names should be enclosed with square brackets [] if there are spaces in between. 

For Example: 

If the table name is “All_records”, it need not be within square brackets. But, if the table name is “All records”, then it needs to be within square brackets as there is a space in the name of the table. [All records].

Examples of Running Queries in VBA

Update Query

Here is a table of students’ exam results. It has “Pass” and “Fail” results recorded for students. The minimum pass mark was previously 50. Now, the school rules have changed. As per the new rules, 40 is the minimum passing mark. 

Note: Name of the table is “Exam Results.”

Exam Results
Result ID	ExamID	Student ID	Marks	Exam Result
1	1	STU002	42	Fail
2	2	STU006	80	Pass
3	3	STU007	50	Fail
4	4	STU004	96	Pass
5	4	STU005	100	Pass
6	5	STU008	91	Pass
7	6	STU007	78	Pass
8	7	STU010	76	Pass
9	8	STU003	84	Pass
10	9	STU009	90	Pass
11	10	STU007	67	Pass
12	11	STU006	100	Pass
13	12	STU009	75	Pass
14	13	STU006	83	Pass

So, using VBA we will update the exam result of students based on the new school rule.

Sub view_all_records()
DoCmd.RunSQL ("update [Exam Results] set [Exam Result]='Pass' where Marks >=39")
End Sub

Changes in the table’s data after the query is run.

Exam Results
Result ID	ExamID	Student ID	Marks	Exam Result
1	1	STU002	42	Pass
2	2	STU006	80	Pass
3	3	STU007	50	Pass
4	4	STU004	96	Pass
5	4	STU005	100	Pass
6	5	STU008	91	Pass
7	6	STU007	78	Pass
8	7	STU010	76	Pass
9	8	STU003	84	Pass
10	9	STU009	90	Pass
11	10	STU007	67	Pass
12	11	STU006	100	Pass
13	12	STU009	75	Pass
14	13	STU006	83	Pass

Select Into Query

From an existing table, we will pull out the failures into another table to give them rigorous coaching in the failed subject.

Exam Results
Result ID	ExamID	Student ID	Marks	Exam Result
1	1	STU002	42	Pass
2	2	STU006	80	Pass
3	3	STU007	24	Fail
4	4	STU004	96	Pass
5	4	STU005	100	Pass
6	5	STU008	91	Pass
7	6	STU007	78	Pass
8	7	STU010	36	Fail
9	8	STU003	14	Fail
10	9	STU009	90	Pass
11	10	STU007	67	Pass
12	11	STU006	100	Pass
13	12	STU009	29	Fail
14	13	STU006	83	Pass

VBA code for the same would be:

 Sub view_all_records()

DoCmd.RunSQL ("select * into [coaching list] from [Exam Results] where [Exam Result]='Fail'")

End Sub

Here is the newly created table:

Queries Supported By RunSQL Command

The category of queries supported by RunSQL method are: 

1. Alter table
2. Insert into
3. Delete
4. Drop index
5. Drop table
6. Create table
7. Update table
8. Select into
9. Create index

Notice, however, that this does not include the normal select query. 

RunSQL Does Not Support the Normal Select Query

The select query is run in order to view some data. 

For example, to display all or some records of one or more tables based on a criterion, we use a select query. You could think of these displayed table results as a return value. The RunSQL method unfortunately does not support a return value/object. So, it cannot be used to run any kind of select queries except the “select into” statement.

Conclusion

When compared to Openquery method of the Docmd object, this RunSQL method does have some pros and cons.

Pros

1. Easy to spot the fields and table names used across queries because all queries are directly called out in the VBA module. 
2. Easy view of all queries.

Cons

1. Select queries are not supported by RunSQL method.
2. Debugging and maintenance is not convenient as in the query window. 
3. Running a single query based on your requirements might demand some time as it either needs to be run in a separate sub procedure or should be copy pasted to a query window to run it as a standalone query.
4. Looking at so many queries at one go in a single module can confuse the user if there are no comments. While open query is used, at least the name of the query can describe what the query does.

In a nutshell, it completely depends on the developer/user to define which is convenient to use and easy to maintain as both the methods (RunSQL and Openquery) have their own pros and cons. What looks great in some situations is horrible in others and vice versa.

If you get a chance, try both the methods and leave your experience here in the comments.