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