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.




Access VBA Programming
Microsoft Access VBA Programming for the Absolute Beginner
ISBN: 1598633937
EAN: 2147483647
Year: 2006
Pages: 214
Authors: Michael Vine

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net