[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Code to change ODBC timeout

Posted on 2014-07-16
7
Medium Priority
?
1,704 Views
Last Modified: 2014-07-17
I have a client with multiple applications with dozens and dozens of queries where I need to change the ODBC timeout. Rather than go into each query is there some code I can run to do this for me? It'd save me a ton of time.

Thanks Experts!
0
Comment
Question by:Eileen Murphy
  • 3
  • 2
  • 2
7 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40199959
You can do it with DBEngine:

Dim db As DAO.Database
Set db = DBEngine(0)(0)
db.QueryTimeout = 200 ' or whatever value works for you

Or you can set it in the Registry. See this Article: http://support.microsoft.com/kb/153756
0
 

Author Comment

by:Eileen Murphy
ID: 40199965
OK Cool. I found the registry suggestion and am doing that. Do you know if this then overrides the timeouts in each of the queries??
0
 
LVL 85
ID: 40200738
Yes, it works for all queries. It also is a persistent setting, meaning you only have to change it once, and it'll "stick"
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 58
ID: 40201778
I'd double check that.  I'm not sure that article is correct, but it is the engine key, so maybe it does.

 But I believe like all other settings there it will require a close and re-open of Access.   It's not something your going to be able to set on the fly.   Also the article says "If greater" and I'm wondering if your currently set to 60 (the default) and then set the registry to 0 (no timeout) if it's going to honor that or not.

 Personally, I'd set the set the timeout directly on the querydef object (which is what I do) so there's no question about what's being used.   Any setting there will over ride anything else.

Jim.
0
 
LVL 85
ID: 40201822
Sorry, should have been more clear:

If you set the Registry entry it would stick. You'd need to run the DBEngine code each time the app is started in order for that to work.
0
 

Author Comment

by:Eileen Murphy
ID: 40202075
So - in each front-end upon open I need to run the above code? I just want to make sure I understand.
0
 
LVL 58
ID: 40202232
Or the following as a one time thing:

  Dim db As DAO.Database
  Dim myQueryDef As DAO.QueryDef
  Dim intI As Integer

  Set db = DBEngine.Workspaces(0).Databases(0)

  For intI = 0 To db.QueryDefs.Count - 1
    Set myQueryDef = db.QueryDefs(intI)
    Debug.Print "> "; myQueryDef.Name
    myQueryDef.ODBCTimeout = 60
  Next intI

  This sets the queries individually.  Note however that it doesn't apply to SQL you execute in code.   Those reply on the DB and registry settings.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 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