Speed diferences among ODBC, OLEdb, DAO

[rushdib]

Hi,
We are using ODBC connections to connect to SQL Server from Access(97 and 2000). It's quite slow in data transfer, and I heard ODBC connections are slow specailly in updates. Is it true?
Is OLEdb or DAO is faster than ODBC in data transfer from SQL Server to MS Access?

Thanks in advance,

Rushdi

 

[bperry]

My (skimpy) understanding if that ole is faster than odbc because it eliminates one extra layer of technology that the bytes have to pass through. OLE is clearly the direction MS is moving in, and will at some point replace ODBC.

OLE works with SQL Server, not Access. (I think I might be right about that.)

DAO is also based on the Jet engine. I'm not sure where it fits in the alphabet soup.

There's probably some white papers, etc about all that stuff at this link (which happens to be down just right now when I tried it.

http://www.microsoft.com/data

 

[cmmrfrds]

Generally, DAO is Access specific and is probably the fastest product to work strictly with Access databases. It is not used with SQL Server unless you go through an ODBC interface. DAO is basically the library for data manipulation on an Access MDB. So, to use DAO with SQL Server the layers are DAO, Jet, ODBC and finally SQL Server. ADO along with OLE DB comes under the umbrella of MDAC (Microsoft Data Access Components), which comes native on the Windows 2000 and above OS's. ADO is the counterpart to DAO in that it is the library for all the data objects. It uses OLE DB kind of like DAO uses Jet to get at the data in the database.

Both ODBC and OLE DB have another layer underneath that provide service to the specific database. There is an ODBC driver for Access, SQL Server, Oracle, etc... There are OLE DB providers for Access, SQL Server, Oracle, etc....

Unlike DAO (Access MDB) the ADO libraries are used with many front end products. You can use DAO with front ends that use an Access MDB. ADO is more versital in that the same ADO code can be used in multiple front end products that go to multiple database products.

The SQL Server native OLE DB provider for ADO will be faster than ODBC linked tables. With ODBC linked tables you end up using Jet along with ODBC, which is even more overhead than going through just the ODBC layer. Be aware, that the OLE DB provider named MSDASQL routes you through ODBC so it will not be as efficient as the native provider. Use the provider called SQLOLEDB.1 since it will interface directly with SQL Server.

 

[bperry]

It constantly puzzles me when I see:

SQ<happyface>EDB.1

I can never figure out what the heck it is: I'm always afraid it is some kind of inside joke, and I am on the outside.

cmmrfrds: what does it mean??

thanx

 

[hmadhur]

Hi bperry,
You see SQ<Happy Face>EDB.1 instead of SQLOLEDB because the Emoticons/Smileys option is checked when you post your replies. The word LOL means <Happy Face> in Emoticons/Smileys Jargon that you see. So whenever you wish to Write SQLOLEDB try unchecking the Emoticons/Smileys checkbox provided just below the message box(as I have did since you will see LOL as 'LOL')



Happy Smiling

 

[bperry]

Gotcha.
thanks

 

[cmmrfrds]

I was wondering about that myself, thanks hmadhur.

LOL