• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 36
  • Last Modified:

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.
0
deer777
Asked:
deer777
  • 2
1 Solution
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now