August 31, 2006 at 5:26 am
Dear all
I have created a view on our server, to which we have ODBC'ed in Access.
Unfortunately, however, whenever we try to open the view in Access, we keep getting "ODBC -- called failed. Microsoft ODBC SQL Server Driver Timeout Expired #0".
I've changed the advanced Access settings to increase the OLE/DDE timeout to the maximum, but the problem is still happening.
FYI, the view takes about 3.5 minutes to appear in SQL query analyser, is 8 colums wide and only about 15k rows.
Any help much appreciated!
Thanks
Phil
August 31, 2006 at 10:52 pm
Hi Phil
Can you pin it down to just this view? Do you have any linked tables which can open or perhaps another view - preferably simpler and faster to respond. If you can open other linked objects OK then that tends to suggest you have a problem in the view.
But it could simply be a performance issue on the view. How is it being opened - directly in the object list (tables) or via a query/form/report?
If you are opening it via a query (whether that is a pure query, or as a record source for a form or report) there is an ODBC Timeout option on the properties of the query itself - I think the default will be zero, but you could attempt a setting of say 900 which I think is milliseconds. 3.5 minutes in QA is quite a long time so maybe an even bigger number could be appropriate.
Also, maybe this view is a candidate for either conversion to a stored procedure, or at the very least some serious reviewing for performance issues.
Hope this provides ome clues.
All the best.
Rowan
September 5, 2006 at 2:12 pm
Another option is to convert your view to a store procedure. You will be able to view the store procedure from an Access project or even Excel.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply