如何使用Excel VBA获取新插入记录的id？

这似乎是一个很常见的问题，但大多数解决方案都涉及连接多个 SQL 命令，我相信这是 ADO/VBA 无法完成的（不过，我很高兴在这方面被证明是错误的）。

我当前插入新记录，然后使用（我希望）足够的字段运行选择查询，以保证仅返回新插入的记录。我的数据库很少同时被多个人访问（查询之间发生另一次插入的风险可以忽略不计），并且由于表的结构，识别新记录通常非常容易。

我现在正在尝试更新一个表，除了人工主键之外，该表没有太大的唯一性范围。这意味着存在新记录可能不唯一的风险，并且我不愿意仅仅为了强制唯一性而添加字段。

在这种情况下，将记录插入 Access 表然后从 Excel 查询新主键的最佳方法是什么？

感谢您的回复。我试图得到@@IDENTITY工作，但使用下面的代码总是返回 0。

Private Sub getIdentityTest()
    Dim myRecordset As New ADODB.Recordset
    Dim SQL As String, SQL2 As String

    SQL = "INSERT INTO tblTasks (discipline,task,owner,unit,minutes) VALUES (""testDisc3-3"",""testTask"",""testOwner"",""testUnit"",1);"
    SQL2 = "SELECT @@identity AS NewID FROM tblTasks;"

    If databaseConnection Is Nothing Then
        createDBConnection
    End If

    With databaseConnection
        .Open dbConnectionString
        .Execute (SQL)
        .Close
    End With

    myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly

    Debug.Print myRecordset.Fields("NewID")

    myRecordset.Close

    Set myRecordset = Nothing
End Sub

有什么突出的责任感吗？

然而，考虑到雷诺（Renaud）提供的有用的警告（如下），使用似乎几乎同样有风险@@IDENTITY与任何其他方法一样，所以我诉诸于使用SELECT MAX目前。为了将来的参考，我有兴趣看看我上面的尝试有什么问题。

关于你的问题：

我现在正在尝试更新一个表 没有太大的余地 独特性，除了 人工主键。这意味着 存在新纪录的风险 可能不是独一无二的，我讨厌 添加一个字段只是为了强制唯一性。

If you are使用自动增量作为主键，那么您就具有唯一性，并且可以使用SELECT @@Identity;获取最后一个自动生成的 ID 的值（请参阅下面的注意事项）。

如果你是not使用自动增量，并且您正在从 Access 插入记录，但想要从 Excel 检索最后一条记录：

• 确保您的主键是可排序的，以便您可以使用以下任一查询获取最后一个主键：

  SELECT MAX(MyPrimaryField) FROM MyTable;
  SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY MyPrimaryField DESC;
  

• 或者，如果对主字段进行排序不会给您提供最后一个字段，则您需要添加一个日期时间字段（例如InsertedDate）并在每次在该表中创建新记录时保存当前日期和时间，以便您可以获得最后一个记录，如下所示：

  SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY InsertedDate DESC;
  

在这两种情况下，我认为您会发现添加自动增量主键更容易处理：

• 这不会花费你太多

• 它将保证您的记录的唯一性，而无需考虑它

• 这将使您更轻松地选择最新记录，可以使用@@Identity或通过按主键排序或获取Max().

来自 Excel

要将数据导入 Excel，您有以下几种选择：

• 使用查询创建数据链接，以便您可以直接在单元格或区域中使用结果。

• 从 VBA 查询：

  Sub GetLastPrimaryKey(PrimaryField as string, Table as string) as variant
      Dim con As String
      Dim rs As ADODB.Recordset
      Dim sql As String
      con = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source= ; C:\myDatabase.accdb"
      sql = "SELECT MAX([" & PrimaryField & "]) FROM [" & MyTable & "];"
      Set rs = New ADODB.Recordset
      rs.Open sql, con, adOpenStatic, adLockReadOnly
      GetLastPrimaryKey = rs.Fields(0).Value
      rs.Close
      Set rs = Nothing
  End Sub
  

注意事项@@Identity

你必须的小心注意事项使用时@@Identity在标准 Access 数据库中(*)：

• 它仅适用于自动增量标识字段。

• 仅当您使用 ADO 并运行时才可用SELECT @@IDENTITY;

• 它返回最新使用的计数器，但这是为了所有桌子。您不能使用它来返回 MS Access 中特定表的计数器（据我所知，如果您使用FROM mytable，它只是被忽略）。
  简而言之，返回的值可能根本不是您期望的值。

• 您必须在INSERT以尽量减少得到错误答案的风险。
  这意味着，如果您一次插入数据并需要在另一时间（或另一地点）获取最后一个 ID，则它将不起作用。

• 最后但并非最不重要的一点是，仅当通过编程代码插入记录时才会设置该变量。
  这意味着记录是通过用户界面添加的，@@IDENTITY不会被设置。

(*)：为了清楚起见，@@IDENTITY如果您对数据库使用 ANSI-92 SQL 模式，则行为会有所不同，并且更具预测性。
但问题是 ANSI 92 的语法与 Access 支持的 ANSI 89 风格，旨在提高将 Access 用作前端时与 SQL Server 的兼容性。