Results 1 to 3 of 3
  1. #1
    DavidT is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    5

    Question CREATE VIEW syntax for linked SQL view

    I have an Access FE (2010-2013 ACCDB) linked to SQL BE (2005-2012). Utilizing SQL view for menu forms. Users can view, filter, and select a record on the menu form, double-click to open a single record on another "source" form. The problem I am experiencing is when multiple users are logged into the program, updates from the "source" form are being blocked by the select statement on which the view is based. I have run traces on the sql db and witnessed this behavior. For example, I have a parts menu form based on a view that displays part records and an aggregate of the number of parts on order. The part information comes from tblPart, inventory location information comes from tblPartLocation, and parts on order information comes from a derived table that aggregates the number of parts yet to be received from purchase orders not yet completed. If, after receiving parts, I attempt to update the status of the purchase order to "Completed", the update on tblOrder is blocked by the view's select statement.

    I've added a WITH (NOLOCK) table hint to the part view so I can get a non-blocking "dirty" read for the part menu form, but I can't imagine that this would be the magic fix.

    Here is the script to create the view with the NOLOCK hints:

    'CREATE VIEW [dbo].[vwPartMenu1]
    AS
    SELECT dbo.tblPart.Part, dbo.tblPart.Description, dbo.tblPart.Class, dbo.tblPart.Manufacturer, dbo.tblPart.Model, dbo.tblPart.Stock, dbo.tblPart.Hazardous, dbo.tblPart.Account,
    dbo.tblPart.ControlNumber, dbo.tblPart.MinQty, dbo.tblPart.MaxQty, dbo.tblPart.CostAverage, dbo.tblPart.MaintenanceCategory, dbo.tblPart.Active,
    dbo.tblPart.FuelFluid, ISNULL(OHRSV.OnHand, 0) AS OHTotal, ISNULL(OHRSV.Reserved, 0) AS RSVTotal, ISNULL(OO.OnOrder, 0) AS OOTotal
    FROM dbo.tblPart WITH (NOLOCK) LEFT OUTER JOIN
    (SELECT dbo.tblOrderDetail.Part, SUM(dbo.tblOrderDetail.Quantity * dbo.tblOrderDetail.SizeCase - dbo.tblOrderDetail.Received) AS OnOrder
    FROM dbo.tblOrder WITH (NOLOCK) INNER JOIN
    dbo.tblOrderDetail ON dbo.tblOrder.OrderID = dbo.tblOrderDetail.OrderID
    WHERE (dbo.tblOrder.CanceledDate IS NULL) AND (dbo.tblOrder.CompletedDate IS NULL) AND (dbo.tblOrder.Status = ''Issued'' OR
    dbo.tblOrder.Status = ''Partial'')
    GROUP BY dbo.tblOrderDetail.Part
    HAVING (SUM(dbo.tblOrderDetail.Quantity * dbo.tblOrderDetail.SizeCase - dbo.tblOrderDetail.Received) > 0)) AS OO ON
    dbo.tblPart.Part = OO.Part LEFT OUTER JOIN


    (SELECT L.Part, ISNULL(SUM(L.OnHand), 0) AS OnHand, ISNULL(W.QtyEst, 0) AS Reserved
    FROM dbo.tblPartLocation AS L WITH (NOLOCK) LEFT OUTER JOIN
    (SELECT dbo.tblWOpart.Part, SUM(dbo.tblWOpart.QuantityEstimated) AS QtyEst
    FROM dbo.tblWOpart INNER JOIN
    dbo.tblWO ON dbo.tblWOpart.WO = dbo.tblWO.WO
    WHERE (dbo.tblWO.Completed IS NULL) AND (dbo.tblWO.Canceled IS NULL)
    GROUP BY dbo.tblWOpart.Part) AS W ON L.Part = W.Part
    GROUP BY L.Part, W.QtyEst) AS OHRSV ON dbo.tblPart.Part = OHRSV.Part'

    Does anyone have any alternative ideas? Can anyone point me to other resources for best practices on creating and using views in a multi-user environment?

    I was under the impression that views would be THE solution for an Access FE to reduce network traffic and performance. I don't believe that adding NOLOCK to all of my views is the best solution, but will do it if that is what it takes.

    Thanks in advance.

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    This is a total shot in the dark, but try putting a "WITH (NOLOCK)" hint on dbo.tblOrderDetail as well.

    I have no other reason than its proximity in an INNER JOIN to the table you are being blocked from updating.

  3. #3
    DavidT is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    5
    Dal,

    Thanks for responding. With all the joins in the view, I am not surprised I missed one.

    What I was hoping for was insight on SQL Server view best practices. Since originally posting, I've done a lot of reading on concurrency in SQL. I could not understand why SELECTs were blocking UPDATEs. MS and others recommend setting db isolation levels on the db (READ_COMMITTED_SNAPSHOT). The cost is having to plan for and maintain the tempdb. Now I am researching the behavior of the sql tempdb.

    Bottom line, if dirty reads and the possibility of phantom inserts and lost updates are acceptable, WITH (NOLOCK) hint is okay.

    Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. remove design view and layout view on right click
    By sdel_nevo in forum Programming
    Replies: 2
    Last Post: 07-11-2013, 01:00 AM
  2. Need to view linked document in report
    By dagwood in forum Reports
    Replies: 4
    Last Post: 05-09-2013, 07:27 AM
  3. Replies: 8
    Last Post: 04-29-2013, 11:23 AM
  4. Replies: 16
    Last Post: 09-12-2012, 08:39 AM
  5. Replies: 0
    Last Post: 05-21-2012, 07:00 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums