SQL Server/Access Front End Need to create a checkout process on primary key (product_number)

deer777
deer777 used Ask the Experts™
on
Working with an Access front end/SQL Server backend (linked tables).  Need to create process that will send a message to any user that tries to open the same product_number that is already checked out by another user at the same time.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
First of all: While this is not a hard task, it is a complicate one. Cause what happens when a user forgets to check-in again?

But the solution is pretty simple: Use a store procedure for this. Something like

CREATE PROCEDURE CheckoutItem ( @ProductNumber INT, @UserName SYSNAME OUTPUT )
AS
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

	-- Constants.
    DECLARE @GENERIC_ERROR INT = -1; 
    DECLARE @NO_ERROR INT = 0;    

    BEGIN TRY 
        INSERT INTO Checkout ( ProductNumber ) VALUES ( @ProductNumber );
        RETURN @NO_ERROR;
    END TRY
    BEGIN CATCH
        SELECT @UserName = UserName 
        FROM Checkout
        WHERE ProductNumber = @ProductNumber;
        RETURN @GENERIC_ERROR;
    END CATCH;

Open in new window


with
CREATE TABLE Checkout
    (
        ProductNumber INT NOT NULL PRIMARY KEY ,
        UserName sysname
            DEFAULT ( SUSER_SNAME())
    );

Open in new window


and run this as pass-through query in Access to check-out your product.

Author

Commented:
Can we include a message to the user trying to check out this product_name record that is already checked out in this stored procedure?
Senior Developer
Commented:
This is already covered by @UserName SYSNAME OUTPUT :)

E.g. the pass-through can look like:

SET NOCOUNT ON;

DECLARE @Message NVARCHAR(255);
DECLARE @Result INT;
DECLARE @ProductNumber INT = 123;

EXECUTE @Result = Checkout @ProductNumber ,
                           @UserName OUTPUT;

SELECT @Result AS Result ,
       @UserName AS UserName;

Open in new window


Now you can evaluate the result in Access:

0 => success
-1 => error, when UserName contains a user then it is already checked out, otherwise another error

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial