Function to Run Action Query

In MS Access, action query is query that changes data or copies data. Action query differs with select query that returns a set of records. Using action query, we can append, delete, and update records in a table. We can create a table, as well. There are four types of action queries as follows:

1. Append, query for adding new records at the end of an existing table.
2. Delete, query for deleting records that match the specified criteria.
3. Make-Table, query for creating a new table.
4. Update, query for updating records according to specified criteria.

Visual Basic for Application in MS Access provides methods for running action query. In spite of what has just been said, we can create our own function to run the action query as follows:

Function runActionQuery(strSql As String)
' Parameter:
' strSql = any action query used to add new, update, or
' delete records, as well as create new table.
On Error GoTo Err_Msg
    daoDbs.Execute strSql, dbFailOnError
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function runActionQuery, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

The strSql parameter in runActionQuery function is an SQL statement with string data type, mandatory in nature. The runActionQuery function must be executed after a database has been opened. Therefore, make sure that letDbsOpen has been carried out before executing runActionQuery function. Thus, the programming sequence is as follows:

1. letDbsOpen
2. runActionQuery

Inside runActionQuery, there is object named daoDbs that must be set using letDbsOpen. If daoDbs object is not set, a run-time error #91 occurred, saying that object variable not set.

Implementation

The following example open a database named daoDbs that is set by using letDbsOpen and execute a query to update (UPDATE QUERY) a record in a table named tblPrimaryAccounts, field named primaryaccountName and set its value to 'Accounts Payable' (text data type), where the criterion field named primaryAccountCode equal to '20200' (text data type).

letDbsOpen
runActionQuery "UPDATE tblPrimaryAccounts SET primaryaccountName='Accounts Payable' WHERE primaryAccountCode='20200'"

The following example open a database named daoDbs that is set by using letDbsOpen and execute a query to append a new record (APPEND QUERY) in a table named tblPrimaryAccounts, field names are primaryAccountCode, primaryaccountName, and primaryaccountGroup and set the respective values to '10010','KAS SHS',1.

letDbsOpen
runActionQuery "INSERT INTO tblPrimaryAccounts (primaryAccountCode, primaryaccountName, primaryaccountGroup) VALUES('10010','KAS SHS',1)"

The following example open a database named daoDbs that is set by using letDbsOpen and execute a query to create a new table (MAKE-TABLE QUERY) named tblPrimaryAccountsTemp, and copy the records from tblPrimaryAccounts table that have primaryaccountGroup equal to '1' to tblPrimaryAccountsTemp table.

letDbsOpen
runActionQuery "SELECT tblPrimaryAccounts.* INTO tblPrimaryAccountsTemp FROM tblPrimaryAccounts WHERE primaryaccountGroup='1';"

Noted here that all fields that are stated in the strSQL must comply in both the type and value. For example, a field with its text/string data type must use '' (in between single quote, e.g. 'Accounts', 'Tokyo', etc). Another example, a field with its date data type must use ## (in between hash sign, e.e. #12/31/2015#, #2/20/2016#, etc). If not complied in both field type and value, a data type mismatch error occurred.