如何使用Excel VBA获取新插入记录的ID？ [英] How to get id of newly inserted record using Excel VBA?

问题描述

看起来是一个很普遍的问题，但大多数解决方案都是指连接多个SQL命令，我相信无法用ADO / VBA完成这个任务，但是我很乐意在这方面显示错误。 / p>

我现在插入我的新记录，然后使用（我希望）足够的字段运行选择查询，以保证只能返回新插入的记录。我的数据库很少被一个人一次访问（在查询之间发生另一个插入的可能性很小），并且由于表的结构，识别新记录通常很容易。

我现在正在尝试更新一个没有太多的唯一性范围的表，而不是在人工主键中。这意味着新的记录可能不是唯一的，我不愿意添加一个字段来强制唯一。

最好的插入方式是什么在这种情况下，Access表中的记录会从Excel查询新的主键？

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

  Private Sub getIdentityTest（）
 Dim myRecordset As New ADODB.Recordset 
 Dim SQL As String，SQL2 As String 
 
 SQL =INSERT INTO tblTask​​s纪律，任务，所有者，单位，分钟）VALUES（testDisc3-3testTasktestOwnertestUnit，1）; 
 SQL2 =SELECT @@ identity AS NewID FROM tblTask​​s; 
 
如果databaseConnection是Nothing然后
 createDBConnection 
结束如果
 
与databaseConnection 
 .Open dbConnectionString 
 .Execute（SQL） 
 .Close 
结束
 
 myRecordset.Open SQL2，dbConnectionString，adOpenStatic，adLockReadOnly 
 
 Debug.Print myRecordset.Fields（NewID）
 
 myRecordset.Close 
 
设置myRecordset = Nothing 
 End Sub 
  

任何事情脱颖而出都是负责任的。

然而，由于Renaud（下图）有助于提供的警告，使用 @@ IDENTITY 与任何其他方法一样，所以我现在使用了 SELECT MAX 。为了将来参考，虽然我有兴趣看看上面的尝试有什么问题。

解决方案

关于你的问题：

我正在尝试更新一个表，
对于
的唯一性没有太大的作用，除了在
人工主键。这意味着
存在新记录
可能不是唯一的风险，我不喜欢
添加一个字段来强制唯一。

如果您使用为您的主键使用自动增量功能，那么您具有唯一性，您可以使用 SELECT @@ Identity; 以获取上一个自动生成ID的值（请参阅下面的注意事项）

如果您不是 / strong>使用自动增量，并且您正在从Access中插入记录，但是要从Excel中检索最后一个记录：

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

    SELECT MAX MyPrimaryField）FROM MyTable; 
   SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY MyPrimaryField DESC; 
    




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

    SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY InsertedDate DESC; 
    

在这两种情况下，我认为你会发现添加一个AutoIncrement主键更容易处理：

• 这不会花费你太多p>




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




• 使您可以使用 @@ 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& ]; 
  设置rs =新建ADODB.Recordset 
   rs.Open sql，con，adOpenStatic，adLockReadOnly 
   GetLastPrimaryKey = rs.Fields（0）.Value 
   rs.Close 
  设置rs = Nothing 
   End Sub 
    

请注意 @@身份

您必须是注意使用 @@ Identity 在标准Access数据库（*）中：

• 它只适用于AutoIncrement Identity字段。 >




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




• 它返回最新使用的计数器，但这是对于所有表 。您不能使用它返回MS Access中的特定表的计数器（据我所知，如果使用 FROM mytable 指定表，则只会被忽略）
  
  简而言之，返回的值可能根本不符合您的预期。




• 您必须直接在 INSERT 以尽量减少错误答案的风险。
  
  这意味着如果您一次插入数据并需要获取最后一个ID在另一个时间（或另一个地方），它将无法工作。




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

（*）：只是为了清楚， @@ IDENTITY 的行为不同，更具预测性的方法，如果您对数据库使用ANSI-92 SQL模式。

问题是ANSI 92有一个稍微差异租用语法比
Access 89支持的ANSI 89语言，这意味着当Access用作前端时，可以提高与SQL Server的兼容性。

Seems a common enough problem this, but most solutions refer to concatenating multiple SQL commands, something which I believe can't be done with ADO/VBA (I'll be glad to be shown wrong in this regard however).

I currently insert my new record then run a select query using (I hope) enough fields to guarantee that only the newly inserted record can be returned. My databases are rarely accessed by more than one person at a time (negligible risk of another insert happening between queries) and due to the structure of the tables, identifying the new record is normally pretty easy.

I'm now trying to update a table that does not have much scope for uniqueness, other than in the artificial primary key. This means there is a risk that the new record may not be unique, and I'm loathe to add a field just to force uniqueness.

What's the best way to insert a record into an Access table then query the new primary key from Excel in this situation?

Thanks for the replies. I have tried to get @@IDENTITY working, but this always returns 0 using the code below.

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

Anything stand out being responsible?

However, given the caveats helpfully supplied by Renaud (below) there seems nearly as much risk with using @@IDENTITY as with any other method, so I've resorted to using SELECT MAX for now. For future reference though I would be interested to see what is wrong with my attempt above.

解决方案

About your question:

I'm now trying to update a table that does not have much scope for uniqueness, other than in the artificial primary key. This means there is a risk that the new record may not be unique, and I'm loathe to add a field just to force uniqueness.

If you are using an AutoIncrement for your primary key, then you have uniqueness and you could use SELECT @@Identity; to get the value of the last autogenerated ID (see caveats below).

If you are not using autoincrement, and you are inserting the records from Access but you want to retrieve the last one from 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;
  

In either of these cases, I think you would find adding an AutoIncrement primary key as being a lot easier to deal with:

• It's not going to cost you much

• It's going to guarantee you uniqueness of your records without having to think about it

• It's going to make it easier for you to pick the most recent record, either using @@Identity or through sorting by the primary key or getting the Max().

From Excel

To get the data into Excel, you have a couple of choices:

• 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
  

Note about @@Identity

You have to be careful of the caveats when using @@Identity in standard Access databases(*):

• It only works with AutoIncrement Identity fields.

• It's only available if you use ADO and run SELECT @@IDENTITY;

• It returns the latest used counter, but that's for all tables. You can't use it to return the counter for a specific table in MS Access (as far as I know, if you specify a table using FROM mytable, it just gets ignored).
  In short, the value returned may not be at all the one you expect.

• You must query it straight after an INSERT to minimize the risk of getting a wrong answer.
  That means that if you are inserting your data at one time and need to get the last ID at another time (or another place), it won't work.

• Last but not least, the variable is set only when records are inserted through programming code.
  This means that is the record was added through the user interface, @@IDENTITY will not be set.

(*): just to be clear, @@IDENTITY behaves differently, and in a more predictive way, if you use ANSI-92 SQL mode for your database.
The issue though is that ANSI 92 has a slightly different syntax than the ANSI 89 flavour supported by Access and is meant to increase compatibility with SQL Server when Access is used as a front end.

这篇关于如何使用Excel VBA获取新插入记录的ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！