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"

----------------