Access 2013 ODBC timeout

I have an Access 2013 database with tables and views linked from SQLServer. I am transferring from the adp that I used in Office  2010. I keep running into the problem of reports, based on linked views, not running because an ODBC timeout. The timeout seems to be the default value of 60 seconds. I have tried using CurrentDB.Timeout = 180, but that does't seem to have any affect.
Can I change the timeout and, if so, how.
Thanks.
pjrv2Asked:
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If the datasource of the report is a query, change the ODBC Timeout property of the query.   Zero (0), means no timeout.

If not based on a query, let me know and we'll see what other options you have.

Jim.
0
pjrv2Author Commented:
The report is based on a a SQLServer view that is linked to the database.
0
Jeffrey CoachmanMIS LiasonCommented:
I have never had a problem with this...
Check the setting Jim mentioned, ...also review the other ODBC setting, to be sure.

Also try linking to the source SQL table (instead of a view) and write any "criteria" in an Access query (or exclude/add any fields) and use the Access query as the Reports Recordsource.

JeffCoachman
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pjrv2Author Commented:
Thanks for the thoughts.
It would seem that a solution is to create a query that selects all the data from the view and to set the ODBC timeout on that query.
Perhaps not the most elegant solution, but it appears to work.
0
Jeffrey CoachmanMIS LiasonCommented:
I tend to link only to the tables.
I was never quite sure of how a view would work under all circumstances.

Other Experts like Jim have more experience in these areas, so I tend to yield to them in matters like these...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<It would seem that a solution is to create a query that selects all the data from the view and to set the ODBC timeout on that query.
Perhaps not the most elegant solution, but it appears to work. >>

  That's the best way to handle it.   Part of the issue is that the ODBC driver itself is at work, not just Access.

 So you need something to specify the timeout somewhere.  In Access, on a "linked table", the fastest way to do that is with a querydef, which exposes a timeout property.

 If you were using ADO to access the view in code, you could specify it with connection timeout or command timeout.

<<I have tried using CurrentDB.Timeout = 180, but that does't seem to have any affect.>>

 This is only a default value for new queries.

Jim.
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
pjrv2Author Commented:
Thanks Jim. I think that I now understand.
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 Applications

From novice to tech pro — start learning today.