I am finding that "SELECT @@IDENTITY;" works about 3 times out of 4.

I have no idea why it decides not to work sometimes. Anyone else found "SELECT @@IDENTITY;" to be unreliable?

My code writes a transaction and then debit and credit postings which need the ID of the transaction ID as an external key.

Code:
strSQLCommand = "INSERT INTO Transactions " & _
    "(" & strSQLFields & ") " & _
    "VALUES(" & strSQLValues & ");"
objCommand.Execute Options:=(adExecuteNoRecords Or adCmdText)
'NOTE: the insert transaction statement never fails

'retrieve the ID of the new Transaction
objCommand.CommandText = "SELECT @@IDENTITY;"
Set rsData = objCommand.Execute(Options:=adCmdText)
intTransID = rsData.Fields("TransID").Value
'SOMETIMES THE ABOVE LINE GETS THE ERROR "no current record"