Prev: mada faka
Next: Data Compression
From: alex on 16 Feb 2010 09:25
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
From: Dirk Goldgar on 16 Feb 2010 10:23
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)

"alex" <sql_aid(a)yahoo.com> wrote in message
news:cbe79cd2-b9d9-45e1-92af-7cad8b34c460(a)d27g2000yqn.googlegroups.com...
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



From: alex on 16 Feb 2010 10:42
On Feb 16, 10:23 am, "Dirk Goldgar"
<d...(a)NOdataSPAMgnostics.com.invalid> wrote:
> 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)
>
> "alex" <sql_...(a)yahoo.com> wrote in message
>
> news:cbe79cd2-b9d9-45e1-92af-7cad8b34c460(a)d27g2000yqn.googlegroups.com...
> 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
From: Dirk Goldgar on 16 Feb 2010 11:16
"alex" <sql_aid(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)

From: alex on 16 Feb 2010 12:25
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
 |  Next  |  Last
Pages: 1 2
Prev: mada faka
Next: Data Compression