Excel VBA和ADODB:从VBA中检索插入SQL Server的行的自动标识

Question

在Excel的VBA模块中。使用ADODB.Connection和此连接字符串：

"Provider=SQLOLEDB;Data Source=MARS;Initial Catalog=automation;Trusted_connection=yes;"

我想：

1. 插入测试（数据）值（"测量"）
2. 检索新插入行的自动递增标识（test.data_id）

Answer 1

Dim identity as integer
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.ConnectionString = "whatever..."
cn.Open
cn.Execute ("INSERT INTO test(data) VALUES ('hello world')")
rs.Open "SELECT @@identity AS NewID", cn
identity = rs.Fields("NewID") 
MsgBox (identity)