If Then to trap ODBC error

[sxschech]

Hi,

Anyone know the syntax to trap for an ODBC error. Have a dlookup pointing to an oracle function that is currently not working. As a temporary measure I put a local table in my access 2010 database, but would like to refer to Oracle once the function has been corrected by the dba. I tried:

if iserror (CDate(DLookup("SPRG_CENSUS", "qrySPRGCensusDate"))) then
 dlookup("SPRG_CENSUS","qrySPRGCENSUSDATE_HARDCODE")
else 
CDate(DLookup("SPRG_CENSUS", "qrySPRGCensusDate"))
end if

It still stops on the dlookup pointing to oracle instead of continuing on with the THEN portion.

 

[Andrzejek]

How does your iserror function look like?

Something like

Function [blue]iserror[/blue](...) as Boolean
...
End Function

Have fun.

---- Andy

 

[Andrzejek]

OK, how about this:

If [blue]OrackeFunctIsWorking[/blue] then
    dlookup("SPRG_CENSUS","qrySPRGCENSUSDATE_HARDCODE")
else 
    CDate(DLookup("SPRG_CENSUS", "qrySPRGCensusDate"))
end if

===========================

Private Function [blue]OrackeFunctIsWorking[/blue]() as Boolean

On Error GoTo MyErrHandler
CDate(DLookup("SPRG_CENSUS", "qrySPRGCensusDate"))
[blue]OrackeFunctIsWorking[/blue] = True

Exit Function
MyErrHandler:
[blue]OrackeFunctIsWorking[/blue] = False
End Function

Have fun.

---- Andy

 

[sxschech]

Thanks Andy. With a few minor changes, got it working. Had to reverse the position of the lookups and make a slight change in the function...

If OrackeFunctIsWorking Then
                CenDate = CDate(DLookup("SPRG_CENSUS", "qrySPRGCensusDate"))
                MsgBox "use oracle function"
            Else
                CenDate = CDate(DLookup("SPRG_CENSUS", "qrySPRGCENSUSDATE_HARDCODE"))
                MsgBox "use local table 'qrySPRGCENSUSDATE_HARDCODE'"
            End If

and in my existing code, changed the following line

If CDate(Me.txtRunDate) <= CDate(DLookup("SPRG_CENSUS", "qrySPRGCensusDate")) Then

to

If CDate(Me.txtRunDate) <= CenDate Then

In the function, received an error on
[tt]
CDate(DLookup("SPRG_CENSUS", "qrySPRGCensusDate"))
[/tt]
so changed to
[tt]
oradate = DLookup("SPRG_CENSUS", "qrySPRGCensusDate")
[/tt]

Private Function OrackeFunctIsWorking() As Boolean

On Error GoTo MyErrHandler
oradate = DLookup("SPRG_CENSUS", "qrySPRGCensusDate")
OrackeFunctIsWorking = True

Exit Function
MyErrHandler:
OrackeFunctIsWorking = False
End Function

 

[MazeWorX]

since you haven't provided the err.number you could execute based weather an error exists or not as a work around

If Err.Number = 0 Then
'no error has occurred
Else
'error has occurred
End If


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[sxschech]

Sorry forgot about the error number. Though not necessary since Andy's solution seems to be working, but for reference, here is the full message:
[tt]
Run-time error '3146':

ODBC--call failed.
[Microsoft][ODBC driver for Oracle][Oracle]ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "Stu.FUNCTIONS", line 22 (#1422)
[/tt]

We know why the oracle function is not working, but a different dept is responsible for fixing that, so that is why doing a local table in the mean time.

 

[MazeWorX]

so to truly trap this error

If Err.Number = 3146 Then
'error has occurred
Else
'no error has occurred
End If

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[sxschech]

Thanks MaZeWorX, I tried your method in a different database and it works too.