You can use the OpenQuery method to run any query but then have to deall with turning warnings on and off when the query is an action query. Plus, that method won't tell you anything if the query fails completely or even partially. The following deals with another method that dispels with the warnings plus can tell you how many records were affected. What's NOT here is the ability to roll back the changes if there is any type of failure. That's a bit more complex and isn't present because I have no idea if it's important.
To call for running action query and not utilize the function return value:
RunActionQuery "001-Make total Orders"
To call same but utilize the return value:
If RunActionQuery "001-Make total Orders" = False Then...
- do whatever, such as Exit Sub. Similarly, a test for True could be used if more compatible with whatever follows it.
NOTE: requires a reference to the DAO library in the vb editor (Tools, References)
Code:
Function RunActionQuery (qryName As String) As Boolean
Dim db As DAO.Database
RunActionQuery = False 'ensure return value of function is False
On Error GoTo errHandler
Set db = CurrentDb
db.Execute qryName, dbFailOnError
msgbox db.RecordsAffected & " records were affected."
RunActionQuery = True 'if no error, function returns True
exitHere:
Set db = Nothing
Exit Function
errHandler:
'IFs or Select Case block could be added here to customize messages
msgbox "Error " & err.Number & ": " & err.Description
Resume exitHere 'Function return value remains False
End Function
To call non-action query: 0=can add only; 1=can add, edit, view; 2=read only I left off the view mode option, assuming the default would suffice.
Notes:
- warnings don't apply as this isn't an action query
- this could be a sub instead of function, since it isn't returning anything. If a return value is needed (say to suspend further code from executing in the module that called this procedure, you have the example above on how to modify it to be a function instead.
RunSelQry "qryYourSelectQueryName", 2
Code:
Sub RunSelQuery (qryName As String, Mode As Long)
On Error GoTo errHandler
DoCmd.OpenQuery qryName,,Mode
exitHere:
Exit Sub
errHandler:
'IFs or Select Case block could be added here to customize messages
msgbox "Error " & err.Number & ": " & err.Description
Resume exitHere
End Sub
BTW - spaces, names, numbers, special characters (save for underscore _ ) for any object or variable name is just a bad idea - https://access-programmers.co.uk/for...d.php?t=225837