Set ODBC timeout value

Steve Eslinger

I have a query that I built using VB. It takes parameters that I pass in from a form and inserts records into a table. The problem I have is some of the values I enter cause the query to timeout. I need to know how to set the ODBC timeout value so my query will run. My query text is listed below

Expand|Select|Wrap|Line Numbers

 Private Sub Command104_Click()

On Error GoTo Err_Command104_Click
 
    Dim stDocNameInfo02 As String

    Dim stDocNameInfo03 As String

    Dim stDocNameInfo10 As String

    Dim stDocNameInfo11 As String
 
    stDocNameInfo02 = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _

    "SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _

    "FROM (AVS_ACCOUNT INNER JOIN AVS_BANK_GROUP ON AVS_ACCOUNT.BANK_GROUP_ID = AVS_BANK_GROUP.ID) INNER JOIN AVS_FI ON AVS_BANK_GROUP.FI_ID = AVS_FI.ID " & _

    "WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)=2)) "

    DoCmd.SetWarnings False

    DoCmd.RunSQL stDocNameInfo02
 
    stDocNameInfo03 = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _

    "SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _

    "FROM (AVS_ACCOUNT INNER JOIN AVS_BANK_GROUP ON AVS_ACCOUNT.BANK_GROUP_ID = AVS_BANK_GROUP.ID) INNER JOIN AVS_FI ON AVS_BANK_GROUP.FI_ID = AVS_FI.ID " & _

    "WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)=3)) "

    DoCmd.SetWarnings False

    DoCmd.RunSQL stDocNameInfo03
 
    stDocNameInfo10 = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _

    "SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _

    "FROM (AVS_ACCOUNT INNER JOIN AVS_BANK_GROUP ON AVS_ACCOUNT.BANK_GROUP_ID = AVS_BANK_GROUP.ID) INNER JOIN AVS_FI ON AVS_BANK_GROUP.FI_ID = AVS_FI.ID " & _

    "WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)=10)) "

    DoCmd.SetWarnings False

    DoCmd.RunSQL stDocNameInfo10
 
    stDocNameInfo11 = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _

    "SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _

    "FROM (AVS_ACCOUNT INNER JOIN AVS_BANK_GROUP ON AVS_ACCOUNT.BANK_GROUP_ID = AVS_BANK_GROUP.ID) INNER JOIN AVS_FI ON AVS_BANK_GROUP.FI_ID = AVS_FI.ID " & _

    "WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)=11)) "

    DoCmd.SetWarnings False

    DoCmd.RunSQL stDocNameInfo11
 
Exit_Command104_Click:

    Exit Sub
 
Err_Command104_Click:

    MsgBox Err.Description

    Resume Exit_Command104_Click
 
End Sub

ChipR

First of all:

Expand|Select|Wrap|Line Numbers

 Dim strSQL as String

strSQL = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _

"SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _

"FROM (AVS_ACCOUNT INNER JOIN AVS_BANK_GROUP ON AVS_ACCOUNT.BANK_GROUP_ID = AVS_BANK_GROUP.ID) INNER JOIN AVS_FI ON AVS_BANK_GROUP.FI_ID = AVS_FI.ID " & _

"WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)="

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL & "2))"

DoCmd.RunSQL strSQL & "3))"

DoCmd.RunSQL strSQL & "10))"

DoCmd.RunSQL strSQL & "11))"

DoCmd.SetWarnings True

Now, a couple questions.
Where do you set up this connection?
How do you know that it's timing out?
How sure are you that changing the timeout value will fix it?

NeoPa

Good question!

