Recordset Error Handling

S

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
 
S

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
 
K

Klatuu

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.
 
S

Scott

thanks much.

Klatuu said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top