>>lSuccess = .f.
>>Do While Not lSuccess
>> cSQL = "SELECT LastNumber FROM NumberTable WHERE cKey = 'TABLENAME'"
>>
>> SQLExec(nConn, cSQL)
>>
>> nLastNumber = SQLResult.LastNumber
>> nNewId = nLastNumber + 1
>>
>> cSQL = "UPDATE NumberTable SET LastNumber = nNewID WHERE "+ ;
>> "cKey='TABLENAME' and LastNumber = nLastNumber"
>>
>> lSuccess = ( SQLExec(nConn, cSQL) = 1 )
>>EndDo
>
>
>Thanks Bob, this seems to be a good solution. Not the most elegant way to obtain the goal, but I suppose there is no better way. Wouldn't have found it myself ....
Hi Pascal
Bob's solution does not address multiuser issue. You need to lock NumberTable or at least one row from this table (if it is possible).
I'd create stored procedure for this job.
Something along these lines:
CREATE PROCEDURE GETID @tbl char(10) AS
set nocount on
declare @TempId int
begin tran
select @TempId = (select ID_Value from NumberTable (Tablockx HOLDLOCK) where TABLENAME=@tbl
select @TempId = @TempId + 1
update NumberTable SET ID_Value =@TempId where TABLENAME= @tbl
select ID_Value from NumberTable where TABLENAME= @tbl
commit tran

In Fox code you just ask for next ID:
=SQLExec(nConn, "GETID","curID")
yourId=curID.ID_Value

I locked the table, b/c I am working with 6.5, but in SQL Server 7 should be the way to lock just one row.

HTH
Oleg