ADO 中使用 SELECT @@IDENTITY 取得新增資料自增 ID 的四種寫法

寫法一：分兩次 Execute（所有 ADO 版本）

Dim loConn, lsSQL, loRs
Set loConn = CreateObject("ADODB.Connection")
loConn.Open "Provider=SQLOLEDB;Data Source=...;User ID=sa;Password=;"
lsSQL = "INSERT INTO tMembers (MemberName) VALUES ('Manohar')"
loConn.Execute lsSQL
lsSQL = "SELECT @@IDENTITY AS NewID"
Set loRs = loConn.Execute(lsSQL)
llID = loRs.Fields("NewID").Value
loConn.Close: Set loConn = Nothing

寫法二：批次執行 + NextRecordset（ADO 2.0+）

Dim loConn, lsSQL, loRs
Set loConn = CreateObject("ADODB.Connection")
loConn.Open "..."
lsSQL = "INSERT INTO tMembers (MemberName) VALUES ('Manohar');" & _
        "SELECT @@IDENTITY AS NewID;"
Set loRs = loConn.Execute(lsSQL)
Set loRs = loRs.NextRecordset()  ' 關鍵：移至第二個結果集
llID = loRs.Fields("NewID").Value
loConn.Close: Set loConn = Nothing

寫法三：SET NOCOUNT ON（所有 ADO 版本）

lsSQL = "SET NOCOUNT ON;" & _
        "INSERT INTO tMembers (MemberName) VALUES ('Manohar');" & _
        "SELECT @@IDENTITY AS NewID;"
Set loRs = loConn.Execute(lsSQL)
llID = loRs.Fields("NewID").Value  ' 直接取得，無需 NextRecordset
loConn.Close: Set loConn = Nothing

SET NOCOUNT ON 會抑制不回傳結果集的陳述式（INSERT/UPDATE/DELETE）產生的「影響列數」訊息，使唯一的 SELECT 成為第一個結果集。

寫法四：一次新增多筆並取得各自 ID

lsSQL = "SET NOCOUNT ON;" & _
        "INSERT INTO tMembers (MemberName) VALUES ('John');" & _
        "SELECT @@IDENTITY AS NewID;" & _
        "INSERT INTO tMembers (MemberName) VALUES ('Jane');" & _
        "SELECT @@IDENTITY AS NewID;"
Set loRs = loConn.Execute(lsSQL)
llID1 = loRs.Fields("NewID").Value
Set loRs = loRs.NextRecordset()
llID2 = loRs.Fields("NewID").Value