SQL Server & MS Access separate Views for data entry and read only forms?

Question

I'm working on a small company information system using MS Access as the front end and SQL Server 2019 Express as the back end. I am a bit confused about views at the moment.

Here is what I have:

CREATE FUNCTION dbo.DisplayCurrencyFormat
(
    @Amount DECIMAL(10,2), 
    @Currency INT
)
RETURNS NVARCHAR(100)
AS
BEGIN
    RETURN
        CASE
        WHEN @Currency = 1 THEN FORMAT(@Amount, 'C', 'cs-cz')
        WHEN @Currency = 2 THEN FORMAT(@Amount, 'C', 'de-ch')
        WHEN @Currency = 3 THEN FORMAT(@Amount, 'C', 'en-us')
        WHEN @Currency = 4 THEN FORMAT(@Amount, 'C', 'de-de')
    END
END

CREATE VIEW v_PurchaseOrderLines
AS
    SELECT  tbl1PurchaseOrderDetails.PurchaseOrderDetailID,
            tbl1PurchaseOrderDetails.PurchaseOrderID,
            tbl1Products.ProductName,
            tbl1PurchaseOrderDetails.Config,
            dbo.DisplayCurrencyFormat(ListPrice,CurrencyID) AS ListPrice,
            tbl1PurchaseOrderDetails.Quantity,
            FORMAT(tbl1PurchaseOrderDetails.Discount, 'P0') AS Discount,
            dbo.DisplayCurrencyFormat(UnitPrice,CurrencyID) AS UnitPrice,
            dbo.DisplayCurrencyFormat(UnitPrice*Quantity,CurrencyID) AS TotalPrice,
            FORMAT (tbl1PurchaseOrderDetails.VAT, 'P0') AS VAT,
            dbo.DisplayCurrencyFormat(UnitPrice*Quantity*(1+VAT),CurrencyID) AS TotalPriceVAT,
            tbl1PurchaseOrderDetails.ExpectedDelivery,
            tbl1PurchaseOrderDetails.Notes
    FROM tbl1PurchaseOrderDetails   JOIN tbl1Products ON tbl1PurchaseOrderDetails.ProductID = tbl1Products.ProductID
;

GO

CREATE VIEW v_PurchaseOrderLines_DE
AS
    SELECT  tbl1PurchaseOrderDetails.PurchaseOrderDetailID,
            tbl1PurchaseOrderDetails.PurchaseOrderID,
            tbl1PurchaseOrderDetails.ProductID,
            tbl1PurchaseOrderDetails.Config,
            tbl1PurchaseOrderDetails.Quantity,
            tbl1PurchaseOrderDetails.Discount,
            tbl1PurchaseOrderDetails.UnitPrice,
            tbl1PurchaseOrderDetails.CurrencyID,
            tbl1PurchaseOrderDetails.VAT,
            tbl1PurchaseOrderDetails.ListPrice,
            dbo.DisplayCurrencyFormat(UnitPrice*Quantity,CurrencyID) AS TotalPrice,
            dbo.DisplayCurrencyFormat(UnitPrice*Quantity*(1+VAT),CurrencyID) AS TotalPriceVAT,
            tbl1PurchaseOrderDetails.ExpectedDelivery,
            tbl1PurchaseOrderDetails.Notes
    FROM tbl1PurchaseOrderDetails
;

This works quite well but I'm stuck with 2 views. I'm not able to use any view with FORMAT function or joins inside my data entry forms, it will not accept any changes, so the first view is used for the read only form (looking at purchase orders). However I still need to see calculated total prices in real time on my data entry form, that's why I can't feed data directly from the table.

The second view is then used for editing purchase orders.

Is there some workaround how to do all this within a single view, or am I stuck with 2 views for every entity like this one?

Thanks a lot for any tips.

The problem is that SQL Server has much more capabilities in formatting data than Access and I would rob myself from that by using this approach. — ThomassoCZ

Andre Andre · Accepted Answer · 2022-06-28T09:32:39

Linked Views with JOINs are editable in Access, if the PK of the underlying table you want to edit is included in the view, and you specify it as PK when linking the view.

Percent formatting can be done in the form.

Currency formatting: you simplify your life and the view by putting the currency symbol into a separate field in view and form. But it is less perfect. :)

It will still depend on your exact requirements if you can get away with a single view for list display vs. editing. It is not always possible.

SQL Server & MS Access separate Views for data entry and read only forms?

1 Answers