Increase Timeout for a View?

Access Client, SQL Server backend.

In the Access client I am linking to a view on SQL Server.

The view is taking a long time to process, and eventually times out.

The same view takes a long time to return (in SQL Server Query Analyzer) but finishes.

I am looking at 2 options:

1)  Wrap a Pass Through Query around the View and use the Pass Through Query ODBC Timeout property to help with the timeout issue.

2)  Increase the timout for the View in the Access client (Is this possible?  I didn't see a "Timeout" property for a View like I did for a Pass Through query)
LVL 5
Tom KnowltonWeb 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.

Karen FalandaysTraining SpecialistCommented:
I'm guessing the "view" comes from a query of some sort in Access, and you can increase the timeout in the design view of the query properties. If you set the timeout to 0, no timeout error occurs.

You may want to see what else is happening in the query/sql behind the scenews of the view, if it continues to be slow
Karen
0
Karen FalandaysTraining SpecialistCommented:
I'm guessing the "view" comes from a query of some sort in Access, and you can increase the timeout in the design view of the query properties. If you set the timeout to 0, no timeout error occurs.

You may want to see what else is happening in the query/sql behind the scenews of the view, if it continues to be slow
Karen
0
Tom KnowltonWeb developerAuthor Commented:
"View" in this case is a special read-only recordset available in SQL Server

I see the "View" I have to link to it via ODBC or some other means.  The code behind the View resides on SQL Server.
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.

Karen FalandaysTraining SpecialistCommented:
Sorry to be so dense, but with Access as the client, how are you accessing the view? Is it listed with the table objects?
0
Tom KnowltonWeb developerAuthor Commented:
Correct.

The ICON for the link to the SQL Server View looks different....it is a globe icon.

When I go into the Design View for the SQL Server View object, and righ-click on the Title Bar and go to Properties....there seems to be no "ODBC Timeout" property (like you would see for a SQL Server Pass Through Query object)
0
Karen FalandaysTraining SpecialistCommented:
OK, that's good. It's a linked table to Access
Now try this:
Create a query based on the view, add all of the fields and change the properties to see the odbc timeout to 0. Will that work?
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
Tom KnowltonWeb developerAuthor Commented:
RE:  setting the timeout to 0...yes I will try that in a minute.

As an example:

SELECT Count(homebuyerThanksView.EntryID) AS HBCount FROM homebuyerThanksView;

Gives a timeout error in Access.

If I run the SAME thing in SQL Server's Query Analyzer, it will finish, but takes a long time.

Hope this helps,

Tom
0
Karen FalandaysTraining SpecialistCommented:
Oh I see what you are doing. I have to think about that some more.

On another note, are you friendly with the dba who owns/maintains the data? Sounds like they need to add an index here or there to help speed things up. THat almost always does the trick.
0
Tom KnowltonWeb developerAuthor Commented:
The DBA has admitted that the problem is on his end.

As a temporary work-around I am trying to increase the timeout duration so the program can still FUNCTION, albeit very very slowly.
0
Karen FalandaysTraining SpecialistCommented:
OK, good luck with that. In the meanwhile, in the main screen of Access, try to go to Tools, Options, Advanced and manipulate some of these settings..it may help!
Karen

0
Tom KnowltonWeb developerAuthor Commented:
Thanks, Karen.

Tom
0
Karen FalandaysTraining SpecialistCommented:
Anytime, and let me know if any of the other things worked!
0
Tom KnowltonWeb developerAuthor Commented:
As a matter of fact, setting the ODBC Timeout to 0 worked for the 3 queries I'm using for counting the number of records.

Looks like you'll be getting partial if not full credit for this question.

I need a few more minutes to test....and then I'll be back here to award your points!

:)

Thanks,

Tom
0
Karen FalandaysTraining SpecialistCommented:
Oh very cool. I know how frustrating that can be! Hope the dba can get those indexes for you too, that will REALLY speed them up! I had views accessing 6 million records, in a multiuser that was dragging until he re-indexed correctly!
0
Tom KnowltonWeb developerAuthor Commented:
Your advice allowed me to workaround the problem.

The points are yours, with my sincere gratitude!

Tom
0
Karen FalandaysTraining SpecialistCommented:
Wowee! I'm so glad for you, and I love the points!
Karen
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.