Dim Conn, strSQL, Rs
Set Conn = CreateObject("ADODB.Connection")
' Open a connection to the database
Conn.Open("DSN=myDSN;UID=myUID;PWD=myPWD;")
' Insert a new record into the table
strSQL = "INSERT INTO mtTable (columnName) VALUES ('something')"
' Execute the SQL statement
Conn.Execute(strSQL)
' Get the @@IDENTITY.
strSQL = "SELECT @@IDENTITY AS NewID"
Set Rs = Conn.Execute(lsSQL)
NewID = Rs.Fields("NewID").value
' Close the connection
Conn.Close()
Set Conn = Nothing
方法二(仅限于 ADO 2.0 以上):
Dim Conn, strSQL, Rs
Set Conn = CreateObject("ADODB.Connection")
' Open a connection to the database
Conn.Open("DSN=myDSN;UID=myUID;PWD=myPWD;")
' Insert a new record into the table
lsSQL = "INSERT INTO myTable (columnName) VALUES ('something');" &_
"SELECT @@IDENTITY AS NewID;"
' Execute the SQL statement
Set Rs = Conn.Execute(lsSQL)
' Get the second resultset into a RecordSet object
Set Rs = Rs.NextRecordSet()
' Get the inserted ID
NewID = Rs.Fields("NewID").value
' Close the connection
Conn.Close()
Set Conn = Nothing
方法三:
Dim Conn, strSQL, Rs
Set Conn = CreateObject("ADODB.Connection")
' Open a connection to the database
Conn.Open("DSN=myDSN;UID=myUID;PWD=myPWD;")
' Insert a new record into the table
strSQL = "SET NOCOUNT ON;" &_
"INSERT INTO myTable (columnName) VALUES ('something');" &_
"SELECT @@IDENTITY AS NewID;"
' Execute the SQL statement
Set Rs = Conn.Execute(lsSQL)
' Get the inserted ID
NewID = Rs.Fields("NewID").value
' Close the connection
Conn.Close()
Set Conn = Nothing