Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Passthrough query locking hints

Posted on 2011-09-29
1
Medium Priority
?
493 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

688 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