How to reset a Paradox AutoInc field (Views: 32)
Problem/Question/Abstract: Aside from using the Database Desktop to copy the structure of a Paradox table to a new one, is there a way or a utility to reset a Paradox AutoInc to one (for any empty table) or to the next number after the maximum value for the field? Answer: You would have to restructure the table and change the field type to long integer then restructure the table and change the field type back to autoinc. An alternative is to generate your own autoinc value. Create a single field single record table to hold the last number used then use the following code to get the next value. 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; |