Solved

handle ODBC timeout programmatically to continue execution

Posted on 2007-04-03
6
436 Views
Last Modified: 2009-07-29
I have a large program with queries to 80+ different SQL servers over the net.

My problem: how can I programmactically handle timeouts when they occur due to 1 or more of those servers being down; and thus not stop execution with an error box, but continue executing the remainder of the lines in my code; in effect "skipping" the one that timed out?

I simply have 80+ of these:
DoCmd.OpenQuery "1-POS"
DoCmd.OpenQuery "2-POS"
DoCmd.OpenQuery "3-POS"
etc.

0
Comment
Question by:ToddRod_Taylor
  • 2
  • 2
  • 2
6 Comments
 
LVL 2

Accepted Solution

by:
kathik earned 250 total points
ID: 18847180
If you really don't want to trap for errors, you can always use this statement before the first docmd line:

ON ERROR RESUME NEXT
0
 

Author Comment

by:ToddRod_Taylor
ID: 18847197
After a procedure with ON ERROR RESUME NEXT set finishes, does that go back to a default ?
0
 
LVL 2

Expert Comment

by:kathik
ID: 18847260
The ON ERROR RESUME NEXT will be local to that procedure.  So, if a calling procedure has different error handling, it will take over once this one is finished.  I think the default error handling in MS Access is just to break at the error.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Assisted Solution

by:glennwaldron
glennwaldron earned 250 total points
ID: 18848692
Wouldn't you be better off to set it up like

Sub Doqueries
On error goto error_trap

'list of queries
.
...

Exit_Here:
Exit Sub
Error_Trap:
'now you can handle the errors any way you want
Select Case Err.Number
  Case 1024 (or whatever the error you want to trap)
     do something, possibly write an error log to tell you what query failed
     resume next  'this takes you to the next query
   Case else
       do something
       goto Exit_Here

 
This allows you to keep track of all errors not just one specific error


Hope this helps


Glenn
0
 

Author Comment

by:ToddRod_Taylor
ID: 18860496
foolish me.....
the ON ERROR RESUME NEXT is perfect ; down-n-dirty

trapping for which one failed I liked as well.

I used the ON ERROR RESUME for a few days, then coded the trap to tell me which servers where down.  Both served my needs, thanks!
0
 
LVL 1

Expert Comment

by:glennwaldron
ID: 18868263
Thanls for the points and glad to be of help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now