You've guessed it. That means I've never found a solution to it :(

I found the option to control usage of transactions, but no timeout setting.

There is another way to execute action queries (not simple SQL I'm afraid) but this has no reference to timeouts either. It is calling QueryDef.Execute, or even CurrentDb.Execute("QueryName").

mshmyob

Read this article by MS that explains how to speed up connection using ODBC. It specifically talks about Access and MS SQL server but the idea works for all databases.

Pay particular attention on how to trace ODBC traffic by changing the Registry. This will give you an idea of what is going on.

http://msdn.microsoft.com/en-us/library/bb188204.aspx

For setting ODBC timeouts see the following

http://support.microsoft.com/kb/153756

Also see if Begin Tran and Commit Tran helps.

cheers,

Steve Eslinger

thanks for all the replies....

ChipR. Thx for the modification to the SQL. That will save me a few lines of un-necessary code. Regarding your questions, The connection is made to the database when the form is opened. I know the query is timing out because I copied one of the SQL statements to a standard access query (minus all the VB settings) and the query timed out. The default ODBC timeout for the first time I ran the query was 60 seconds. I changed the ODBC timeout for the query to be 300 seconds and the query completed successfully. I figure I could create individual insert queries, change the ODBC setting to 300 for each and call them one by one but I was hoping there was a way to handle it in VB.

Mshmyob. I will begin to read the articles you provided. I am sure I will find my solution there

Thanks Again everyone for your help

NeoPa

You can also change the default to 300 from 60 in the Options of Access.

I often do the same as you've done but I think I may change my defaults too. Unfortunately this effects only those PCs you change it for.

NeoPa

I just read one of Fish's links and now I can change my routines by setting :

Expand|Select|Wrap|Line Numbers

CurrentDb.QueryTimeout=120

ADezii

Here is some handy Base Code that will allow you to use native Jet to execute a Query by creating a Temporary QueryDef Object. There is no TimeOut consideration since Jet wait wait as long as it takes for the Query to Execute. The entire process will also be encapsulated within a Transaction.

Expand|Select|Wrap|Line Numbers

 
Dim strSQL As String

Dim wsCurrent As DAO.Workspace

Dim dbCurrent As DAO.Database

Dim qdfTemp As DAO.QueryDef
 
strSQL = "<Your SQL Statement here>"
 
Set wsCurrent = DBEngine.Workspaces(0)

Set dbCurrent = wsCurrent.Databases(0)
 
'Create the Temporary QueryDef Object

Set qdfTemp = dbCurrent.CreateQueryDef("", strSQL)
 
'Tell Jet to wait as long as the Query takes

qdfTemp.ODBCTimeout = 0
 
'Encapsulate within a Transaction

wsCurrent.BeginTrans

  qdfTemp.Execute

wsCurrent.CommitTrans
 
'Don't forget to make allowances for a possible Rollback

NeoPa

Creating temp queries can be very useful and powerful, but remember it puts the database into an edit state which locks it from any type of editing from any other session. This includes any other user creating temp queries of course. Fine for stand-alone usage, but can cause problems where multiple users access the database concurrently.

ADezii

Quote:

Originally Posted by NeoPa

Creating temp queries can be very useful and powerful, but remember it puts the database into an edit state which locks it from any type of editing from any other session. This includes any other user creating temp queries of course. Fine for stand-alone usage, but can cause problems where multiple users access the database concurrently.

Quote:

Creating temp queries can be very useful and powerful, but remember it puts the database into an edit state which locks it from any type of editing from any other session.

I would think that this would be a Hugh Plus given the context, No?

NeoPa

Quote:

Originally Posted by ADezii

I would think that this would be a Hugh Plus given the context, No?

Maybe I'm missing something, but I can't see anything that would make this a good thing. It's perfectly possible that I am of course. As a general rule though, this is not good. You appreciate that databases can also be run multiple times on the same PC? This would also cause yourself problems in such a scenario. Coincidentally enough I have such a scenario and actually fell over just this very problem yesterday after my earlier post. No kidding.

Set ODBC timeout value

re: Set ODBC timeout value

re: Set ODBC timeout value

re: Set ODBC timeout value

re: Set ODBC timeout value

re: Set ODBC timeout value

re: Set ODBC timeout value

re: Set ODBC timeout value

re: Set ODBC timeout value

re: Set ODBC timeout value

re: Set ODBC timeout value

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/other

/bytes/about