RunSQL

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.