Matthew...

SQL-Server's identity field capabilities work fine. What you are proposing here is to go outside the SQL-Server realm for PK's. In most cases, this is simply not allowed, and is generally not a good idea. Mostly because you don't need to. For Fox data...sure. For SQL data, not acceptable at all... And, if you have a DBA on board, no way he/she would let something like this go through, at least, if they knew what they were doing....

>Ken,
>
>Not what you want to hear, but I use an id table which has a one record for each table in my system. The key field in each table then has a stored procedure for the default value. When a new record is added, the stored procedure will get the next key value and increment the id table. This is based directly on the original codebook and works great. If you are interested, I could send you the id table and the two stored procedures.
>
>Kind Regards,
>
>Mat
>
>
>>Ok,
>>
>>I'm in a quandry here - someone please help me out!
>>I have an updatable cursor created from a SQL stored proc and all the appropriate SQLSetProp() stuff to make it happy. I use integer surrogate keys, and the key is set up as an identity field on the back end.
>>
>>On regular (existing record) updates - all works as expected. On inserts - here is the problem - and I can't find the way around it though I'm sure there must be one.
>>
>>When I insert into the cursor, the key "automatically" gets a value of 0 (zero) since the field is non-null. (The key field is NOT set up as updatable, of course.) When I fill in all the blanks and issue TableUpdate(), the record is correctly inserted on the back end with the correct @@identity number. Great. Now I can retrieve the identity number. Great there too. HOWEVER - here is the problem.
>>
>>If I now (after the initial tableupdate to do the insert) change a field and try to update again, it fails because the code generated by TableUpdate() still looks for "where keyfield = 0" I can see this in SQL Profiler. Now - even IF I REPLACE the keyfield in the cursor with the correct identity#, tableupdate STILL uses "where keyfield = 0" in its update statement!! Refresh() doesn't have any effect either.
>>
>>So - how the heck do I do this? Specifically - how can I "force" tableupdate to use the current value in my key field in its where statement? Please don't tell me I need to write my own "tableupdate". I thought @@identity was the answer to getting around having to have a "keys table", but I'm beginning to wonder.
>>
>>Any help is GREATLY appreciated.