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

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.
deer777Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
0
deer777Author 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?
0
ste5anSenior DeveloperCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.