Improve company productivity with a Business Account.Sign Up

x
?
Solved

Access Passthrough query locking hints

Posted on 2011-09-29
1
Medium Priority
?
519 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

595 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