NOLOCKS / Ac2000 & Pass-through query / SQL Server
Posted on 2006-05-08
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"