Need Assistance Setting ODBC Timeout in VB

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)
LVL 1
CoghanAsked:
Who is Participating?
 
jadedataConnect With a Mentor MS Access Systems CreatorCommented:
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
 
jadedataMS Access Systems CreatorCommented:
Have you considered running this query as a Pass-Through so that the DB Server picks up the workload on this...???
0
 
CoghanAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
jadedataMS Access Systems CreatorCommented:
See help for more details but here's one example

  dbsCurrent.QueryTimeout = 30  'in seconds

see help for "ODBCTimeOut" and "QueryTimeout"
      
0
 
CoghanAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
I think ODBCTimeout is an application.property not a db.property.  Have you tried that?
0
 
CoghanAuthor Commented:
Application.ODBCTimeout doesn't compile.  Application.CurrentDb.QueryTimeout compiles but doesn't seem to do anything.  
0
 
jadedataMS Access Systems CreatorCommented:
Try: for the specific query

currentdb.querydefs("QueryName").odbctimeout = {whatever}
0
 
CoghanAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
You are not going to be able to change the ODBC timeout for a nonpersistant query.  VB will use the default timeout.
0
 
CoghanAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
Why didn't you just say you wanted to swap the sql string on a query with a long ODBCTimeout value?!!

:)

0
 
CoghanAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
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
 
CoghanAuthor Commented:
Okay that worked.  Thanks a million.  Now off to chop down the next briar bush...
0
 
jadedataMS Access Systems CreatorCommented:
Can I ask why this did not rate an "A"?
0
 
CoghanAuthor Commented:
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
 
jadedataMS Access Systems CreatorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.