NOLOCKS / Ac2000 & Pass-through query / SQL Server

Posted on 2006-05-08
Last Modified: 2012-08-13
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"


Question by:SpencerSteel
    1 Comment
    LVL 142

    Accepted Solution

    >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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now