ADO query causing mdb database to exceed 2GB limit

Question

I'm using the Delphi code below to replace a table inside an Access database with a big csv file in utf=8 format. The DB is 946MB before I start. Everything is OK until my table reaches a certain size then I get an error. When this happens, the database has exceeded 2GB. I'm guessing maybe the Jet engine is creating an internal copy which is causing the overrun - is there any way of stopping this, or maybe someone could suggest a different method of importing my data which avoids it?

function TCSVDatabase.ADOFromCSV(ConStr: string): Boolean;
var
  J, K: Integer;
  S: string;
  SN, DN: string;
  DefDir: string;
  DestDir: string;
  TN: TStringList;
begin
  BeginUpdate;
  Result := False;
  DoProgress(0, 'Initializing...');
  DN := ADOSetConnectionString(ConStr);
  if FileExists(DN) then
  begin
    DestDir := ExtractFilePath(DN);
    TN := TStringList.Create;
    Result := True;
    try
      DefDir := FDefaultPath;
      FDefaultPath := DestDir;
      TN.Assign(ADOGetTableNames);
      for K := 0 to TN.Count - 1 do
      begin
        SN := DestDir + TN[K] + '.csv';
        if FileExists(SN) then
        begin
          DoProgress(0, 'Opening "' + TN[K] + '"...');
          FADOTable.Close;
          while FADOTable.Active do;
          FADOTable.Connection := FADOConnection;
          FADOTable.TableName := TN[K];
          FADOTable.Open;
          if FADOTable.Active then
          begin
            Result := True;
            ADOGetFieldNames;
            FADOQuery.Connection := FADOConnection;
            FADOQuery.Recordset := FADOTable.Recordset;
            FADOQuery.Open;
            DoProgress(0, 'Emptying "' + TN[K] + '"...');
            FADOQuery.SQL.Text := 'DELETE * FROM [' + TN[K] + ']';
            FADOQuery.ExecSQL;
            S := ExtractFilePath(SN);
            SetLength(S, Length(S) - 1);
            try
              DoProgress(0, 'Filling "' + TN[K] + '"...');
              FADOQuery.SQL.Text := 'INSERT INTO [' + TN[K] +
                '] SELECT * FROM [' + ExtractFileName(SN) + '] IN "' + S +
                '" "Text;HDR=YES;FMT=Delimited(,);CharacterSet=65001;"';
              FADOQuery.ExecSQL;
            except
              FADOQuery.Connection := nil;
              FADOQuery.Close;
              DoProgress(0, 'SQL error in "' + TN[K] + '"');
{$IFDEF VCL}
              ShowMessage('SQL error in "' + TN[K]);
{$ENDIF}
              Result := False;
              Exit;
            end;
            FADOQuery.Connection := nil;
            FADOQuery.Close;
            for J := 0 to FTemp.Count - 1 do
            begin
              if (FTemp.Codes[J] in [FT_MEMO, FT_WIDEMEMO]) then
              begin
                FADOTable.First;
                while (not FADOTable.EOF) do
                begin
                  S := FADOTable.Fields[J].AsString;
                  if (Pos('_\', S) <> 0) then
                  begin
                    S := StringReplace(S, '_\r\n_', #13#10, [rfReplaceAll]);
                    S := StringReplace(S, '_\r_', #13, [rfReplaceAll]);
                    S := StringReplace(S, '_\n_', #10, [rfReplaceAll]);
                    FADOTable.Edit;
                    FADOTable.Fields[J].AsString := S;
                    FADOTable.Post;
                  end;
                  FADOTable.Next;
                end;
              end;
            end;
          end;
        end;
        DoProgress(0, '');
      end;
    finally
      FADOTable.Close;
      if Result then
      begin
        DoProgress(0, 'Compacting...');
        CompactDatabase(DN, FADOConnection);
      end;
      FDefaultPath := DefDir;
      TN.Free;
    end;
  end;
  DoProgress(0, '');
  EndUpdate;
end;

Answer 1

The solution for me is to clear the tables and refill them in two separate passes, with a CompactDatabase in between. Thanks for your suggestions everyone and I hope my solution helps someone else :-)

PS Thanks Sam I just saw your answer after testing my fix and it'a basically what you suggested!

function TCSVDatabase.ADOFromCSV(ConStr: string): Boolean;
var
  Err: Cardinal;
  J, K: Integer;
  S: string;
  SN, DN: string;
  DefDir: string;
  DestDir: string;
  TN: TStringList;
begin
  BeginUpdate;
  Result := False;
  DoProgress(0, 'Initializing...');
  DN := ADOSetConnectionString(ConStr);
  if FileExists(DN) then
  begin
    DestDir := ExtractFilePath(DN);
    TN := TStringList.Create;
    Result := True;
    try
      DefDir := FDefaultPath;
      FDefaultPath := DestDir;
      TN.Assign(ADOGetTableNames);
      for K := 0 to TN.Count - 1 do
      begin
        SN := DestDir + TN[K] + '.csv';
        if FileExists(SN) then
        begin
          DoProgress(0, 'Emptying "' + TN[K] + '"...');
          FADOTable.Close;
          while FADOTable.Active do;
          FADOTable.Connection := FADOConnection;
          FADOTable.TableName := TN[K];
          FADOTable.Open;
          if FADOTable.Active then
          begin
            Result := True;
            ADOGetFieldNames;
            FADOQuery.Connection := FADOConnection;
            FADOQuery.Recordset := FADOTable.Recordset;
            FADOQuery.Open;
            FADOQuery.SQL.Text := 'DELETE * FROM [' + TN[K] + ']';
            FADOQuery.ExecSQL;
          end;
        end;
        DoProgress(0, '');
      end;
      DoProgress(0, 'Compacting...');
      CompactDatabase(DN, FADOConnection);
      DN := ADOSetConnectionString(ConStr);
      for K := 0 to TN.Count - 1 do
      begin
        SN := DestDir + TN[K] + '.csv';
        if FileExists(SN) then
        begin
          DoProgress(0, 'Opening "' + TN[K] + '"...');
          FADOTable.Close;
          while FADOTable.Active do;
          FADOTable.Connection := FADOConnection;
          FADOTable.TableName := TN[K];
          FADOTable.Open;
          if FADOTable.Active then
          begin
            Result := True;
            ADOGetFieldNames;
            FADOQuery.Connection := FADOConnection;
            FADOQuery.Recordset := FADOTable.Recordset;
            FADOQuery.Open;
            S := ExtractFilePath(SN);
            SetLength(S, Length(S) - 1);
            try
              DoProgress(0, 'Filling "' + TN[K] + '"...');
              FADOQuery.SQL.Text := 'INSERT INTO [' + TN[K] +
                '] SELECT * FROM [' + ExtractFileName(SN) + '] IN "' + S +
                '" "Text;HDR=YES;FMT=Delimited(,);CharacterSet=65001;"';
              FADOQuery.ExecSQL;
            except
              FADOQuery.Connection := nil;
              FADOQuery.Close;
              DoProgress(0, 'Error in "' + TN[K] + '"');
              ShowMessage('Error in "' + TN[K] + '"' );
              Result := False;
              Exit;
            end;
            FADOQuery.Connection := nil;
            FADOQuery.Close;
            for J := 0 to FTemp.Count - 1 do
            begin
              if (FTemp.Codes[J] in [FT_MEMO, FT_WIDEMEMO]) then
              begin
                FADOTable.First;
                while (not FADOTable.EOF) do
                begin
                  S := FADOTable.Fields[J].AsString;
                  if (Pos('_\', S) <> 0) then
                  begin
                    S := StringReplace(S, '_\r\n_', #13#10, [rfReplaceAll]);
                    S := StringReplace(S, '_\r_', #13, [rfReplaceAll]);
                    S := StringReplace(S, '_\n_', #10, [rfReplaceAll]);
                    FADOTable.Edit;
                    FADOTable.Fields[J].AsString := S;
                    FADOTable.Post;
                  end;
                  FADOTable.Next;
                end;
              end;
            end;
          end;
        end;
        DoProgress(0, '');
      end;
    finally
      FADOTable.Close;
      if Result then
      begin
        DoProgress(0, 'Compacting...');
        CompactDatabase(DN, FADOConnection);
      end;
      FDefaultPath := DefDir;
      TN.Free;
    end;
  end;
  DoProgress(0, '');
  EndUpdate;
end;

Answer 2

You need to split the database. From Access 2007 specifications

Access database (.accdb) file size

2 gigabytes, minus the space needed for system objects

Note Although the maximum size for a single database file is 2GB, you can work around this limitation by using a split database. A front-end database file can point to thousands of back-end database files, each of which could be as large as 2GB. For more information, see the topic, Split an Access database.

You should also consider to move to another relational database management system(RDBMS) like Oracle,SQL server, FireBird (you name it)