I am curious to see other responses. I have been trying to figure out the correction configuration for a small access database. I would say a max of 5 concurrent users at a time. Anyway, it seems like the OlEDB Jet 4.0 provider is more advanced than you might get with the generic ODBC drivers.
I'd use the native SQL Server provider - SQLOLEDB.1. It's more flexible and faster than going through ODBC. (Not to mention the fact that OLEDB is the direction that Microsoft is taking.) Generically, this question depends on the DBMS and what you need to accomplish (especially with Oracle).