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
10 21028
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 32,554
Expert Mod 16PB
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").
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,
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 32,554
Expert Mod 16PB
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 32,554
Expert Mod 16PB
I just read one of Fish's links and now I can change my routines by setting : - CurrentDb.QueryTimeout=120
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 32,554
Expert Mod 16PB
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.
@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 32,554
Expert Mod 16PB @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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Robert Oschler |
last post by:
I want to change the time it takes the browser to timeout before producing a
404 error on one of my web pages. I want to do it before I set the SRC
attribute of an IFRAME on the page, and change...
|
by: Mike Knight |
last post by:
I have the following code in an Excel 2003 module that creates a query
in MS Access 2003. The created query can then be opened from Access.
In Access, right-mouse clicking in design mode, in...
|
by: A.M-SG |
last post by:
Hi,
How can I increase the HTTP Timeout value at the client side?
Thank you,
Alan
|
by: George |
last post by:
Access 2002
SQL Server 2000
ODBC Linked Tables
I have a user who gets an ODBC timeout message when trying to save a new
record in a form. I looked at the locks on the server and that user was...
|
by: Jamie Schatte |
last post by:
Hi, I have an ASP.NET 2.0 website that uses SQL Server session state. All
works well with session state, except it seems like no matter what value I
use for the timeout in web.config, the session...
|
by: Jarf |
last post by:
I have and ASP.Net 2.0 application I've set the session state to use
SQL Server and I modified the Timeout setting to be 60 minutes.
However, my session is still expiring in 20 minutes. Looking in...
|
by: olle |
last post by:
Hi.
I have the problem that some records in a ms sqlserver table is unable
to update from Access.
I get the error message odbc-time out error in linked table......
I tried to copy this table...
|
by: =?Utf-8?B?ZG91Zw==?= |
last post by:
I support several intranet sites one of which had NO STATE. Even though
content has ASP extensions, it was all rendered HTML. Site had NO session or
application variables being managed. Site had...
|
by: teddysnips |
last post by:
One of our clients has reported a problem. Everything was working
fine on Monday, but since Tuesday all is going wrong. The sysadmin
assures me that there have been no changes to the network, or...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
| |