What is the difference between OLEDB and ODBC? When do I use which and how do I know what I am looking at is a OLEDB driver v/s an ODBC driver?
Answer
OLEDB and ODBC are two different database API's. ODBC is an older standard and is actually not specific to windows - you can get Unix-based ODBC libraries. OLEDB is a COM-based API for database connections.
There is a driver for wrapping ODBC with an OLEDB front-end if the database you are using does not come with a native OLEDB driver. IIRC DB2/400 and Sybase OLEDB drivers (for example) use this method.
The ODBC administrator in Windows is only concerned with ODBC drivers. Both ODBC and OLEDB can use connection strings, which have a slightly different format. You can tell from the connection string - www.connectionstrings.com has samples of connection strings for many different database drivers.
Edit: Oracle has a driver technology for every day of the week.
Oracle have a native OLEDB driver called 'Oracle provider for OLEDB' or some such. If you're using ADO (non-.Net, which sits over OLEDB) this would be the preferred driver.
They also have an ODBC driver that would be (for example) useful for extracting from an Oracle database into MS-Access or with an application or system that does not support OLEDB. For example, older Delphi/Oracle or Powerbuilder/Oracle apps will probably use this driver.
Oracle also has an Oracle-specific interface called OCI. This is the fastest interface if you're writing in C and will also work on non-windows plaforms but ties your application to Oracle. Dynamic language bindings such as cx_Oracle for Python tend to be wrappers around OCI.
If you're using .Net you probably want ODP.Net rather than using the .Net provide for OLEDB. This is the standard .Net interface library provided by Oracle.
There are several JDBC drivers for Oracle. There is a type-2 driver which is a wrapper for OCI and a type-4 driver which is written natively in Java and communicates directly over the network to the server. If you're using Java the type-4 driver is probably the best for most applications (you don't have to install a full Oracle client) unless you have a particular reason to need the type-2 driver.