John, Ken,

I'm a little puzzled here. Let me explain and perhaps something will fall out of the tree.

I have remote views (by this I'm presuming you mean a remote view in a DBC) of SQL tables and am able to retrieve the Identity column.

What I've done is this: I created a COM object that receives a Logon/Logoff request with some basic data like workstation GUID, SYS(0), etc.. It then creates an ADODB connection to the SQL connection and, based on the connection string passed in, connects to either the TEST or LIVE data. Once the connection is established it then calles a SQL Stored Procedure that returns the Identity value as an @OUTPUT. That value for the new record is returned to the MTS COM object which in turn passes the Identity value back to the calling program.

Works great!

To get more than one I'd just do my inserts and then REQUERY() the remote view.

Best,

DD

>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