Solved

Need Assistance Setting ODBC Timeout in VB

Posted on 2004-03-22
18
1,616 Views
Last Modified: 2012-05-04
I have this query which is referencing two tables with many millions of rows, so it takes some time.  Both tables are DB2 linked via ODBC from our mainframe.  I get a timeout message.  If it were a stored query, I know how to change the ODBC timeout from properties of the query.  

This is what I have (which doesn't work because it says operation not supported for this type of object, the SQL has already tested good):
CurrentDb.Connection.QueryTimeout = 600
Dim strSQL As String
strSQL = "SELECT NCBX_MC_ANSR_INQ.CALL_CLOS_DT, NCBX_MC_ANSR_ROUT.CALL_RUT_CD, " & _
         "NCBX_MC_ANSR_INQ.CALL_INQ_STAS_CD, NCBX_MC_ANSR_INQ.CNTL_INQ_NO, " & _
         "tblNBANSIRIDs.[Ansir ID], tblNBANSIRIDs.Name, " & _
         "NCBX_MC_ANSR_ROUT.CALL_RUT_IN_DT INTO tblNBAudits " & _
         "FROM (NCBX_MC_ANSR_INQ INNER JOIN NCBX_MC_ANSR_ROUT ON " & _
         "NCBX_MC_ANSR_INQ.CNTL_INQ_NO = NCBX_MC_ANSR_ROUT.CNTL_INQ_NO) INNER JOIN " & _
         "tblNBANSIRIDs ON NCBX_MC_ANSR_ROUT.EMP_PRCS_CD = tblNBANSIRIDs.[Ansir ID] " & _
         "WHERE (((NCBX_MC_ANSR_INQ.CALL_CLOS_DT)= #" & _
         Forms!frmDashBoard.RptStart & "#) AND " & _
         "((NCBX_MC_ANSR_ROUT.CALL_RUT_CD) In " & _
         "('BRA ','BCM ','BAP ','BOL ','BRF ','BWC ','BLC ','BLA ','BSA ','PEN ')) AND " & _
         "((NCBX_MC_ANSR_INQ.CALL_INQ_STAS_CD)='C') AND ((NCBX_MC_ANSR_ROUT.CALL_RUT_IN_DT) " & _
         "Between [pln_rec_dt] And [call_clos_dt]));"
DoCmd.RunSQL (strSQL)
0
Comment
Question by:Coghan
  • 10
  • 8
18 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 10650537
Have you considered running this query as a Pass-Through so that the DB Server picks up the workload on this...???
0
 
LVL 1

Author Comment

by:Coghan
ID: 10651002
I can not figure out the syntax to make this work.  I do know the connect string and all of that, but it seems to have trouble figuring out when I'm referring to a table that is in my database and when it is one that is on the mainframe database.  I fiddled with it for awhile, but it didn't get me anywhere.  What I really need right now is just a way to set my timeout in VB.  I  know it can be done as I found many references to it in other posts, but nothing that exactly fits my situation.  Either that, or I'm applying it incorrectly.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10651205
See help for more details but here's one example

  dbsCurrent.QueryTimeout = 30  'in seconds

see help for "ODBCTimeOut" and "QueryTimeout"
      
0
 
LVL 1

Author Comment

by:Coghan
ID: 10651298
I did try Currentdb.querytimeout.. no luck.  It doesn't give me an error, but it doesn't override the 60 second default either.  Currentdb.ODBCtimeout gave me a compile error.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10651536
I think ODBCTimeout is an application.property not a db.property.  Have you tried that?
0
 
LVL 1

Author Comment

by:Coghan
ID: 10651746
Application.ODBCTimeout doesn't compile.  Application.CurrentDb.QueryTimeout compiles but doesn't seem to do anything.  
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10651796
Try: for the specific query

currentdb.querydefs("QueryName").odbctimeout = {whatever}
0
 
LVL 1

Author Comment

by:Coghan
ID: 10651886
For this query, I'm using a string of SQL and running it with DoCmd.RunSQL.  So what would I use for my QueryName?  If I run this from a saved query, I don't need to do this because I can just set the time out in the properties.  

 
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10651942
You are not going to be able to change the ODBC timeout for a nonpersistant query.  VB will use the default timeout.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:Coghan
ID: 10652262
I am making progress.  Using the QueryDef idea, I got this to work once. (code below)  BUT, I need to re-create this query each time I run the code in order for this to work, because when running it through again, it errors because the query already exists.  How can I delete the query first before running the code?  And as I have never used querydef to create a query, please give me any pointers you can on how to improve the code. (For instance when I did Dim qd as querydef I got an error so skipped the Dim altogether, what should I have done?)

Dim strSQL As String
Set db = CurrentDb()
strSQL = "SELECT NCBX_MC_ANSR_INQ.CALL_CLOS_DT, NCBX_MC_ANSR_ROUT.CALL_RUT_CD, " & _
         "NCBX_MC_ANSR_INQ.CALL_INQ_STAS_CD, NCBX_MC_ANSR_INQ.CNTL_INQ_NO, " & _
         "tblNBANSIRIDs.[AnsirID], tblNBANSIRIDs.Name, " & _
         "NCBX_MC_ANSR_ROUT.CALL_RUT_IN_DT INTO tblNBAudits " & _
         "FROM (NCBX_MC_ANSR_INQ INNER JOIN NCBX_MC_ANSR_ROUT ON " & _
         "NCBX_MC_ANSR_INQ.CNTL_INQ_NO = NCBX_MC_ANSR_ROUT.CNTL_INQ_NO) INNER JOIN " & _
         "tblNBANSIRIDs ON NCBX_MC_ANSR_ROUT.EMP_PRCS_CD = tblNBANSIRIDs.[AnsirID] " & _
         "WHERE (((NCBX_MC_ANSR_INQ.CALL_CLOS_DT)= #" & _
         Forms!frmDashBoard.RptStart & "#) AND " & _
         "((NCBX_MC_ANSR_ROUT.CALL_RUT_CD) In " & _
         "('BRA ','BCM ','BAP ','BOL ','BRF ','BWC ','BLC ','BLA ','BSA ','PEN ')) AND " & _
         "((NCBX_MC_ANSR_INQ.CALL_INQ_STAS_CD)='C') AND ((NCBX_MC_ANSR_ROUT.CALL_RUT_IN_DT) " & _
         "Between [pln_rec_dt] And [call_clos_dt]));"
Set qd = db.CreateQuerydef("qryTestThis", strSQL)
CurrentDb.QueryDefs("qryTestThis").ODBCTimeout = 600
db.QueryDefs.Refresh
DoCmd.OpenQuery "qryTestThis"
Set qd = Nothing
0
 
LVL 32

Accepted Solution

by:
jadedata earned 250 total points
ID: 10652329
leave the persistant copy in place and change it's sql

currentdb.querydefs("qryTestThis").sql = strsql

This will allow you to set and forget the ODBCTimeOut on this query..
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10652333
Why didn't you just say you wanted to swap the sql string on a query with a long ODBCTimeout value?!!

:)

