SQL Server+Access前端：配送路线序号并发生成与修改方案咨询

针对你遇到的多用户并发添加订单时序号重复、VBA方案失效的问题，以下是基于SQL Server后端的完整解决方案，确保序号唯一、支持顺序调整，且完全规避并发冲突。

一、新增订单时自动生成唯一序号（并发安全） #

核心思路是将序号生成逻辑放在SQL Server端，通过事务+行锁避免并发冲突。假设：

• 车辆日记录表：VanDaily，字段VanDailyID（每日车辆唯一标识，如Van1-2023/01/01对应ID=1）
• 订单表：SalesOrders，字段包括SalesOrderID（主键）、VanDailyID（关联车辆日记录）、RouteOrderSequence（配送序号，总部为0）、其他业务字段

创建新增订单存储过程 #

CREATE PROCEDURE dbo.AddSalesOrderWithRouteSequence
    @VanDailyID INT,
    -- 按需添加其他订单参数（如CustomerID、DeliveryAddress等）
    @NewSalesOrderID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    DECLARE @NextSeq INT;

    -- 锁定当前车辆日的所有订单，防止并发会话获取相同的最大序号
    SELECT @NextSeq = ISNULL(MAX(RouteOrderSequence), 0) + 1
    FROM SalesOrders WITH (UPDLOCK, HOLDLOCK)
    WHERE VanDailyID = @VanDailyID;

    -- 插入新订单并生成序号
    INSERT INTO SalesOrders (VanDailyID, RouteOrderSequence /* 其他业务字段 */)
    VALUES (@VanDailyID, @NextSeq /* 对应参数值 */);

    SET @NewSalesOrderID = SCOPE_IDENTITY();

    COMMIT TRANSACTION;
END;

说明：UPDLOCK和HOLDLOCK会在事务期间锁定查询范围内的行，确保其他会话无法修改/插入同一车辆日的订单，彻底避免并发序号重复问题。

二、调整订单配送顺序（上下移动） #

通过事务包裹序号交换操作，保证顺序调整的原子性，避免中间状态异常。

向上移动订单（序号减1） #

CREATE PROCEDURE dbo.MoveOrderUp
    @SalesOrderID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    DECLARE @CurrentSeq INT, @VanDailyID INT;

    -- 获取当前订单的序号和所属车辆日ID
    SELECT @CurrentSeq = RouteOrderSequence, @VanDailyID = VanDailyID
    FROM SalesOrders
    WHERE SalesOrderID = @SalesOrderID;

    -- 边界判断：第一个配送订单（序号=1）无法向上移动
    IF @CurrentSeq <= 1
    BEGIN
        RAISERROR('无法向上移动，已是第一个配送订单', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- 交换当前订单与上一个订单的序号
    UPDATE SalesOrders
    SET RouteOrderSequence = RouteOrderSequence + 1
    WHERE VanDailyID = @VanDailyID AND RouteOrderSequence = @CurrentSeq - 1;

    UPDATE SalesOrders
    SET RouteOrderSequence = @CurrentSeq - 1
    WHERE SalesOrderID = @SalesOrderID;

    COMMIT TRANSACTION;
END;

向下移动订单（序号加1） #

CREATE PROCEDURE dbo.MoveOrderDown
    @SalesOrderID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    DECLARE @CurrentSeq INT, @VanDailyID INT, @MaxSeq INT;

    SELECT @CurrentSeq = RouteOrderSequence, @VanDailyID = VanDailyID
    FROM SalesOrders
    WHERE SalesOrderID = @SalesOrderID;

    -- 获取当前车辆日的最大序号
    SELECT @MaxSeq = MAX(RouteOrderSequence)
    FROM SalesOrders
    WHERE VanDailyID = @VanDailyID;

    -- 边界判断：最后一个配送订单无法向下移动
    IF @CurrentSeq >= @MaxSeq
    BEGIN
        RAISERROR('无法向下移动，已是最后一个配送订单', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- 交换当前订单与下一个订单的序号
    UPDATE SalesOrders
    SET RouteOrderSequence = RouteOrderSequence - 1
    WHERE VanDailyID = @VanDailyID AND RouteOrderSequence = @CurrentSeq + 1;

    UPDATE SalesOrders
    SET RouteOrderSequence = @CurrentSeq + 1
    WHERE SalesOrderID = @SalesOrderID;

    COMMIT TRANSACTION;
END;

三、异常修复：重新整理序号连续性 #

若出现手动修改序号导致的重复/断层问题，可通过以下存储过程重新生成连续序号：

CREATE PROCEDURE dbo.ResequenceOrdersForVanDaily
    @VanDailyID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    -- 按原序号排序，重新生成连续的配送序号
    WITH OrderedOrders AS (
        SELECT SalesOrderID,
               ROW_NUMBER() OVER (ORDER BY RouteOrderSequence) AS NewSeq
        FROM SalesOrders
        WHERE VanDailyID = @VanDailyID
    )
    UPDATE SalesOrders
    SET RouteOrderSequence = oo.NewSeq
    FROM SalesOrders so
    JOIN OrderedOrders oo ON so.SalesOrderID = oo.SalesOrderID;

    COMMIT TRANSACTION;
END;

四、Access前端集成 #

在Access中通过ADO调用SQL Server存储过程，替代原VBA逻辑：

' 示例：新增订单
Sub AddNewSalesOrder(VanDailyID As Integer)
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim newOrderID As Integer

    Set conn = New ADODB.Connection
    conn.Open "你的SQL Server连接字符串" ' 示例："Provider=SQLOLEDB;Data Source=服务器名;Initial Catalog=数据库名;User ID=账号;Password=密码;"

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "dbo.AddSalesOrderWithRouteSequence"

    ' 添加参数
    cmd.Parameters.Append cmd.CreateParameter("@VanDailyID", adInteger, adParamInput, , VanDailyID)
    ' 按需添加其他业务参数
    cmd.Parameters.Append cmd.CreateParameter("@NewSalesOrderID", adInteger, adParamOutput)

    cmd.Execute

    newOrderID = cmd.Parameters("@NewSalesOrderID").Value
    MsgBox "新增订单ID：" & newOrderID

    conn.Close
    Set cmd = Nothing
    Set conn = Nothing
End Sub

上下移动按钮的逻辑类似，调用对应存储过程即可，捕获存储过程抛出的错误提示给用户。

内容的提问来源于stack exchange，提问作者Darren Oxley