How to create unique numbers for a primary index field

Problem/Question/Abstract:

Using D4, Paradox 7 and a peer-to-peer network on Win95/ 98, I am currently thinking about the problems of using AutoInc fields as primary indexes to avoid key violations. On balance, I feel that it is probably best to avoid potential problems by choosing an alternative primary index system. But what are the alternatives? Using a DateTime field as the unique primary index or use a number that is incremented in code?

Answer:

If you need a unique number for a primary key create a single-field-single-record table to hold the last used value and call the following function when you need a new number.

function dgGetUniqueNumber(LastNumberTbl: TTable): LongInt;
{Gets the next value from a one field one record table which stores the last used
value in its first field. The parameter LastNumberTbl is the table that contains the last used number.}
const
  ntMaxTries = 100;
var
  I, WaitCount, Tries: Integer;
  RecordLocked: Boolean;
  ErrorMsg: string;
begin
  Result := 0;
  Tries := 0;
  with LastNumberTbl do
  begin
    {Make sure the table contains a record. If not, add one and set the first field to zero.}
    if RecordCount = 0 then
    begin
      Insert;
      Fields[0].AsInteger := 0;
      Post;
    end;
    {Try to put the table that holds the last used number into edit mode. If calling Edit
    raises an exception wait a random period and try again.}
    Randomize;
    while Tries < ntMaxTries do
    try
      Inc(Tries);
      Edit;
      Break;
    except
      on E: EDBEngineError do
        {The call to Edit failed because the record could not be locked.}
      begin
        {See if the lock failed because the record is locked by another user.}
        RecordLocked := False;
        for I := 0 to Pred(E.ErrorCount) do
          if E.Errors[I].ErrorCode = 10241 then
            RecordLocked := True;
        if RecordLocked then
        begin
          {Wait for a random period and try again.}
          WaitCount := Random(20);
          for I := 1 to WaitCount do
            Application.ProcessMessages;
          Continue;
        end
        else
        begin
          {The record lock failed for some reason other than another user has the
          record locked. Display the BDE error stack and exit.}
          ErrorMsg := '';
          for I := 0 to Pred(E.ErrorCount) do
            ErrorMsg := ErrorMsg + E.Errors[I].Message + ' (' + IntToStr(E.Errors[I].ErrorCode) + '). ';
          MessageDlg(ErrorMsg, mtError, [mbOK], 0);
          Exit;
        end;
      end;
    end;
    if State = dsEdit then
    begin
      Result := Fields[0].AsInteger + 1;
      Fields[0].AsInteger := Result;
      Post;
    end
    else
      {If the record could not be locked after the specified number of tries raise an exception.}
      raise Exception.Create('Cannot get next unique number. (dgGetUniqueNumber)');
  end;
end;