error trapping with ODBC

[ddodd]

I need to understand how to detect and recover from a network down / server unavailable situation.
i'm reading a db (sound level) meter from a serial port and writing to a local access db on same PC 24x7.
the code below is used to write local data to a SQL server via ODBC and then delete the local data.
Ideas, comments and help appreciated,


Public Sub readnwrite()
Dim dlyloop As Integer
Dim strSQL As String
Dim xfertime
Dim proctime
Dim PauseTime, Start, Finish, TotalTime

PauseTime = 4 ' Set duration.

On Error GoTo whoops
DoCmd.SetWarnings False
proctime = DateAdd("n", 1, Now)

Do While True
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
If proctime < Now Then
proctime = DateAdd("n", 1, Now) ' add a minute for the next slot
xfertime = DateAdd("n", -2, Now) ' - 1 minute use whole minute

Forms!Form1!fxfertime = xfertime
strSQL = "INSERT INTO dbo_dbdata " _
& "( dbdate, dblocation, dbstat, dbleveln )" _
& " SELECT dbdata.dbdate, dbdata.dblocation, " _
& " dbdata.dbstat, dbdata.dbleveln " _
& "FROM dbdata " _
& "WHERE (((dbdata.dbdate)< #" & xfertime & "#));"
DoCmd.RunSQL strSQL ' append dbdata record to dbo_dbdata

Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop

strSQL = "DELETE dbdata.dbdate, dbdata.*" _
& "FROM dbdata " _
& "WHERE (((dbdata.dbdate)<#" & xfertime & "#));"
DoCmd.RunSQL strSQL ' delete dbdata record
' error catching
'
'
'
'

End If ' full minute
If Forms!Form1!lblStat = "Stopped" Then
DoCmd.SetWarnings False
Exit Do
End If
Loop


Exit Sub

whoops:
MsgBox Err.Description
Resume Next

End Sub

 

[ddodd]

Any ideas how i can test the success or failure of the ODBC call so i can put in retry logic?

 

[mdlueck]

Any ideas how i can test the success or failure of the ODBC call so i can put in retry logic?

I coded my Access 2007 apps using ADODB objects. For them, if an object runs into some sort of an error, the object instance is useless for any further transactions.

I moved to creating instances each time a method runs, and cleaning them up at the end of the method... outside of any error handling so that guaranteed THEY GET CLEANED UP. That completely cleared up nonsense with objects becoming unusable in the event an object encountered any type of error.

 

[ddodd]

thanks, i'm looking at ADODB now