How to catch error in function which is called from a query (1 Viewer)

[PanKey]

Hi
In below test scenario i would like to do a rollback but my err handler is never reached. Access throws its own error " devision by zero"
Table2 holds 2 fields type double
Thevalue( is first field) TheResult ( is second field and has nulls)
5
7
0
8

Function myCalc(TV As Variant) As Double
myCalc = 10 / TV
End Function

Sub DoIt()
Dim strSql As String
On Error GoTo f
strSql = "UPDATE Table2 SET Table2.theResult = myCalc([thevalue])"
DBEngine.BeginTrans

CurrentDb.Execute strSql, dbFailOnError

v:
DBEngine.CommitTrans
Exit Sub
f:
DBEngine.Rollback
MsgBox "error"
Resume v
End Sub

Tnx in advance
Pan

 

[pbaldy]

You need to prevent it in the function. I'd test the input for null and zero, return zero (or whatever) in those instances, else the result.

 

[PanKey]

Hi paul
That will not update the single row
But the good rows will be updated and i do not want that

 

[PanKey]

There was a slight error in above code
Dbengine.committrans should be above v:
But the problem is still there

 

[pbaldy]

I'm not sure what you're saying the problem is. You can add a criteria to the SQL so it only updates a record or filtered set of records.

 

[MarkK]

You don't need to call a VBA function to do this. Do it right in the query...

UPDATE Table2 
SET theResult = 10 / thevalue 
WHERE thevalue <> 0

But the recommended option is to not save that calculation at all, because you can just write this query...

SELECT theValue, iif(theValue <> 0, 10 / theValue, 0) As theResult
FROM Table2

...which calculates the result immediately and directly from the raw data. This is always your simplest and most reliable approach.

 

[PanKey]

Maybe I should clarify
I need at regular interval to import a couple of CSV files. These files are linked tables in access. The rules and XY transformations are complex. Hence the combination of UDF's and Queries. My div by zero is a simplification of such a transformation.
The main business rule: All rows from all files should be imported as 1 batch. If 1 row fails a rollback is issued.
Proposed (but messy) answer to myself is:
Use a public variable set it to some value prior to the update(actually insert) call
use error handling in the function.Set the pubVar (if error)to something else.
Read the pubVar after the call and if different , stop the remaining transformations(on the remaining csv's) and do a rollback.
Please tell me there is another way

 

[MarkK]

Please tell me there is another way

I would import into a temporary table first, and then validate the import later, as a second step. Enough things can go wrong with just the import that doing the validation at the same time seems burdensome, to me. Pull it all in, and then test what got, and avoid putting yourself in the position where an error causes a rollback. IMO.

 

[The_Doc_Man]

Let me get a bit theoretical here. Normally, if you have an SQL statement, the rules of SQL are that regardless of the actual implementation method, the EFFECT must be as though everything was done simultaneously. There is no guarantee of the order in which ANY event fires within the melee that is occurring during the effectively monolithic operation. The only sane, simple way to do what you said (in essence, do it for all or none at all) is to pre-test for the conditions that would make it do NONE.

Therefore, MarkK's suggestion makes a lot of sense in this context.

Stated another way, you have TWO operations spread over a multitude of records, BUT they are such that the operations are interdependent as opposed to independent. That is because of your All or Nothing rule. Therefore, your choices are (a) do the update but then detect that there was an error so do a full rollback, or (b) test for the conditions that would cause the error and don't do the update in that case. No matter how you cut it you will have two steps because Access (and for that matter, ANSI SQL) lacks an "UPDATE IF AND ONLY IF EVERYTHING CAN BE UPDATED."

Having said that, it is possible for you to do a DAO operation to execute your SQL because the DAO .Execute operation includes an AUTOMATIC rollback on errors.

Dim dbDAO as DAO.Database
Dim sActQry as String
Dim lErrCode as Long
Dim sErrDesc as String

...
    lErrCode = 0
    sErrDesc - ""
    On Error GoTo BadSQL
    sActQry = "{your SQL statement goes here}"
    Set dbDAO = CurrentDB
    dbDAO.Execute sActQry, dbFailOnError
    GoTo CodeReady

BadSQL:
    lErrCode = Error.Number
    sErrDesc = Error.Description
    Resume CodeReady

CodeReady:
    If lErrCode = 0 Then GoTo ItWorked

    {here, your SQL failed AND was rolled back in its entirety}.

I'm showing you this because the DAO .Execute method IMPLIES its own set of very narrowly focused Begin/Commit/Cancel code. The alternative with DoCmd.RunSQL would be what you posted including having to build an explicit Begin/Commit/Cancel sequence.

NOTE that the SQL query you pass to .Execute must be complete. It cannot have external items from the Access environment in it at the time of passing the string. However, there is nothing to stop you from doing complex string building in the string action query holder BEFORE you make the call... which is why I showed the query string declaration as a separate step.