Hi Ken,

One way is to have open a connection through SPT to the back-end data and requery the new record on that second connection via an alternate, unique key. It's a PITA, though. Personally, I've all but abandoned remote views where Identities are involved.


>Hey now,
>
>We're planning a conversion from Fox tables to SQL Server 7.0 and are thinking about using remote views to prevent a total rewrite of the application. But I found a gotcha and would appreciate any input. Here's the deal:
>
>When you insert into a SQL table using ADO you can simply ignore the identity column, it then increments for the new row and you can you can the SELECT back the new records identity field's value:
>
>myTableStructure
>
>TableKey, Integer, Identity
>myField1, whatever
>myFieldx, whatever
>
>So then the ADO command is:
>
>sSQL = "INSERT mytable VALUES (myfield1, myfieldx) ; SELECT @@Identity FROM mytable AS NewID"
>myRS.Open sSQL, myConnection
>iNewID = oRS("NewID")
>
>iNewID from the SELECT has the table's identity value. But playing with a remote view the only way I have been able to get back the value of the Identity field is to close the table and open it up again, or REQUERY it...both of which are of course unacceptable. Despite a TableUpdate, the Identity column stays at 0 in the remote view.
>
>Anybody figure this one out?
>
>TIA,
>Ken