Querydef Timeout

[alex]

Querydef Timeout

Hello,

Using Acesss ’03…

I use the following code to create a querydef. It works fine, but
notice the ODBC timeout…when it expires, the query just ends without
causing an error; i.e., I’d like to trap an error so that my users
know if the query timed out or if no records actually exist. Right
now, they cannot determine.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb 'open pointer to current database

On Error Resume Next
dbs.QueryDefs.Delete ("MyQuery")
Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)
qdf.ODBCTimeout = Me.comTimeOut 'combo box that holds 1-5 min (in sec)
intervals

qdf.Execute 'run query
Debug.Print qdf.RecordsAffected & " records added"

qdf.Close 'clean up
Set qdf = Nothing 'clean up
Set dbs = Nothing

Thanks for your help
alex

 

[Dirk Goldgar]

Alex -

You have the statement "On Error Resume Next" near the top of your code, so
the error will not automatically be shown to the user. You can either:

(a) restore normal error-handling or default error-handling:

On Error Resume Next
dbs.QueryDefs.Delete ("MyQuery")
On Error GoTo 0 ' default error-handling
'** or else use your own error-handler:
' On Error GoTo YourErrorHandler
Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)
qdf.ODBCTimeout = Me.comTimeOut
qdf.Execute 'run query
' ... and so on


*** or ***

(b) Use inline error-checking to see if an error occurred, and display a
message:

On Error Resume Next
dbs.QueryDefs.Delete ("MyQuery")

Err.Clear
Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)
qdf.ODBCTimeout = Me.comTimeOut
If Err.Number <> 0 Then
MsgBox "Unable to create query; is SQL correct?"
Else
qdf.Execute 'run query

If Err.Number <> 0 Then
MsgBox _
"An error occurred running the query. The message was:" & _
Err.Number & ": " & Err.Description, _
vbExclamation, _
"Error Running Query"
Else
Debug.Print qdf.RecordsAffected & " records added"
End If

qdf.Close 'clean up
End If

Set qdf = Nothing 'clean up
Set dbs = Nothing


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Querydef Timeout

Hello,

Using Acesss ’03…

I use the following code to create a querydef. It works fine, but
notice the ODBC timeout…when it expires, the query just ends without
causing an error; i.e., I’d like to trap an error so that my users
know if the query timed out or if no records actually exist. Right
now, they cannot determine.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb 'open pointer to current database

On Error Resume Next
dbs.QueryDefs.Delete ("MyQuery")
Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)
qdf.ODBCTimeout = Me.comTimeOut 'combo box that holds 1-5 min (in sec)
intervals

qdf.Execute 'run query
Debug.Print qdf.RecordsAffected & " records added"

qdf.Close 'clean up
Set qdf = Nothing 'clean up
Set dbs = Nothing

Thanks for your help
alex

 

[alex]

Alex -

You have the statement "On Error Resume Next" near the top of your code, so
the error will not automatically be shown to the user.  You can either:

(a) restore normal error-handling or default error-handling:

    On Error Resume Next
    dbs.QueryDefs.Delete ("MyQuery")
    On Error GoTo 0  ' default error-handling
    '** or else use your own error-handler:
    ' On Error GoTo YourErrorHandler
    Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)
    qdf.ODBCTimeout = Me.comTimeOut
    qdf.Execute 'run query
    ' ... and so on

*** or ***

(b) Use inline error-checking to see if an error occurred, and display a
message:

    On Error Resume Next
    dbs.QueryDefs.Delete ("MyQuery")

    Err.Clear
    Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)
    qdf.ODBCTimeout = Me.comTimeOut
    If Err.Number <> 0 Then
        MsgBox "Unable to create query;  is SQL correct?"
    Else
        qdf.Execute 'run query

        If Err.Number <> 0 Then
            MsgBox _
                "An error occurred running the query. Themessage was:" & _
                    Err.Number & ": " & Err.Description, _
                vbExclamation, _
                "Error Running Query"
        Else
            Debug.Print qdf.RecordsAffected & " records added"
        End If

        qdf.Close 'clean up
    End If

    Set qdf = Nothing 'clean up
    Set dbs = Nothing

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)


Querydef Timeout

Hello,

Using Acesss ’03…

I use the following code to create a querydef.  It works fine, but
notice the ODBC timeout…when it expires, the query just ends without
causing an error; i.e., I’d like to trap an error so that my users
know if the query timed out or if no records actually exist.  Right
now, they cannot determine.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb 'open pointer to current database

On Error Resume Next
dbs.QueryDefs.Delete ("MyQuery")
Set qdf = dbs.CreateQueryDef("MyQuery", strSQL)
qdf.ODBCTimeout = Me.comTimeOut 'combo box that holds 1-5 min (in sec)
intervals

qdf.Execute 'run query
Debug.Print qdf.RecordsAffected & " records added"

qdf.Close 'clean up
Set qdf = Nothing 'clean up
Set dbs = Nothing

Thanks for your help
alex

Dirk,
I can't belive I missed that (...yes I can!).

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?

Thanks,
alex

 

[Dirk Goldgar]

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.

 

[alex]

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

 

[Dirk Goldgar]

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.

 

[alex]

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.

(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

 

[alex]

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

 

[Dirk Goldgar]

[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

 

[alex]

news:[email protected]...>[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