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 IDExamIDStudent IDMarksExam Result
11STU00242Fail
22STU00680Pass
33STU00750Fail
44STU00496Pass
54STU005100Pass
65STU00891Pass
76STU00778Pass
87STU01076Pass
98STU00384Pass
109STU00990Pass
1110STU00767Pass
1211STU006100Pass
1312STU00975Pass
1413STU00683Pass

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 &gt;=39")
End Sub

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

Changes in the table’s data after the query is run.
Exam Results
Result IDExamIDStudent IDMarksExam Result
11STU00242Pass
22STU00680Pass
33STU00750Pass
44STU00496Pass
54STU005100Pass
65STU00891Pass
76STU00778Pass
87STU01076Pass
98STU00384Pass
109STU00990Pass
1110STU00767Pass
1211STU006100Pass
1312STU00975Pass
1413STU00683Pass

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 IDExamIDStudent IDMarksExam Result
11STU00242Pass
22STU00680Pass
33STU00724Fail
44STU00496Pass
54STU005100Pass
65STU00891Pass
76STU00778Pass
87STU01036Fail
98STU00314Fail
109STU00990Pass
1110STU00767Pass
1211STU006100Pass
1312STU00929Fail
1413STU00683Pass

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
Pasting rows into a table.

Here is the newly created table:

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 *