如何通过Access VBA获取SQL Server新插入行的主键值

现有代码获取主键失败的核心原因是INSERT操作和标识值查询操作不在同一个SQL Server会话中，具体表现对应如下：

• DoCmd.RunSql执行链接表插入时，会从连接池取一个连接执行插入，执行完成后连接立刻释放，会话上下文销毁
• 后续用CurrentDb.OpenRecordset执行查询时，会新开一个独立会话，@@IDENTITY、SCOPE_IDENTITY()都是会话级函数，跨会话无法读取之前插入操作生成的标识值，因此始终返回0
• 执行SELECT SCOPE_IDENTITY()报“不存在该列”，是因为该查询通过Access本地的ACE/JET引擎解析执行，本地引擎不认识SQL Server专属的SCOPE_IDENTITY()函数，直接将其识别为不存在的列名

可以，但必须保证INSERT语句和SELECT @@IDENTITY在同一个SQL Server连接会话中连续执行，不能拆分到两个独立的执行动作里。
可行的直接写法示例（用ADO同连接执行）：

Dim conn As ADODB.Connection
Dim lastId As Long
' 初始化和SQL Server的直连，连接参数与对应链接表指向的库保持一致
Set conn = New ADODB.Connection
conn.Open "你的SQL Server连接字符串"
' 同一会话先执行插入，再查询标识值
conn.Execute "INSERT INTO Departments (DeptName) VALUES ('Department A')"
lastId = conn.Execute("SELECT SCOPE_IDENTITY()")(0).Value
conn.Close
Set conn = Nothing

注意：生产环境优先用SCOPE_IDENTITY()替代@@IDENTITY，避免表上触发器插入其他标识列时返回错误的主键值，该函数需要通过直连SQL Server或者直通查询执行，不能走Access本地查询引擎。

这个方案完全可行，而且是稳定性更高的生产环境推荐方案，不需要手动维护会话一致性，还能避免SQL注入风险。

1. 先在SQL Server端创建带输出参数的存储过程：

CREATE PROCEDURE usp_InsertDepartment
    @DeptName NVARCHAR(255),
    @NewDeptId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Departments(DeptName) VALUES (@DeptName);
    SET @NewDeptId = SCOPE_IDENTITY();
END

2. 在Access VBA中调用该存储过程，直接读取输出参数即可拿到新插入行的主键值，不需要额外执行单独的标识查询语句。

内容的提问来源于stack exchange，提问作者Doug Kimzey