Code to change ODBC timeout

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!
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eileen MurphyIndependent Application DeveloperAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Eileen MurphyIndependent Application DeveloperAuthor Commented:
So - in each front-end upon open I need to run the above code? I just want to make sure I understand.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.