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.