Hi!

I know ADO have built-in ability to get the last ID value of the inserted record. When field is IDENTITY field on the SQl Server, the field's property 'IDENTITY' is .T. When you issue Recordset.Resynch just after the new record inserting, ADO gets the value of that field automatically, as far as I know. However, it is not reliable. You can do manual query t the server: "SELECT @@IDENTITY". However, this is not reliable approach because insert trigger can insert records into other tables that will spoil the @@IDENTITY SQL Server session variable value (Last INSERT command identity value). Thus this approach is good only when you have no such Insert triggers.

GUID is good, but it have 32 bytes length compare to the integer (4 bytes). For large tables this is significant slow down and extra data. When you want to use it, call SQL Server using "SELECT NewID()" command before record update, out new ID to the ID field than update (note that ID key in view should be updatable for such case). Other way, when you use default value of the ID KEY field, you will not be able to reliably get the ID of the new record at the client side after insert otehr way than complete requery of the view (and thus losing the record pointer).

You can also make your own custom ID generator. Make a table with only identity integer field. Before record inserting insert record into that table and get a value of the @@IDENTITY variable. Than use that variable as a new ID. This way ID will be unique fro all users and all tables. However, this requires to clean up the ID generatign table periodically.

However, all above will be, anyway, an extra call of the SQL Server, otherwise you cannot be sure the new ID is unique in case when 2 concurrent users try to insert the new records into the same table.

HTH.

>Hi, everybody!
>
>What is best way to retrieve NewID on a SQL Table:
> * Identity Fields?
> * A Meta-Table with the last ID generated for each one of my tables?
> * A SELECT MAX(ID) Statement?
>
> Or there's some other?
>
> I'm developing a N-Tier application and I'm using ADO RecordSets to pass data through layers...
>
> Any information about this issue is of extreme importance for me.
>
> Thanks in Advance!