Docmd.RunSQL in Access VBA
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.
Contents
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):
- Values for string datatypes should be within single quotes
- Values for integer datatypes should not have any quotes
- 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:
- Alter table
- Insert into
- Delete
- Drop index
- Drop table
- Create table
- Update table
- Select into
- 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
- Easy to spot the fields and table names used across queries because all queries are directly called out in the VBA module.
- Easy view of all queries.
Cons
- Select queries are not supported by RunSQL method.
- Debugging and maintenance is not convenient as in the query window.
- 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.
- 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.