SQL View Linked in Access Times Out

1
votes

I have a SQL view, which takes 4-5 seconds to run, with no filters, if I run it within SSMS. If I try to open the linked "table" in Access 2010, it times out.

In the Options - Client Side Settings, I set the OLE/DDE timeout to 0, and the ODBC timeout to 0. I still get the ODBC--call failed. [Microsoft][ODBC SQL Server Driver]Query timeout expired (#0). Once I click ok, I get another message [current application name] can't open the table in Datasheet view.

I just don't understand how I can't open this linked table in Access, but the underlying view only has 88 records right now. There are a lot of columns, but few results, and it only takes a few seconds to run in SSMS. Why does it timeout and have such a problem as a linked table in Access?

Any help is greatly appreciated.

Thanks!

1
ms-accesstimeoutodbclinked-server
When you open the linked table in Design View in Access does it show a primary key (little gold key icon beside one or more fields)? - Gord Thompson
no, there is no primary key. There really isn't one, because this sql view is basically a left joining of all the tables in the app (more or less), to provide provide all fields and info in app, so it can be the basis of a build your own report form. They choose the fields from the list of those fields, and filters from listboxes, which are fed as criteria to their appropriate field. in essence it will always have a filter applied, but should be able to run completely in access, as it does in sql. - missscripty
Can you confirm that SELECT COUNT(*) AS n FROM dbo_YourViewName in Access returns the row count without timing out? Also, by chance does the SQL view return more than 255 columns? - Gord Thompson
Thank you for the responses. There are 62 columns, and I cannot get a count from the view in Access. I can in SSMS. - missscripty
unfortunately, I have joins to 2 different sql database servers, and one has a different collation. Access will only allow that in a passthrough and that is not working, so I will have to work on other methods of getting this complete view working. - missscripty

1 Answers

1
votes

So I was looking at this puzzle, with a colleague. It would be difficult and still poor performance, to translate this 118 line query, with 30 table joins, into an Access query.

Instead, I am breaking the sections of the giant view, into separate smaller views. Each independent view, will be joined in an access query, so that each section of the query can be filtered independently, and allow for smaller sets of results, thereby improving the overall performance.