By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,218 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

Set ODBC timeout value

P: 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
Jun 17 '09 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 1,287
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?
Jun 17 '09 #2

NeoPa
Expert Mod 15k+
P: 31,709
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").
Jun 17 '09 #3

mshmyob
Expert 100+
P: 904
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,
Jun 17 '09 #4

P: 2
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
Jun 17 '09 #5

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 18 '09 #6

NeoPa
Expert Mod 15k+
P: 31,709
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
Jun 18 '09 #7

ADezii
Expert 5K+
P: 8,679
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
Jun 18 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 18 '09 #9

ADezii
Expert 5K+
P: 8,679
@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?
Jun 18 '09 #10

NeoPa
Expert Mod 15k+
P: 31,709
@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.
Jun 19 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.