V
vcsphx
All, I have a Access 03 db that pulls data from a SQL server. The code works
just fine but sometimes when the code executes the query that connects to the
server, I guess when the server gets busy, it gives me a "ODBC call failed :
Error # 3146" error. For the ODBC Timout property in the query, I have set it
as "0" but still it's erring out at times. So I built an error handling that
looks something like this:
"...
on error goto ErrorHandler
dim rs as dao.recordset
dim rs1 as dao.recordset
set rs1=db.openrecordset("tblmain",dbopenrecordset)
do while rs1.eof=false
j=0
REDO_ONCE_ON_ERROR:
set rs=db.openrecordset("select * from sql_query_name where
c1='" & rs1!main & ''",dbopendynaset)
do while rs.eof=false
....
loop
NextEventCode:
rs1.movenext
loop
ErrorHandler:
'For a given parameter, the first time this error pops up, then wait for 2
minutes and re-try. If it errs out again, then skip this parameter and goto
the next record in the tblmain.
j = j + 1
If j = 1 and err.number =3146 Then
twait = Time
twait = DateAdd("s", 120, twait)
Do Until tnow >= twait
tnow = Time
Loop
GoTo REDO_ONCE_ON_ERROR
Elseif j>1 and err.number=3146
GoTo NextEventCode
End If
The issue I have here is that the first time I get this error, the ON ERROR
statement works fine. But if I get the same time out error again, then for
some reason the ON ERROR statement gets disabled and the system just breaks.
It spits out the error and I have to go and manually run it again. Do you
guys know why the ERROR HANDLING logic is not working for the first time but
not after that? Thank you very much for your help!
just fine but sometimes when the code executes the query that connects to the
server, I guess when the server gets busy, it gives me a "ODBC call failed :
Error # 3146" error. For the ODBC Timout property in the query, I have set it
as "0" but still it's erring out at times. So I built an error handling that
looks something like this:
"...
on error goto ErrorHandler
dim rs as dao.recordset
dim rs1 as dao.recordset
set rs1=db.openrecordset("tblmain",dbopenrecordset)
do while rs1.eof=false
j=0
REDO_ONCE_ON_ERROR:
set rs=db.openrecordset("select * from sql_query_name where
c1='" & rs1!main & ''",dbopendynaset)
do while rs.eof=false
....
loop
NextEventCode:
rs1.movenext
loop
ErrorHandler:
'For a given parameter, the first time this error pops up, then wait for 2
minutes and re-try. If it errs out again, then skip this parameter and goto
the next record in the tblmain.
j = j + 1
If j = 1 and err.number =3146 Then
twait = Time
twait = DateAdd("s", 120, twait)
Do Until tnow >= twait
tnow = Time
Loop
GoTo REDO_ONCE_ON_ERROR
Elseif j>1 and err.number=3146
GoTo NextEventCode
End If
The issue I have here is that the first time I get this error, the ON ERROR
statement works fine. But if I get the same time out error again, then for
some reason the ON ERROR statement gets disabled and the system just breaks.
It spits out the error and I have to go and manually run it again. Do you
guys know why the ERROR HANDLING logic is not working for the first time but
not after that? Thank you very much for your help!