MS Access Database table lock

[vincentw56]

I have Delphi 7 and using ADO. What I am trying to do is use a invoice number table that stores the invoice number. When a user saves an invoice, the program opens the invoice number table, grabs the invoice number, increments it, and then saves it back. That part is not too hard, the problem is that I want the table to be opened exclusivly so no one else can get the same invoice number. This is what I am having a problem with. Any help would be appreciated. Thanks.

Vincent

 

[Zathras]

You don't really need exclusive access:

Include the old invoice number in the WHERE clause when doing the UPDATE. Then check the RowsAffected property. If 0, then someone else beat you to to it, so start over. Keep trying until you get the RowsAffected property > 0. Keep a loop counter and after 20 or so loops, raise an exception to indicate failure.

 

[vincentw56]

I understand what you are saying, I am just having a problem implementing it. Would you happen to have some sample code? I appreciate your help on this. Thanks.

Vincent

 

[Zathras]

Ok, here it is for what it's worth. It's a little more complicated than you need. It works on a 2-column table where the "COLNAME" column identifies a particular sequence of next numbers, allowing for multiple sequences. For example one entry in the table might call for WHERE COLNAME = 'INVOICENUM' to access the sequence of inventory numbers.

{Fetch a string from a database table.
If more than one column listed (CSV) results are tab-delimited.}
function SimpleSelect(Query:TADOQuery; SqlStatement: string): string;
var
  i: integer;
begin
  try
    with Query do  begin
      SQL.Clear;
      SQL.Add(SqlStatement);
      Open;
      if Eof then Result := ''
      else begin
        Result := Fields[0].AsString;
        if FieldCount > 1 then
          for i := 2 to FieldCount do Result := Result+#9+Fields[i-1].AsString;
      end; {if}
      Close;
    end; {with}
  except
    Result := '*Error';
  end; {try}
end;


{Simple way to update a table given a TADOQuery and a SQL statement.}
function SimpleUpdate( Query:TADOQuery; SqlStatement:string ):integer;
begin
  Result := 0;
  with Query as TADOQuery do
    try
      SQL.Add(SqlStatement);
      ExecSQL;
      Result := RowsAffected;
    except
      on E: Exception do ShowMessage( E.Message );
    end;
end;


{Returns a number representing the "next number" for a given series.}
function NextNumber( AQuery:TADOQuery; Name:string; Quantity:integer = 1 ): integer;
var
  bTrying:boolean;
  sNextNumber:string;
  nNextNumber:integer;
  nNextNextNumber:integer;
  sSqlStatement:string;
  nLoops,nUpdated:integer;
begin
  Result := 0;
  nLoops := 0;
  Assert( (Quantity > 0), 'NextNumber called with zero or negative quantity.');
  bTrying := True;
  while bTrying do
    begin
      sNextNumber := SimpleSelect( AQuery,
          'SELECT NEXTNUMBER FROM NEXTNUMBERS WHERE COLNAME='+QuotedStr(Name));
      if sNextNumber = '' then Exit;  // Failure
      nNextNumber := StrToInt( sNextNumber );
      nNextNextNumber := nNextNumber + Quantity;
      sSqlStatement := 'UPDATE NEXTNUMBERS SET NEXTNUMBER='
           + IntToStr(nNextNextNumber) + ' WHERE COLNAME='+QuotedStr(Name)
           + ' AND NEXTNUMBER=' + sNextNumber;
      nUpdated := SimpleUpdate( sSqlStatement );
      if nUpdated = 1 then
        begin
          bTrying := False;
          Result := nNextNumber;
        end;
      Inc(nLoops);
      if nLoops > 20 then bTrying := False; // Failure
    end;
end;

 

[Zathras]

It just occurred to me. MS Access may not support the RowsAffected property. I don't know. If not then more drastic measures are needed.

I would first post on one of the Access forums here to see whether there is a way to get an exclusive lock on a table. If there is, you can use that method.

If there is not, then I would probably put extra columns on the table to contain a timestamp and a unique user ID. Then you would get a pseudo lock on the table by putting your ID and current timestamp in the record, clearing them after successful update. To handle the situation where a user "locks" the record then exits the program before clearing the lock, check the timestamp and ignore it if it more than a few minutes old. Tricky business, but that's what programming is all about.

Please let me know whether or not Access supports the RowsAffected property. I would like to file that bit of info away for future reference.

 

[awarnica]

I had the exact same issue but with a SQL server database.

In my case people could be inserting 10 records a second so checking the database is not the best idea. I created a compound SQL statement that selects / inserts all in one, no locking required. Im only guessing it would work with access too, give something like this a shot:

lstrSQL := 'SELECT [LastEntryNo] as dLastNo ' +
'FROM [SetupTable] ' +
'UPDATE [SetupTable] ' +
'SET [LastEntryNo] = ' +
'(SELECT [LastEntryNo] ' +
'FROM [SetupTable]) + 1';

if lodbcStmt.ExecDirect(lstrSQL) then
begin
if lodbcStmt.Fetch then
begin
ldTemp := lodbcStmt.GetDataAsDouble('dLastNo');
...

SetupTable.LastEntryNo is an integer

I am using a custom ODBC unit, but basically that is it. The SQL statement sets the number to number + 1, and the SQL statement also returns the number.

Any other way of checking to see if the table has been updated isn't really the best way to do it if you have lots of database hits.

Hope this helps!

-Andrew