well, i think i have find what´s happening.
This dummy example to execute a commit for each 50 records:
var x:Integer;
sqlquery1.PacketRecords:=100;
sqlquery1.SQL.Text:='Select * from example1';
sqlquery1.Open;
if sqlquery1.IsEmpty = False then
begin
x:=0;
while not sqlquery1.EOF do
begin
sqlqlery1.Edit;
sqlquery1.Fieldbyname('Field5').AsInteger:=0;
sqlquery1.Post;
sqlquery1.ApplyUpdates;
if x=50 then
begin
SQLTransaction1.CommitRetaining;
x:=0;
end;
x:=x+1;
sqlquery1.Next;
end;
end;
sqlquery1.Close;
works fine in Firebird, but not at all in Access.
The problem in Access is the instruction Trans.CommitRetaining. After it, the Dataset remains active, but not at all... To explain it anyway, the dataset keeps not "alive", it´s just a "photo". For this reason when trying to read the record number (PacketRecord + 1) generates a SQL sequence error. The only solution I know to resolve it is assigning the value -1 to PacketRecords, this way all records are buffered into memory.
I suppose, not sure at all, the problem is the lack of RETAIN parameter in the COMMIT instruction of access SQL.
Regards,