Retrieving Identities from ADO Recordsets

[deejay]

Hi
we have the following problem when inserting new rows in our SQL Server 7.0 database using ADO recordsets.
There is a requirement on the system to audit all activity on all fields in all tables. We've handled this by using triggers to insert copies of the inserted/updated rows into a seperate shadow table. The problem is that within VB, the ADO recordset picks up the unique ID of the shadow table insert rather than that of the original table on which the insert was done. This is because the @@identity, which sql server returns, holds only the id of the last inserted record.
We've tried removing the identity property from the both tables but we just end up with a zero value in the id of the recordset even though the db record has a valid ID.
Any ideas at all?????
Many thanks

[Clunietp]

you could possibly use a stored procedure instead of a trigger. Insert the data into the new table, return the identity, then insert the data into the shadow table

[deejay]

Thanks for the reply, but due to time constraints rewriting the triggers wasn't an option. We managed to get around this problem by calling a requery on the recordset after the initial batch update was called. This refresh from the database provided us with the correct ids.
Thanks again!