Sounded like Ken was using an ODBC or OLE DB data link.


>
>Sure, but since I'm the guy here who makes those decisions all I want to know is when, *g*
>
>One thing I forgot tomention was that my Logoff routine received the Identity value that was created when the user logged in as the record to update when the user logos out. In this scenario it works really well. Crashes = abandoned 'IN' records that can easily get flagged and used for troubleshooting and/or statistical studies.
>
>Were these remote views server side of DBC-based?
>
>Best,
>
>DD
>
>>Doug,
>>
>>I mentioned one way to do it. You've got another that looks great. I think, though, that one of the attractions of remote views is the simplicity and that is lost when you have to get fancy to retrieve a new identity value. Also, your method assumes that you can create a stored proc on the server end: In some environments, that woule require a change order or request to a departmental or corporate DBA and that could be a bitch to get done.
>>
>>
>>>
>>>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