Recordset Error Handling

[Scott]

My function below gets the max date from a "Linked" table. It works fine
except if the file "c:\data\myBackend.mdb" doesn't exist. When that file
isn't there, I get the below error.

The error is on the "objRS.Open sSQL" line of code. Is there a way to add
error handling to this function to trap this error? I thought using the "If
Not objRS.EOF" would work, but unfortunately the error happens as soon as
the recordset is open.


ERROR *************

Could not find file c:\data\myBackend.mdb

CODE **************

Public Function GetMaxData()

Dim objRS As ADODB.Recordset, Conn As ADODB.Connection, sSQL As String
Set objRS = New ADODB.Recordset

objRS.ActiveConnection = CurrentProject.Connection
objRS.CursorType = adOpenForwardOnly
objRS.LockType = adLockReadOnly
Set Conn = CurrentProject.Connection

sSQL = "SELECT MAX(myDateField) FROM myTable"
objRS.Open sSQL, CurrentProject.Connection

If Not objRS.EOF Then
GetMaxData = objRS.Fields(0).Value
Else
GetMaxData = "n/a"
End If

objRS.close
Set objRS = Nothing

End Function

 

[Steve C]

Hi Scott,

An option may be to check for the file existence at the start of your
function using the Dir() function. Something like

If Dir("c:\data\myBackend.mdb") <> "" Then
'file exists
Else
'file not found
End If

HTH
Steve C

 

[Guest]

Add standard error handling:

Public Function GetMaxData()

Dim objRS As ADODB.Recordset, Conn As ADODB.Connection, sSQL As String

On Error GoTo GetMaxData_Error

Set objRS = New ADODB.Recordset

objRS.ActiveConnection = CurrentProject.Connection
objRS.CursorType = adOpenForwardOnly
objRS.LockType = adLockReadOnly
Set Conn = CurrentProject.Connection

sSQL = "SELECT MAX(myDateField) FROM myTable"
objRS.Open sSQL, CurrentProject.Connection

If Not objRS.EOF Then
GetMaxData = objRS.Fields(0).Value
Else
GetMaxData = "n/a"
End If

objRS.Close
Set objRS = Nothing

GetMaxData_Exit:

On Error Resume Next
Exit Function

GetMaxData_Error:

If Err.Number = 9999 Then
MsgBox "File Not Found"
Else
MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & _
") in procedure GetMaxData of Module Module1"
End If
GoTo GetMaxData_Exit

End Function

Change the line If Err.Number = 9999 Then to the actual number of the error
if you want to give it a special message. You should have error handling in
all your procedures.

 

[Scott]

thanks much.

Add standard error handling:

Public Function GetMaxData()

Dim objRS As ADODB.Recordset, Conn As ADODB.Connection, sSQL As String

On Error GoTo GetMaxData_Error

Set objRS = New ADODB.Recordset

objRS.ActiveConnection = CurrentProject.Connection
objRS.CursorType = adOpenForwardOnly
objRS.LockType = adLockReadOnly
Set Conn = CurrentProject.Connection

sSQL = "SELECT MAX(myDateField) FROM myTable"
objRS.Open sSQL, CurrentProject.Connection

If Not objRS.EOF Then
GetMaxData = objRS.Fields(0).Value
Else
GetMaxData = "n/a"
End If

objRS.Close
Set objRS = Nothing

GetMaxData_Exit:

On Error Resume Next
Exit Function

GetMaxData_Error:

If Err.Number = 9999 Then
MsgBox "File Not Found"
Else
MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & _
") in procedure GetMaxData of Module Module1"
End If
GoTo GetMaxData_Exit

End Function

Change the line If Err.Number = 9999 Then to the actual number of the
error
if you want to give it a special message. You should have error handling
in
all your procedures.