Greetings

I am creating a new record in an Auto Increment sql table (by copying an existing record) and I want to retrieve the new id created.
So far I have got the following, which creates the record nicely, but I dont know how to retrieve the New id

Private Sub CopyRecord(OriginalTable As String, ByRef ID As Integer)
myconnection.Execute("SELECT * INTO TempTable FROM " & OriginalTable & " WHERE ID = " & ID)
myconnection.Execute("ALTER TABLE TempTable DROP COLUMN ID")
myconnection.Execute("INSERT INTO " & OriginalTable & " OUTPUT INSERTED.ID as ID SELECT * FROM TempTable")
myconnection.Execute("DROP TABLE TempTable")
End Sub

How can I get the subroutine to return the new Id in the subroutines ID parameter
Thank you for your help

(Note: myconnection is an ADODB.Connection and has been established earlier by the following
ConnectionString = "Driver={SQL Server};Server=" & strSQLPath & ";Database=TESTING;Trusted_Connection=yes"
myconnection.Open(ConnectionString)