Querydef Timeout
in [General]
|
Prev: mada faka
Next: Data Compression
From: Dirk Goldgar on 16 Feb 2010 12:50 "alex" <sql_aid(a)yahoo.com> wrote in message news:cfde9c57-c2a9-4dd2-809a-0b0f6509c0b3(a)l26g2000yqd.googlegroups.com... > I did some looking around, both online and in Access help... I'm not sure > you can trap the specific ODBC timeout error. > > When I run your code, I get the following: > 0 [Microsoft][ODBC SQL Server Driver]Timeout expired > 3146 ODBC--call failed. > > It looks like the err.number is 0. That seems wrong to me. Are you sure you didn't print Err.Number, rather than objError.Number? I could be mistaken, because I haven't done this for several years. Please post your code. > Taken from the help file (DAO error object): > Enumerating the specific errors in the Errors collection enables your > error-handling routines to more precisely determine the cause and origin > of an error, and take appropriate steps to recover. (makes it sound like > you can, however) > > It looks like the 0 is the lowest level error, followed by 3146... I believe there ought to be an actual non-zero error number for that lowest-level error. > How can you trap the lowest level error when the error handler returns the > higher (3146) level err.number? Your error-handler will trap the highest-level error, 3146. In your error-handler you will loop through the DBEngine.Errors collection to find the lowest-level error number, and take action based on that. Actually, when I have had to deal with ODBC errors, it has just been for purposes of logging and displaying an error message, so I have just concatenated all the errors into a list that gets printed and displayed. If the lowest-level error really is coming through as zero, try refreshing the DBEngine.Errors collection: DBEngine.Errors.Refresh .... before looping through. See if that makes any difference. I really think you should not be getting error number 0 there. But if I'm wrong, and that's what you really do get, you'll have to look at the error's ..Description rather than the .Number. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: alex on 16 Feb 2010 12:54 On Feb 16, 12:25 pm, alex <sql_...(a)yahoo.com> wrote: > On Feb 16, 11:16 am, "Dirk Goldgar" > > > > > > <d...(a)NOdataSPAMgnostics.com.invalid> wrote: > > "alex" <sql_...(a)yahoo.com> wrote in message > > >news:b788f485-2daa-4590-87f0-7b0a508f73a9(a)d2g2000yqa.googlegroups.com... > > > > I'm now getting the error (3146 ODBC--call failed) which I've trapped and > > > assigned to a msgbox. I'm assuming that error is my query timing out; > > > although it's not specific? > > > You can examine the contents of the DBEngine.Errors collection to see what > > specific error(s) are returned. For example: > > > Dim objError As Error > > > For Each objError In DBEngine.Errors > > Debug.Print objError.Number, objError.Description > > Next objError > > > In the event of an ODBC error, you will probably find multiple errors in the > > Errors collection: one 3146 (call failed), and at least one that is more > > specific (e.g, "query timed out"). See the online help about the Errors > > collection and Error object. > > > -- > > Dirk Goldgar, MS Access MVP > > Access tips:www.datagnostics.com/tips.html > > > (please reply to the newsgroup) > > Dirk, > I did some looking around, both online and in Access help... > I'm not sure you can trap the specific ODBC timeout error. > > When I run your code, I get the following: > 0 [Microsoft][ODBC SQL Server Driver]Timeout expired > 3146 ODBC--call failed. > > It looks like the err.number is 0. > > Taken from the help file (DAO error object): > Enumerating the specific errors in the Errors collection enables your > error-handling routines to more precisely determine the cause and > origin of an error, and take appropriate steps to recover. (makes it > sound like you can, however) > > It looks like the 0 is the lowest level error, followed by 3146... > > How can you trap the lowest level error when the error handler returns > the higher (3146) level err.number? > alex- Hide quoted text - > > - Show quoted text - This is what I came up with: Dim errLoop As Error For Each errLoop In Errors With errLoop If .Number = 0 Then 'ODBC TimeOut Expired MsgBox "Your query has timed out!" Exit Sub Else MsgBox "Problem encountered with Query!" Exit Sub End If End With Next It works, but I'm confused about the error number; is it really 0. I guess I need to search for a list or throw some different errors to test... alex
From: alex on 16 Feb 2010 13:04 On Feb 16, 12:54 pm, alex <sql_...(a)yahoo.com> wrote: > On Feb 16, 12:25 pm, alex <sql_...(a)yahoo.com> wrote: > > > > > > > On Feb 16, 11:16 am, "Dirk Goldgar" > > > <d...(a)NOdataSPAMgnostics.com.invalid> wrote: > > > "alex" <sql_...(a)yahoo.com> wrote in message > > > >news:b788f485-2daa-4590-87f0-7b0a508f73a9(a)d2g2000yqa.googlegroups.com.... > > > > > I'm now getting the error (3146 ODBC--call failed) which I've trapped and > > > > assigned to a msgbox. I'm assuming that error is my query timing out; > > > > although it's not specific? > > > > You can examine the contents of the DBEngine.Errors collection to see what > > > specific error(s) are returned. For example: > > > > Dim objError As Error > > > > For Each objError In DBEngine.Errors > > > Debug.Print objError.Number, objError.Description > > > Next objError > > > > In the event of an ODBC error, you will probably find multiple errors in the > > > Errors collection: one 3146 (call failed), and at least one that is more > > > specific (e.g, "query timed out"). See the online help about the Errors > > > collection and Error object. > > > > -- > > > Dirk Goldgar, MS Access MVP > > > Access tips:www.datagnostics.com/tips.html > > > > (please reply to the newsgroup) > > > Dirk, > > I did some looking around, both online and in Access help... > > I'm not sure you can trap the specific ODBC timeout error. > > > When I run your code, I get the following: > > 0 [Microsoft][ODBC SQL Server Driver]Timeout expired > > 3146 ODBC--call failed. > > > It looks like the err.number is 0. > > > Taken from the help file (DAO error object): > > Enumerating the specific errors in the Errors collection enables your > > error-handling routines to more precisely determine the cause and > > origin of an error, and take appropriate steps to recover. (makes it > > sound like you can, however) > > > It looks like the 0 is the lowest level error, followed by 3146... > > > How can you trap the lowest level error when the error handler returns > > the higher (3146) level err.number? > > alex- Hide quoted text - > > > - Show quoted text - > > This is what I came up with: > Dim errLoop As Error > For Each errLoop In Errors > With errLoop > If .Number = 0 Then 'ODBC TimeOut Expired > MsgBox "Your query has timed out!" > Exit Sub > Else > MsgBox "Problem encountered with Query!" > Exit Sub > End If > End With > Next > > It works, but I'm confused about the error number; is it really 0. I > guess I need to search for a list or throw some different errors to > test... > alex- Hide quoted text - > > - Show quoted text - Dirk, I posted my last message before seeing yours... I'll look at what you said and see what I can come up with! Thanks, alex
From: Dirk Goldgar on 16 Feb 2010 13:18 "alex" <sql_aid(a)yahoo.com> wrote in message news:272c0ea9-4482-4492-a99e-4a9ead3fc42a(a)f8g2000yqn.googlegroups.com... > [Quoting] This is what I came up with: Dim errLoop As Error For Each errLoop In Errors With errLoop If .Number = 0 Then 'ODBC TimeOut Expired MsgBox "Your query has timed out!" Exit Sub Else MsgBox "Problem encountered with Query!" Exit Sub End If End With Next < [End Quoting] Just to be sure you're accessing the correct collection, make that: For Each errLoop In DBEngine.Errors -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: alex on 16 Feb 2010 13:39
On Feb 16, 1:18 pm, "Dirk Goldgar" <d...(a)NOdataSPAMgnostics.com.invalid> wrote: > "alex" <sql_...(a)yahoo.com> wrote in message > > news:272c0ea9-4482-4492-a99e-4a9ead3fc42a(a)f8g2000yqn.googlegroups.com...> [Quoting] > > This is what I came up with: > Dim errLoop As Error > For Each errLoop In Errors > With errLoop > If .Number = 0 Then 'ODBC TimeOut Expired > MsgBox "Your query has timed out!" > Exit Sub > Else > MsgBox "Problem encountered with Query!" > Exit Sub > End If > End With > Next > < [End Quoting] > > Just to be sure you're accessing the correct collection, make that: > > For Each errLoop In DBEngine.Errors > > -- > Dirk Goldgar, MS Access MVP > Access tips:www.datagnostics.com/tips.html > > (please reply to the newsgroup) Dirk, I really appreciate the help... This is what I came up with (still getting 0 for an error number): ErrorHandler: DBEngine.Errors.Refresh Dim errLoop As Error For Each errLoop In DBEngine.Errors 'make sure collection is correct With errLoop If .Description = "[Microsoft][ODBC SQL Server Driver]Timeout expired" Then 'ODBC TimeOut Expired MsgBox "Your query has timed out!" & vbNewLine & _ "Extend the TimeOut or change your query." Exit Sub Else MsgBox "Problem encountered with Query!" Exit Sub End If End With Next You're right about the different error levels. This type of error actually throws two errors with the lower error (the one above) hitting first. I would guess you'd need to loop in reverse (if possible) to first trap the 3146 error and then the Timeout expired error. I think the code above will work (seems to) let me know if you think I'll run into problems. Thanks again, alex |