UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Odbc Timeout On Linked SQL Server Views    
 
   
ws1o
post Apr 6 2017, 03:27 PM
Post#1



Posts: 67
Joined: 30-October 15
From: Central CT USA


I have a View on a SQL Server that runs a bit slow (it takes a couple of minutes). I can get the results just fine in SQL Server Management Studio, but when I link to the View in Microsoft Access 2016 and then try to open the view, I get an ODBC timeout error.

Does anyone know if there is any way to change the Access ODBC timeout setting to let this take as long as it needs to run?
Go to the top of the page
 
PhilS
post Apr 7 2017, 02:41 AM
Post#2



Posts: 404
Joined: 26-May 15
From: The middle of Germany


It might not be required to open the view as a whole at all. Most likely you only need to query some of the data from it, which might be much faster.

If querying only the needed data from the view still runs into the timeout, create a stored query in Access to retrieve the data from the view. A query has an ODBC Timeout-Property you can set to your requirements. If you need to query the data in VBA code, use a QueryDef-Object with the SQL of your query on the view. The QueryDef-Object has the ODBCTimeout-Property as well.

--------------------
Go to the top of the page
 
ws1o
post Apr 7 2017, 07:30 AM
Post#3



Posts: 67
Joined: 30-October 15
From: Central CT USA


That's what I'm doing, for now: I created a pass-through query that pulls data from the saved View.
Go to the top of the page
 
PhilS
post Apr 7 2017, 12:37 PM
Post#4



Posts: 404
Joined: 26-May 15
From: The middle of Germany


QUOTE
I created a pass-through query that pulls data from the saved View.

Good idea.
It should even work with a query on the linked view inside Access. That would be update-able provided the base view is and you manually defined a unique key on the linked table (view) in Access.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 01:02 AM