I have used BDE and ADO, both with "native" drivers and ODBC. To be honest I have found no noticeable speed difference as the real work is done inside SQL Server and in ClientDataSets. These above technologies are just conduits.
What I have found is that ADO is easiest to configure, distribute and use. It also handles the greatest variety of SQL Server features.
I believe that the dbExpress for SQL Server is an ADO wrapper (anyone confirm/deny?).
Have fun
Simon