ODBC or OLEDB

[Kenln]

Hi all,

First I should say I know very little about implimenting OLEDB. Thanks all.

I have an MSAccess fe. The be is from both MSSQL and an AS-400 (actually an iSeries but the AS-400 name seems to stick).

For MSSQL I create the table in Access first then use the wizard to move the table to MSSQL.

For the AS-400 I create pass-through queries. I have recently started using DSN-Less connections. This has been working well. The IT group still has security concerns and is asking about OLEDB Provider connections.

Can someone direct me to a comparison of OLEDB vrs ODBC?
Does anyone have a preference? Why?

Thank you for your help,

 

[Banana]

First, OLE DB and ODBC both are standard pretty much created by Microsoft.

Second, OLE DB is supposed to succeed ODBC. I believe that for any Microsoft products (MS SQL, Jet (Access), and probably Visual Fox Pro), OLE DB is the recommended & preferred.

Third, not all third parties are optimized for OLE DB. For example, MySQL has both ODBC and OLE DB providers, but it is faster and effective for MySQL to use ODBC instead of OLE DB. You will have to double check with iSeries and whether its driver(s) (Mind, some applications can have more than one driver and some driver may not be written by the first party but rather by a third party) are optimized for either.

Regarding security, I am not aware that OLE DB was supposed to be more secure than ODBC; I usually hear about how OLE DB is supposed to be faster and effective, but security is first for me. Maybe others knows and can elaborate.

So to find out which is appropriate for your need would actually depend on what application it is and how it's optimized.

HTH.

PS: Forgot one more thing. In some case, it is indeed possible to use OLE DB with ODBC connection, though I couldn't think of a good reason to do so. I only mention as a caution to not make the connection topheavy with so many layers. ODBC is primarily SQL-oriented while OLE DB is a bit more generic and can include retrieving data from non-RDBMS applications.

 

[Kenln]

Sorry for the late response.

Thank you,