Function to Run Delete Query

Though the runActionQuery function is enough to execute all action queries types, we can create a more specific function to run an action query for deleting records. In general, deleting records has the same method as the runActionQuery. The only difference is that we insert required parameters to delete records. Usually, deleting records in a table has general pattern of SQL statement as follows:

"DELETE * FROM " & strTableName & " WHERE " & strFieldName & "=" & varFieldValue

Thus, the function to run delete query is as follows:

Function runDeleteQuery(strTableName As String, strFieldName as String , varFieldValue As Variant)
' Parameters:
' strTableName  = String expression refer to table name.
' strFieldName  = String expression as the field name reference for deleting records
' varFieldValue    = Variant data type as the value for strFieldName field name
'                 use single quote '' for string value e.g. 'John'
'                 use hash sign ## for date value e.g. #1/1/2016#
On Error GoTo Err_Msg
  runActionQuery "DELETE * FROM " & strTableName & _
              " WHERE " & strFieldName & "=" & varFieldValue
Exit_Function:
  Exit Function
Err_Msg:
  MsgBox "Function runDeleteQuery, Error # " & str(Err.Number) & ", source: " & Err.Source & _
  Chr(13) & Err.description
  Resume Exit_Function
End Function

The runDeleteQuery has three mandatory parameters:

1. strTableName, string data type specifies a table name
2. strFieldName, string data type specifies a field name in a table named strTableName. This parameter is included in the criterion.
3. varFieldValue, variant data type specifies the value for related field name. This parameter is included in the criterion.

The runDeleteQuery function must be executed after a database has been opened. Therefore, make sure that letDbsOpen has been carried out before executing runDeleteQuery function. Thus, the programming sequence is as follows:

1. letDbsOpen
2. runDeleteQuery

Implementation:

To perform runDeleteQuery, varFieldValue must comply with strFieldName data type. For example, for a strFieldName with its text/string data type, the varFieldValue must use '' (in between single quote, e.g. 'Accounts', 'Tokyo', etc). Another example, for a strFieldName with its date data type, the varFieldValue must use ## (in between hash sign, e.e. #12/31/2015#, #2/20/2016#, etc). If varFieldValue is not complied with strFieldName data type, a data type mismatch error occurred.

The following example open a database named daoDbs that is set by using letDbsOpen and execute a query to delete (DELETE QUERY) a record in a strTableName equal to tblPrimaryAccounts, strFieldName equal to primaryaccountCode and set its varFieldValue equal to '10009' (text data type).

letDbsOpen
runDeleteQuery "tblPrimaryAccounts", "primaryAccountCode", "'10009'"

The following example open a database named daoDbs that is set by using letDbsOpen and execute a query to delete (DELETE QUERY) a record in a strTableName equal to tblVoucherTemp, strFieldName equal to vouchId and set its varFieldValue equal to 2 (long integer data type).

letDbsOpen
runDeleteQuery "tblVoucherTemp", "vouchId", "2"

The following example open a database named daoDbs that is set by using letDbsOpen and execute a query to delete (DELETE QUERY) a record in a strTableName equal to tblVoucherTemp, strFieldName equal to vouchDate and set its varFieldValue equal to #1/25/2016# (date data type).

letDbsOpen
runDeleteQuery "tblVoucherTemp", "vouchDate", "#1/25/2016#"