Datasheet

Chapter 1

Data Access Models

Certainly one of the biggest issues to deal with these days is how to access your database within the

various options in Microsoft's "Alphabet Soup" of data access architectures. Which models are available

in what circumstances depends primarily on the version of SQL Server, and the choice and version of

your programming language.

There are four different access models that Microsoft considers as being current for accessing SQL

Server. These are:

❑ ADO: ActiveX Data Objects. The new RDO – or at least that's what Microsoft would like you

to believe. Don't – ADO is its own animal. Each new version seems to improve performance

and add features. Unlike RDO, which was based on ODBC, ADO is based on OLE DB. This

provides a level of flexibility that ODBC alone can't offer, but it comes with more than just a

few headaches (note that ADO can still indirectly use ODBC for connectivity by using the

OLE DB provider for ODBC). RDO had a few features that will never be in ADO, but ADO

has some really cool features – with persistent recordsets, filters, sorts (without going back to

the server), and others – that RDO never had.

It's tough to take just a brief look at ADO, but since I have to, I'll say this much: ADO is now

competitive, if not faster, in terms of speed compared with RDO, and has a very robust

feature set. It is still nowhere near RDO in reliability as of writing this, but Microsoft has and

continues to make a substantial investment in ADO and OLE DB. This is where they are

saying the future is, and I would suggest any new non-C++ based development uses this

access method (there'll be more for the C++ crowd shortly).

❑ ODBC: Open Database Connectivity. If you've been developing for any length of time at all

(say, for more than a week), you have to have heard of ODBC. It is a Microsoft-pushed

standard, but it is most definitely a standard – and a very good one at that. ODBC provides a

way of gaining cross-platform access to database information. It is quite fast (often as fast as or

faster than the native driver for your database) and allows you to use most of the mainstream

standard SQL statements regardless of what the back-end expects for syntax. In short, ODBC

is very cool. The major shortcoming for ODBC is that it is very much oriented to tabular data

(columns and rows), and doesn't deal with non-standard data such as a directory structure or a

multi-sheet database.

❑ OLE DB. The primary competitor to ODBC at this point, OLE DB is an attempt at having an

open standard to communicate with both tabular and non-tabular data. OLE DB uses what is

called a provider. A provider is a lot like an ODBC driver except that it is relatively self-

describing. That is, it is able to tell the application that uses it what kind of functionality it

supports. As mentioned earlier, OLE DB is the foundation under ADO. It is very fast indeed

when not being used with ADO (that extra layer adds some overhead) but, since it deals with

a number of items that aren't compatible with VB, you'll typically only see OLE DB being

used directly by C++ programmers. It is far more of a pain to program in than ADO, but it is

much faster by itself than when used in conjunction with ADO. If you're a non-C++

programmer, stick with ADO at this point. If you're a C++ programmer though, you're going

to need to figure out whether the extra speed is worth the hassle.

❑ Java Database Connectivity: (JDBC). OK, so I said there were five, and there are, but I have

to say that JDBC is still something of an outcast at this point. It is used primarily in the web

arena and by non-Microsoft users. The last time I saw much of anything done with it, it was

fairly simple to use, but had very little functionality and was very slow. In short – unless for

some reason you absolutely have to, don't go there.