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

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:

Dimension a Database and QueryDef variable as shown below:

Dim MyDB As Database
Dim MyQuery As QueryDef

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

Set MyDB = CurrentDB()

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

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

MyQuery.Execute

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 : kbusage OdbcOthr
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbhowto