Solved

Access Passthrough query locking hints

Posted on 2011-09-29
1
465 Views
Last Modified: 2012-05-12
I have been doing a bit of reading on this subject of table hints and passthrough queries and I am requested some clarification on the matter.

I have created an access database with a SQL backend and access frontend.  My connections with the SQL backend are via passthrough queries.  I have a SQL table that all users search based on criteria entered on a form and a triggered select statement in a passthrough query to retrieve records.  It is okay if dirty records are returned for the searches.

Can someone please confirm if my understanding is correct.
1.   I read that if I use 'with nolock' when I reference a table the pass through query will only
      apply a shared read lock and this should not prevent other users from searching or
     updating the table at the same time. Is the correct?

2.  If another user is currently updating the table.  Will the select query with ' with nolock' have
      to wait for the update or will it return all records as at the time of the request even if the
      records are dirty.

3.  What is the difference between using 'with nolock' and 'with readuncommitted' and which
      one is better to use.

Thanks
0
Comment
Question by:RDLFC
1 Comment
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 36817388
1. Correct to my understanding.

2. No or at least thatS what BOL says.

3. They are equivalent although NOLOCK will be deprecated in a future release of SQL Server and you should use READUNCOMMITTED
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

786 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