Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

NOLOCKS / Ac2000 & Pass-through query / SQL Server

Guys and girls,

I've been doing a little reading around this one but need some advice.

I've got an Access 2000 app that pretty heavily relies on a 'pass-through query' to our SQL Server 2000.

I've noticed that sometime the list/combo boxes in the Access 2000 application will lock the entire table. The table-lock can be seen in Enterprise Manager. This only seems to happen when the qry returns a 'large' amounts of rows.

Now, returning 'large' recordsets is obviously bad practice and something I try to avoid, but the nature of the 'users searching' functions means that they can, in theory, return a lot of records, which are then displayed in these combo-boxes.

I've read that you can add NOLOCKS to the SQL statements which should avoid any locking. This is something I am keen on playing with for a couple of my listboxes and would like to see it working ....

The problem I have, is that even with the NOLOCKS, the tables are still being locked up ... should the below statement work ?? If not, why not and do you think what I am trying to do is impossible?

Here is the basic code....


Set myDB = CurrentDb()

Set myQuery = myDB.QueryDefs("myPassThrough")

mySQL = "SELECT TblClientContact.ClientContactID, TblClient.ClientName .......... FROM TblClientContact WITH (NOLOCK) INNER JOIN TblClient WITH (NOLOCK) ON TblClientContact.ClientID = TblClient.ClientID "

myQuery.SQL = mySQL

myQuery.ReturnsRecords = -1

Forms!frmClientContactSearch!lstClientContacts.RowSource = "myPassThrough"


1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I've read that you can add NOLOCKS to the SQL statements which should avoid any locking.

that sentence is usually misunderstood. the effect is that NOLOCK ignores existing locks and data changes, hence can read dirty data.
however, it will put itself a shared read lock itself during the time of reading the data itself

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now