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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *