ACC1x: How to Perform an SQL Bulk Action Query from Access Basic

Last reviewed: November 12, 1997
Article ID: Q88654
The information in this article applies to:

- Microsoft Access versions 1.0, 1.1

SUMMARY

Access Basic does not support SQL statements to access your data without defining a virtual table (VT) object to execute the statement. For example, you cannot execute the following SQL command on a line by itself, even though it would be valid in a Microsoft Access query:

   UPDATE Cust SET Cust.[Phone] = "(206) " & [Phone];

This article describes how to perform SQL bulk action queries, and includes a Sub procedure called PerfromSQLAction that you can add to your programs. You can use this Sub procedure to pass a SQL bulk action statement to the procedure and have it executed.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the "Introduction to Programming" manual.

MORE INFORMATION

Access Basic does not support SQL statements in that you cannot execute a SQL statement on a line by itself. The following SQL command, although a valid Microsoft Access query, will generate a syntax error message in Access Basic:

   UPDATE Cust SET Cust.[Phone] = "(206) " & [Phone];

Instead of including the SQL statement on a line by itself, you have to follow steps similar to the example below to execute the SQL statement:

  1. Dimension a Database and QueryDef variable as shown below:

          Dim MyDB As Database
          Dim MyQuery As QueryDef
    

  2. Set the database variable to the current user database as shown below:

          Set MyDB = CurrentDB()
    

  3. Set the QueryDef variable to use the CreateQueryDef method, assigning an arbitrary query name such as TempQuery and including the desired SQL statement. The query you create using this step is only for the purpose of executing the bulk action query and will be deleted later. An example of how to use the CreateQueryDef method for this purpose is shown below.

    NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

          Set MyQuery = MyDB.CreateQueryDef("TempQuery" ,UPDATE _
          Cust SET Cust.[Phone] = "(206) " & [Phone];
    

  4. Execute the TempQuery query you created in the previous step using the Execute method:

          MyQuery.Execute
    

  5. Delete TempQuery using the DeleteQueryDef method as shown below:

          MyDB.DeleteQueryDef("TempQuery")
    

All of these steps, along with some error trapping, have been combined in the PerfromSQLAction Sub procedure. You can use this Sub procedure to perform bulk action queries in your Access Basic programs.

PerformSQLAction requires only the SQL command you want to execute as a string value. For example:

   PerformSQLAction "UPDATE Cust SET Cust.[Phone] = '(206) ' & [Phone];"

Note that the SQL command is always followed by a semicolon (;).

The following is a listing of PerformSQLAction:

   Sub PerformSQLAction (SQLStmt As String)
      Dim LocalDB As Database, LocalQry As QueryDef

      On Error Resume Next
      Set LocalDB = CurrentDB()
      LocalDB.DeleteQueryDef("TempQuery")
      On Error GoTo 0

      Set LocalQry = LocalDB.CreateQueryDef("TempQuery", SQLStmt)
      LocalQry.Execute
      LocalQry.Close
      LocalDB.DeleteQueryDef("TempQuery")
   End Sub
Keywords          : OdbcOthr kbusage
Version           : 1.0 1.1
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 12, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.