If you copy SQLQuery1 to BufDataSet, then closing your Database connection is irrelevant with Bufdataset's action. BufDataSet will stay as open.
What SQLTransaction1.CommitRetaining; will do, if applied before ODBCConnection1.Close(); ?
Transaction is more related with server. If you change any content of a table, e.g. using SQL statements like insert, delete, or update, the operatios are not reflected in the physical DB table immediately. They are stored in a temporary virtual space. If you rollback transaction, then the changes you made since the start of transaction are just lost. You have to commit your changes in order for the changes to be stored in the DB.
But in the case of just browsing (SQL select statement), it does not matter whether you commit or rollback, because there are no change to be updated to the database.
One thing related with transaction is that it closes all the datasets -- in your case, SQLQuery1. If you connect DBGrid to SQLQuery1, not BufDataSet1, then you'll see it is closed by Transaction.Commit or Rollback, even though you do not run ODBCConnection.Close.
But if you run Transaction.CommitRetaining or RollbackRetaining, SQLQuery1 will stay as open. You'll see the content of it at DBGrid.
I do not know whether ODBCConnection.Close will automatically close all the relevant datasets --- I have never done that. You may check yourself this by running following code, and what happens when showmessage boxes are showing themselves.
procedure TForm1.Button1Click(Sender: TObject);
begin
SQLQuery1.Close;
SQLQuery1.SQL.Text := 'select * from users';
SQLQuery1.Open;
SQLTransaction1.RollbackRetaining;
showmessage('Can you see the content in the dBGrid?');
ODBCConnection1.Close();
showmessage('Still can you see the content in the dBGrid?');
end;