Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > access > answers

Set ODBC timeout value

  #1  
Old June 17th, 2009, 04:17 AM
Newbie
  
Join Date: Jun 2009
Posts: 2
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
  1. Private Sub Command104_Click()
  2. On Error GoTo Err_Command104_Click
  3.  
  4.     Dim stDocNameInfo02 As String
  5.     Dim stDocNameInfo03 As String
  6.     Dim stDocNameInfo10 As String
  7.     Dim stDocNameInfo11 As String
  8.  
  9.     stDocNameInfo02 = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _
  10.     "SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _
  11.     "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 " & _
  12.     "WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)=2)) "
  13.     DoCmd.SetWarnings False
  14.     DoCmd.RunSQL stDocNameInfo02
  15.  
  16.     stDocNameInfo03 = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _
  17.     "SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _
  18.     "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 " & _
  19.     "WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)=3)) "
  20.     DoCmd.SetWarnings False
  21.     DoCmd.RunSQL stDocNameInfo03
  22.  
  23.     stDocNameInfo10 = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _
  24.     "SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _
  25.     "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 " & _
  26.     "WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)=10)) "
  27.     DoCmd.SetWarnings False
  28.     DoCmd.RunSQL stDocNameInfo10
  29.  
  30.     stDocNameInfo11 = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _
  31.     "SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _
  32.     "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 " & _
  33.     "WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)=11)) "
  34.     DoCmd.SetWarnings False
  35.     DoCmd.RunSQL stDocNameInfo11
  36.  
  37.  
  38. Exit_Command104_Click:
  39.     Exit Sub
  40.  
  41. Err_Command104_Click:
  42.     MsgBox Err.Description
  43.     Resume Exit_Command104_Click
  44.  
  45. End Sub
Last edited by NeoPa; June 17th, 2009 at 01:59 PM. Reason: Please use the [CODE] tags provided.
  #2  
Old June 17th, 2009, 01:31 PM
Lives Here
  
Join Date: Jul 2008
Location: Maryland
Posts: 1,121
Provided Answers: 18

re: Set ODBC timeout value

First of all:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. strSQL = "INSERT INTO WWW_STATUS_ACCOUNTS ( SUFFIX, ACCOUNT_NUM, CURR_STATUS ) " & _
  3. "SELECT TOP " & Me!Recs & " AVS_FI.SUFFIX, AVS_ACCOUNT.ACCOUNT_NUM, AVS_ACCOUNT.CURR_STATUS " & _
  4. "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 " & _
  5. "WHERE (((AVS_FI.SUFFIX)= '" & Me!SUFFIX & "') AND ((AVS_ACCOUNT.CURR_STATUS)="
  6. DoCmd.SetWarnings False
  7. DoCmd.RunSQL strSQL & "2))"
  8. DoCmd.RunSQL strSQL & "3))"
  9. DoCmd.RunSQL strSQL & "10))"
  10. DoCmd.RunSQL strSQL & "11))"
  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?
  #3  
Old June 17th, 2009, 02:10 PM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,450
Provided Answers: 56

re: Set ODBC timeout value

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").
  #4  
Old June 17th, 2009, 02:53 PM
mshmyob's Avatar
Expert
  
Join Date: Jan 2008
Location: witness protection
Posts: 610
Provided Answers: 2

re: Set ODBC timeout value

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,
  #5  
Old June 17th, 2009, 07:13 PM
Newbie
  
Join Date: Jun 2009
Posts: 2

re: Set ODBC timeout value

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
  #6  
Old June 18th, 2009, 01:51 AM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,450
Provided Answers: 56

re: Set ODBC timeout value

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.
  #7  
Old June 18th, 2009, 02:28 AM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,450
Provided Answers: 56

re: Set ODBC timeout value

I just read one of Fish's links and now I can change my routines by setting :
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.QueryTimeout=120
  #8  
Old June 18th, 2009, 02:54 AM
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,168
Provided Answers: 19

re: Set ODBC timeout value

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
  1. Dim strSQL As String
  2. Dim wsCurrent As DAO.Workspace
  3. Dim dbCurrent As DAO.Database
  4. Dim qdfTemp As DAO.QueryDef
  5.  
  6. strSQL = "<Your SQL Statement here>"
  7.  
  8. Set wsCurrent = DBEngine.Workspaces(0)
  9. Set dbCurrent = wsCurrent.Databases(0)
  10.  
  11. 'Create the Temporary QueryDef Object
  12. Set qdfTemp = dbCurrent.CreateQueryDef("", strSQL)
  13.  
  14. 'Tell Jet to wait as long as the Query takes
  15. qdfTemp.ODBCTimeout = 0
  16.  
  17. 'Encapsulate within a Transaction
  18. wsCurrent.BeginTrans
  19.   qdfTemp.Execute
  20. wsCurrent.CommitTrans
  21.  
  22. 'Don't forget to make allowances for a possible Rollback
  #9  
Old June 18th, 2009, 12:46 PM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,450
Provided Answers: 56

re: Set ODBC timeout value

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.
  #10  
Old June 18th, 2009, 06:03 PM
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,168
Provided Answers: 19

re: Set ODBC timeout value

Quote:
Originally Posted by NeoPa View Post
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?
  #11  
Old June 19th, 2009, 11:14 AM
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,450
Provided Answers: 56

re: Set ODBC timeout value

Quote:
Originally Posted by ADezii View Post
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.
Reply

« previous question | next question »

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query timeout problems Edward answers 1 November 13th, 2005 07:44 AM
Query timeout problems Edward answers 1 July 23rd, 2005 08:54 AM
Problem with automatic session timeout Jack answers 7 July 22nd, 2005 02:05 AM
$_SERVER returns empty value tornado answers 3 July 17th, 2005 03:27 AM

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 225,662 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved