sql – 如何使用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.为了将来参考,虽然我有兴趣看看我上面的尝试有什么问题.

解决方法

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.

如果您使用AutoIncrement作为主键,那么您具有唯一性,您可以使用SELECT @@ Identity;获取最后一个自动生成ID的值(请参阅下面的警告).

如果您没有使用自动增量,并且您要从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主键更容易处理：

>这不会让你付出太多代价
>这将保证您的记录的独特性,而无需考虑它
>这将使您更容易选择最新的记录,使用@@ Identity或通过主键排序或获取Max().

来自Excel

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

>使用查询创建数据链接,以便直接在Cell或范围中使用结果.
>来自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,adLockReadOnly
    GetLastPrimaryKey = rs.Fields(0).Value
    rs.Close
    Set rs = Nothing
End Sub

关于@@ Identity的注意事项

在标准Access数据库(*)中使用@@ Identity时,您必须是careful of the caveats：

>它仅适用于AutoIncrement Identity字段.
>它仅在您使用ADO并运行SELECT @@ IDENTITY时才可用;
>它返回最新使用的计数器,but that’s for all tables.你不能用它来返回MS Access中特定表的计数器(据我所知,如果你使用FROM mytable指定一个表,它就会被忽略).
简而言之,返回的值可能与您期望的值完全不同.
>您必须在INSERT之后直接查询它,以最大程度地降低获得错误答案的风险.
这意味着,如果您一次插入数据并需要在另一个时间(或其他地方)获取最后一个ID,则无法使用.
>最后但并非最不重要的是,仅当通过编程代码插入记录时才设置变量.
这意味着通过用户界面添加了记录,将不会设置@@ IDENTITY.

(*)：为了清楚起见,@@ IDENTITY表现不同,并且以更具预测性的方式,如果您对数据库使用ANSI-92 SQL模式.但问题是ANSI 92的语法略有不同Access支持的ANSI 89风格,旨在当Access用作前端时增加与SQL Server的兼容性.

（编辑：云计算网_宿迁站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!