0
 
LVL 1

Author Comment

by:Coghan
ID: 10652388
Just as an added comment because I'm sure someone may have wondered... the reason that I can't do this in a regular stored query is because the user must be able to enter a date value in a form.  Because of the way the ODBC works between the mainframe and ACCESS, there is a big problem with dates.  It has to do with the timestamp tagging along.  If I make the input box a date format, then it gets date outside parameters error.  If I make it a text and do conversion on it, it simply can't see it as a date.  I even tried having the #'s entered in the box.  None of it works.  I did create a stored procedure that does a FormatDateTime on the input date and just set the query to look for the result of the proc.  However, as the database has grown, it has slowed to the point it is practically unusable for some queries unless you put the dates directly into the query.  This is just my way to 'feed' the #'s to the query.  I don't want to go off on a long sidebar about what else I have tried on that because it has been very thoroughly explored (unless you have some specific info).  I just wanted to let you know why I'm attempting to do what I'm doing.  
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10652446
I NEVER let users enter parameter directly to an Access query SPECIFICALLY for the reasons you just stated.  User data entry is notoriously unreliable for accuracy and must be run thru an "edit-mill" before committing to an sql string.  

I am behind you 150% on your method.

0
 
LVL 1

Author Comment

by:Coghan
ID: 10652910
Okay that worked.  Thanks a million.  Now off to chop down the next briar bush...
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10653116
Can I ask why this did not rate an "A"?
0
 
LVL 1

Author Comment

by:Coghan
ID: 10660189
No slight was intended at all.  I was very happy to get an answer.  Perhaps I need to review the rating tips.  I was going on a gut instinct, and I based my good rating on the fact that we looped around a bit at first to get to an answer and I supplied a good part of the meat of the answer myself.  However, it is not my intention to be stingy and I will be more generous in the future.  I certainly do not wish to appear ungrateful.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10661212
I was only questioning what your take was on the way the grading guidelines read.  Didn't want to imply anything else.  I will ask that of the occasional "new" questioner I meet.  Thanks for providing that.

I graduated highschool with a 1.3 grade average.  I failed Typing and Algebra.  Now I are a programmer.  A "B" sounds really good compared to that... right??
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now