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

Question

提问by Lunatik

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).

这似乎是一个很常见的问题，但大多数解决方案都是指连接多个 SQL 命令，我认为 ADO/VBA 无法做到这一点（但是，我很高兴在这方面显示错误）。

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?

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

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

感谢您的回复。我试图开始@@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

Anything stand out being responsible?

有什么突出的负责吗？

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

然而，考虑到 Renaud（如下）提供的有用警告，使用的风险似乎@@IDENTITY与任何其他方法一样大，所以我SELECT MAX现在使用了。以供将来参考，尽管我很想知道我上面的尝试有什么问题。

Answer 1

回答by Renaud Bompuis

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 areusing 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).

如果您正在使用您的主键自动增量，那么你有独特性，你可以使用SELECT @@Identity;得到最后自动生成的ID的值（见下面的注意事项）。

If you are notusing autoincrement, and you are inserting the records from Access but you want to retrieve the last one from Excel:

如果您没有使用自动增量，并且您正在从 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;
```

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

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;
```

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

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

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 @@Identityor through sorting by the primary key or getting the Max().

它不会花费你太多
它将保证您记录的唯一性，而无需考虑它
这将使您更容易选择最新的记录，无论是使用@@Identity主键排序还是通过主键排序或获取Max().

From Excel

来自 Excel

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

要将数据导入 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

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

从 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

注意事项 @@Identity

You have to be careful of the caveatswhen using @@Identityin standard Access databases(*):

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

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 INSERTto 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, @@IDENTITYwill not be set.

它仅适用于 AutoIncrement Identity 字段。
仅当您使用 ADO 并运行时才可用 SELECT @@IDENTITY;
它返回最近使用过的计数器，但这适用于所有表。你不能用它来返回 MS Access 中特定表的计数器（据我所知，如果你使用指定一个表FROM mytable，它只会被忽略）。
简而言之，返回的值可能根本不是您期望的值。
您必须在 a 之后直接查询，INSERT以尽量减少得到错误答案的风险。
这意味着如果您一次插入数据并需要在另一时间（或其他地方）获取最后一个 ID，它将不起作用。
最后但并非最不重要的一点是，只有在通过编程代码插入记录时才会设置该变量。
这意味着记录是通过用户界面添加的，@@IDENTITY不会被设置。

(*): just to be clear, @@IDENTITYbehaves 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.

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

Answer 2

回答by Fionnuala

If the artificial key is an autonumber, you can use @@identity.

如果人工键是自动编号，则可以使用@@identity。

Note that with both these examples, the transaction is isolated from other events, so the identity returned is the one just inserted. You can test this by pausing the code at Debug.Print db.RecordsAffected or Debug.Print lngRecs and inserting a record manually into Table1, continue the code and note that the identity returned is not that of the record inserted manually, but of the previous record inserted by code.

请注意，在这两个示例中，事务与其他事件是隔离的，因此返回的身份是刚刚插入的身份。您可以通过暂停 Debug.Print db.RecordsAffected 或 Debug.Print lngRecs 处的代码并手动将记录插入到 Table1 中来测试这一点，继续代码并注意返回的标识不是手动插入的记录的标识，而是前一个代码插入的记录。

DAO Example

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 Example

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")

Answer 3

回答by HansUp

Re: "I have tried to get @@IDENTITY working, but this always returns 0 using the code below."

回复：“我试图让 @@IDENTITY 工作，但使用下面的代码总是返回 0。”

Your code sends SQLand SQL2through different connection objects. I don't think @@identitywill return anything other than zero unless you ask from the same connection where you executed your INSERTstatement.

您的代码通过不同的连接对象发送SQL和发送SQL2。@@identity除非您从执行INSERT语句的同一连接中询问，否则我认为不会返回零以外的任何内容。

Try changing this:

尝试改变这个：

myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly

to:

到：

myRecordset.Open SQL2, databaseConnection, adOpenStatic, adLockReadOnly

Answer 4

回答by Eric

Here's my solution that does not use @@index or MAX.

这是我不使用@@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

Enjoy!

享受！

Answer 5

回答by David Pedack

8 years late to the party... The problem you are having is that you are using dbConnectionString to create a newconnection. @@identity is specific to the connection you are using.

聚会晚了 8 年...您遇到的问题是您正在使用 dbConnectionString 创建新连接。@@identity 特定于您正在使用的连接。

First, don't close the original connection

一、不要关闭原来的连接

'.Close

replace

代替

myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly

with the connection you previously used for the insert

使用您之前用于插入的连接

myRecordset.Open SQL2, databaseConnection, adOpenStatic, adLockReadOnly

and you'd have been all set. In fact, you don't even need to specify the table:

你已经准备好了。实际上，您甚至不需要指定表：

SQL2 = "SELECT @@identity AS NewID"

Answer 6

回答by David Pedack

Try following macro code.First add a command button to the sheet from the control box and paste following codes in the code window

尝试以下宏代码。首先从控制框向工作表添加一个命令按钮，然后在代码窗口中粘贴以下代码

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

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

提问by Lunatik

回答by Renaud Bompuis

回答by Fionnuala

回答by HansUp

回答by Eric

回答by David Pedack

回答by David Pedack

相关推荐

最近更新

标签

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

提问by Lunatik

回答by Renaud Bompuis

回答by Fionnuala

回答by HansUp

回答by Eric

回答by David Pedack

回答by David Pedack

相关推荐

如何从子查询（在 SQL Server 中）中选择多个列，主查询中的每条记录都应该有一个记录（选择前 1 个）？

SQL Server 2008 中超前滞后函数的替代方法

SQL 如何在单个 SELECT 语句中拥有多个公用表表达式？

SQL 错误：ORA-00904:: 标识符无效

相关推荐

最近更新

标签