Microsoft Access VBA Programming for the Absolute Beginner

Runs a SQL statement.

Syntax

DoCmd.RunSQL SQLStatement[, UseTransaction]

with the following parameters:

SQLStatement

A SQL statement for an action query or a data definition query.

UseTransaction

A Boolean that indicates whether the query is included in a transaction. Its default value is True.

Example

The following UPDATE query replaces the substring “Street” in the txtAddress field of the tblCustomer table with the substring “St.”:

Public Sub RunStreetUpdate() Dim strSQL As String ' Make backup copy of tblCustomer DoCmd.CopyObject , "tblCustomer Backup", acTable, "tblCustomer" ' Change "Street" to "St." strSQL = "UPDATE tblCustomer " & _ "SET tblCustomer.txtAddress = Replace([tblcustomer].[txtAddress],'Street','St.') " & _ "WHERE ((InStr([tblcustomer].[txtaddress],'Street')>0));" DoCmd.RunSQL strSQL End Sub

Comments

• An action or data definition query includes the SQL statements INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX.

• To run other queries, use the OpenQuery method.

• The RunSQL method can run against MDB databases only.

• If you copy the SQL code from the query window’s SQL view, you may have to replace embedded double quotation marks with single quotation marks.