使用@@ Identity [英] Using @@Identity

问题描述

我想知道如何从另一个数据库的表中获取最近生成的自动编号值.目前，我正在这样做:

I'm wondering how I could get the most recently generated autonumber value from a table in another db. Currently I am doing this:

Do Until rsA.EOF
    'Inserts new row here (works)
    Set rs = New ADODB.Recordset 
    rs.Open "SELECT @@Identity" (Connection info)
    SQLcmd = "UPDATE tbl SET col = " & rs("SELECT @@Identity").Value & " 
    (WHERE statement);"
    DoCmd.RunSQL SQLcmd
    rsA.MoveNext
Loop

但是它给col的值为0，而不是新生成的自动编号. 知道为什么吗?还是另一种方式?

But its giving col a value of 0 instead of the newly generated autonumber. Any idea why? Or another way to do this?

推荐答案

您没有显示将INSERT应用于其他数据库的代码.如果您使用ADO Connection对象的Execute方法执行此操作，请从该同一连接对象中运行SELECT @@Identity查询...而不是使用相同连接字符串的新连接. @@Identity仅在同一连接会话中可用；否则您将获得0.

You didn't show the code which does the INSERT into the other database. If you're using the Execute method of an ADO Connection object to do that, run the SELECT @@Identity query from that same connection object ... not a new connection with the same connection string. @@Identity is only usable within the same connection session; otherwise you'll get 0.

实际上，您甚至不需要记录集即可捕获该值.如果您的连接对象名为conn，它将返回一个记录集，但是您不必将其分配给一个记录集对象变量.只需从返回的记录集中索要第一项.

And actually you don't even need a recordset to capture that value. If your connection object is named conn, this will return a recordset, but you need not assign it to a recordset object variable. Simply ask for the first item from the returned recordset.

Debug.Print "most recent autonumber: " & _
    conn.Execute("SELECT @@Identity")(0)

这篇关于使用@@ Identity的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！