如何将Access表数据插入带Identity的SQL Server ODBC链接表？

我尝试过多种方法将Access表的数据插入SQL Server的ODBC链接表，但遇到了瓶颈——目标SQL Server表的ID列是Identity自增列，导致无法插入Access表中的原有ID值。我试过在Access查询容器或VBA里执行SET IDENTITY_INSERT ON，但完全没用。

以下是测试用的VBA代码示例：

案例1：可成功插入（不包含ID列） #

Sub InsertData()
    On Error GoTo ErrorHandler
    
    Dim strSQL As String
    
    ' 构造不包含ID列的插入语句
    strSQL = "INSERT INTO odbc_link_table_from_sql_server (col2) " & _
             "SELECT col2 FROM access_table"
    
    ' 执行SQL
    CurrentDb.Execute strSQL, dbFailOnError
    
    MsgBox "Data inserted successfully!"
    
    Exit Sub
    
ErrorHandler:
    Debug.Print err.description
End Sub

//output : Data inserted successfully!

案例2：无法插入（包含ID列） #

Sub InsertData()
    On Error GoTo ErrorHandler
    
    Dim strSQL As String
    
    ' 构造包含ID列的插入语句
    strSQL = "INSERT INTO odbc_link_table_from_sql_server (ID,col2) " & _
             "SELECT ID,col2 FROM access_table"
    
    ' 执行SQL
    CurrentDb.Execute strSQL, dbFailOnError
    
    MsgBox "Data inserted successfully!"
    
    Exit Sub
    
ErrorHandler:
    Debug.Print err.description
End Sub

//output : An error occurred: ODBC--call failed. 48

我的需求：

• 不得修改SQL Server表结构
• 不得使用循环插入
• 最终希望通过Access查询容器实现数据插入

核心问题是：Access默认给每条SQL分配独立的SQL Server会话，单独执行SET IDENTITY_INSERT ON会因为会话断开而失效。必须让开关指令和插入操作在同一个SQL Server会话中执行。

方法1：使用Access传递查询（符合查询容器需求） #

这是最直接满足你需求的方式：

1. 在Access中创建传递查询：
   • 打开查询设计，关闭「显示表」窗口，右键选择「SQL特定查询」→「传递」
   • 在查询属性中设置目标SQL Server的ODBC连接（可选择已有数据源或手动输入连接字符串）
2. 编写包含身份开关和插入逻辑的SQL语句（注意指定SQL Server表的完整架构，比如dbo.前缀）：

   SET IDENTITY_INSERT dbo.odbc_link_table_from_sql_server ON;
   INSERT INTO dbo.odbc_link_table_from_sql_server (ID, col2)
   SELECT ID, col2 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'C:\你的Access文件路径\数据库.mdb';'admin';'', access_table);
   SET IDENTITY_INSERT dbo.odbc_link_table_from_sql_server OFF;
   

   替换路径、用户名和密码为你的实际信息，执行该查询即可完成批量插入。

方法2：VBA中用ADODB保持会话一致（备选） #

如果需要用VBA辅助，不要用CurrentDb.Execute，改用ADODB连接维持会话：

Sub InsertWithIdentity()
    On Error GoTo ErrorHandler
    
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    ' 替换为你的SQL Server连接字符串
    conn.Open "Driver={SQL Server};Server=你的服务器名;Database=你的数据库;UID=用户名;PWD=密码;"
    
    ' 同一连接内执行所有操作
    conn.Execute "SET IDENTITY_INSERT dbo.odbc_link_table_from_sql_server ON;"
    conn.Execute "INSERT INTO dbo.odbc_link_table_from_sql_server (ID, col2) " & _
                 "SELECT ID, col2 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', " & _
                 "'C:\你的Access文件路径\数据库.mdb';'admin';'', access_table);"
    conn.Execute "SET IDENTITY_INSERT dbo.odbc_link_table_from_sql_server OFF;"
    
    conn.Close
    MsgBox "数据插入成功！"
    
    Exit Sub
    
ErrorHandler:
    Debug.Print Err.Description
    If Not conn Is Nothing Then conn.Close
End Sub

• 使用OPENROWSET需要SQL Server开启Ad Hoc Distributed Queries配置，执行以下语句开启（仅需一次）：

  sp_configure 'show advanced options', 1;
  RECONFIGURE;
  sp_configure 'Ad Hoc Distributed Queries', 1;
  RECONFIGURE;
  

• 确保SQL Server服务账号能读取你的Access文件，可将Access文件放在SQL Server可访问的共享文件夹中。

内容的提问来源于stack exchange，提问作者Thanapol Pasokpakdee