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

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:

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 :

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.

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]

@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.

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

[NeoPa]

@ADezii
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.