Causing timeouts querying SQL Server

[jpm]

Hi,

Access 2007 or Access 2003 querying tables linked to Sql Server 2005
database.

I created a recordset with the "Lock Type" property set to "No Locks".
The Recordset Type was set to "Snapshot"

The query was looking at a range of 100000 records, ordering them by a
couple of fields and then returning the top 2500 records.

The query runs pretty quickly but it is apparently putting lock(s) on the
underlying datatable, to the level that they're causing user timeouts in
other applications which are using the SQL databased. I can relate the
occurrence of the error directly to my running of the query; as long as I
have the query results/datasheet window open, the users experience the
error.

If I run the same Sql string in a query window in SQL Server Management
Studio, the query returns the results quickly and causes no problem.

Why is msAccess causing this locking problem given the properties I have
set. Is there some other property I should be adjusting? Or is this
another sign that I should get away from using MsAccess, even for reporting
purposes?

Thanks for any substantive pointers and commentary,

Jim M.

 

[jpm]

Thanks,
I arrived at this option before I read this post; the pass-through query is
the way to go it you must use Access as an interface to the data. It still
irks me that it has locks on the data when I creat a 'regular'query in
Access with the recordset type set to snapshot. To my little mind that ought
to be the equivalent of a 'pass-through' query because you just want a
read-only result, even where you're joining local and linked tables in your
query.

jim M.

 

[jpm]

Thanks,
I arrived at this option before I read this post; the pass-through query is
the way to go it you must use Access as an interface to the data. It still
irks me that it has locks on the data when I creat a 'regular'query in
Access with the recordset type set to snapshot. To my little mind that ought
to be the equivalent of a 'pass-through' query because you just want a
read-only result, even where you're joining local and linked tables in your
query.

jim M.