logo标签列表

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

sqlexcelms-accessvbajet
9
这似乎是一个很常见的问题，但大多数解决方案都涉及连接多个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。 但是，将来我会很有兴趣看看我上面的尝试有什么问题。

使用 Max 可能会有些棘手...如果其他人正在插入记录，它可能会给你另一个记录的ID。如果只有你在插入记录，那就继续吧。 - user2421560
一个小建议是使用SCOPE_IDENTITY()通常比使用@@IDENTITY更好的实践。这样，无论您现在还是将来在多用户环境中工作，您都会得到保障。 - mattpm
6个回答
14
关于您的问题：
我现在正在尝试更新一个表，除了人工主键外，没有太多独特性。这意味着新记录可能不唯一，我不想添加字段来强制唯一性。
如果您使用自动递增作为主键，则具有唯一性，您可以使用“SELECT @@ Identity;”获取最后一个自动生成的ID的值（请参阅下面的警告）。
如果您没有使用自动递增，并且您正在从Access插入记录，但是您想从Excel检索最后一个记录：

  • make sure your primary key is sortable, so you can get the last one using a query like either of these:

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

  • or, if sorting your primary field wouldn't give you the last one, you would need to add a DateTime field (say InsertedDate) and save the current date and time every time you create a new record in that table so you could get the last one like this:

    SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY InsertedDate DESC;
在这两种情况下，我认为你会发现添加一个自增主键更容易处理：

  • 它不会花费你太多

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

  • 它将使您更容易选择最新的记录，可以使用 @@Identity 或通过按主键排序或获取 Max()。

从Excel中

要将数据导入Excel，您有几个选择：

  • create a data link using a query, so you can use the result directly in a Cell or a range.

  • query from 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 的注意事项

在使用标准的 Access 数据库(*) 中，使用 @@Identity 时需要小心注意一些限制：

  • 它只适用于自增长标识字段。

  • 只有在使用 ADO 并运行 SELECT @@IDENTITY; 时才可用。

  • 它返回最近使用的计数器，但这是针对所有表的。你无法在 MS Access 中使用它来返回特定表的计数器（据我所知，如果你使用 FROM mytable 指定一个表，它会被忽略）。
    简而言之，返回的值可能完全不是你期望的值。

  • 你必须在 INSERT 后立即查询它，以最小化获取错误答案的风险。
    这意味着，如果你一次插入数据并需要在另一个时间（或另一个地方）获取最后一个 ID，则无法使用它。

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

(*): 只是为了明确，如果您在数据库中使用ANSI-92 SQL模式，@@IDENTITY的行为会有所不同，并且更具预测性。
问题在于，ANSI 92与Access支持的ANSI 89风格略有不同，旨在在将Access用作前端时增加与SQL Server的兼容性。

1
"SELECT MAX" 的味道非常难闻。虽然在描述的情况下它能够正常工作，但是在所有情况下，事物都会随着时间而变化，不久之后这段代码就会失效！不要编写臭气熏天的代码，考虑一下下一个人。 - TFD
1
@TFD：原则上同意，但在MS Access中实现海报想要的并不是很多。这总是会有一个权衡。Max()对于大多数数据类型，包括字符串，都可以正常工作，尽管我同意可能存在一些情况，您可能无法得到想要的结果。 - Renaud Bompuis
@Remou：您能指出任何有关MS Access的@@IDENTITY更多信息的在线资源，以描述您提到的限制吗？ 我认为这在这里会很有用。 谢谢。 - Renaud Bompuis
这里的关键是您必须在数据库选项中积极选择“ANSI-92”兼容性，这会改变Access的行为。 默认情况下，Access的行为与其自身的文档所述相同。 总之，@@INDENTITY有许多陷阱，在Access中文档不足。 - Renaud Bompuis
1
虽然有点晚，但在Access QBE中不需要使用ANSI-92模式即可使用SELECT @@IDENTITY。它在DAO中也可以正常工作。但是，仅当在INSERT语句之后直接使用相同的数据库对象执行时才可靠。这意味着在Access QBE中执行它并不是非常有用，但在DAO中，您拥有完全控制权，就像在ADO中一样（您将使用相同的连接对象）。主要注意事项是，您不能使用CurrentDB来执行INSERT，然后从CurrentDB获取任何有用信息，因为指针不同。 - David-W-Fenton
显示剩余5条评论
7
如果人工键是自动编号，可以使用@@identity。
请注意，在这两个示例中，事务与其他事件隔离，因此返回的标识符是刚插入的标识符。您可以通过在Debug.Print db.RecordsAffected或Debug.Print lngRecs处暂停代码并手动向Table1插入记录来测试此功能，然后继续代码并注意返回的标识符不是手动插入的记录的标识符，而是由代码插入的上一条记录的标识符。
DAO示例
'Reference: Microsoft DAO 3.6 Object Library '
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

