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

- 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 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];

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")
   

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

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

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