db.Execute ("INSERT INTO table1 (field1, Crdate ) " _
            & "VALUES ( 46, #" & Format(Date, "yyyy/mm/dd") & "#)")
Debug.Print db.RecordsAffected
Set rs = db.OpenRecordset("SELECT @@identity AS NewID FROM table1")
Debug.Print rs.Fields("NewID")

ADO示例

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set cn = CurrentProject.Connection

cn.Execute ("INSERT INTO table1 (field1, Crdate ) " _
            & "VALUES ( 46, #" & Format(Date, "yyyy/mm/dd") & "#)"), lngRecs
Debug.Print lngRecs
rs.Open "SELECT @@identity AS NewID FROM table1", cn
Debug.Print rs.Fields("NewID")
通过这个，我得到了一个3001错误：“参数类型错误，超出可接受范围或彼此冲突”。我在对象浏览器中也找不到“OpenRecordset”，这可能会有所解释。如果有帮助的话，我已经将ADO 2.6库设置为引用。 - Lunatik
这段代码是用于DAO的，通常最适合用于MS Access。如果你需要的话，我应该能够用ADO编写一些东西。 - Fionnuala
1
请注意，SELECT @@IDENTITY 不需要 FROM 子句。实际上，FROM 子句会被忽略，因为它从执行 INSERT 的连接/数据库对象获取其值，而该对象不是特定于表的。事实上，添加一个 FROM 表意味着你将得到与表中行数相同的行数。我经常使用 db.OpenRecordset("SELECT @@IDENTITY")(0)，因为它返回记录集的 Fields 集合的第一项（DAO 记录集对象的默认集合）。 - David-W-Fenton
3

回复："我已尝试使用以下代码使@@IDENTITY正常工作，但始终返回0。"

你的代码通过不同的连接对象发送SQL和SQL2。我认为除非你从执行INSERT语句的同一连接请求，否则@@identity将不会返回任何值，而不是零。

请尝试更改如下：

myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly

to:

myRecordset.Open SQL2, databaseConnection, adOpenStatic, adLockReadOnly
唉，这就是我从不同项目中重用代码片段的下场！我现在正在处理另一个项目，但是你的建议非常有道理，我会有机会尝试的。 - Lunatik
1
这是我的解决方案，不使用@@index或MAX。
Const connectionString = "Provider=SQLOLEDB; Data Source=SomeSource; Initial Catalog=SomeDB; User Id=YouIDHere; Password=YourPassword"
Const RecordsSQL = "SELECT * FROM ThatOneTable"

Private Sub InsertRecordAndGetID()
    Set connection = New ADODB.connection
    connection.connectionString = connectionString
    connection.Open
    Set recordset = New ADODB.recordset
    recordset.Open SQL, connection, adOpenKeyset, adLockOptimistic

    With recordset
        .AddNew
        !Field1 = Value1
        !Field2 = Value2
    End With

    recordset.MoveLast
    ID = recordset.Fields("id")

End Sub

享受！

Eric，在这种情况下，Access表中的Fields("ID")需要是自动编号，对吗？ - cyberspider789
这取决于你是否设置了自增长 ID 的数据库，但通常我会这样做，在这种情况下是的。 - Eric
0
尝试以下宏代码。首先从控制框中向工作表添加一个命令按钮，然后将以下代码粘贴到代码窗口中。
Private Sub CommandButton1_Click()
    MsgBox GetLastPrimaryKey
End Sub

Private Function GetLastPrimaryKey() As String
Dim con As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myaccess.mdb;Persist Security Info=False"
sql = "SELECT MAX(id) FROM  tblMyTable"

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open con
rs.Open sql, cn, 3, 3, 1
If rs.RecordCount <> 0 Then
   GetLastPrimaryKey = rs.Fields(0).Value
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Function
0
八年晚到派对...你遇到的问题是你正在使用dbConnectionString创建一个新连接。@@identity是特定于您正在使用的连接的。首先，不要关闭原始连接。
'.Close

替换

myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly

使用之前用于插入的连接

myRecordset.Open SQL2, databaseConnection, adOpenStatic, adLockReadOnly

那你就可以搞定了。事实上，你甚至不需要指定表格：

SQL2 = "SELECT @@identity AS NewID"
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接