Client/Server and ODBC

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Aug-2002 >

Client/Server and ODBC


Mike Gunderloy


In this issue’s column, Smart Access eXTRA eNewsletter editor

Mike Gunderloy tackles some of the questions he’s gotten

from readers over the past several years.


I’ve always been under the impression that when you place

an Access MDB on a server and then run a query against a

table, all the processing is still done at the client PC. In other

words, if I query a 100-record table to get one record, all 100

records go across the LAN, and then the SQL is processed by

Jet on my machine. This was always explained to me as a

drawback of Access being a file-based database product.

I’m wondering if this is still true. If not, was this correct

in the past? Can you give me a definitive answer? Does it

matter whether DAO, ADO, or RDO is used?


Well, you’re partly right: All of the processing does

indeed happen on the client. But that doesn’t mean that

every record has to cross the LAN. It’s easy enough to

prove that the processing happens on the client: Put an

Access database on a server that doesn’t itself have

Access installed, and you’ll discover that you can still

open it from a client copy of Access.


But that doesn’t mean that the Jet engine has to be

stupid about things. Look at it this way: As far as Jet is

concerned, there isn’t really much difference between

an MDB on the client and an MDB on the server. To

Jet’s way of thinking, a server is just a big hard drive

that’s sort of slow at delivering information. And,

because Jet has an intimate knowledge of the Access

file format, it can use search strategies other than

simply reading all the records in a table to find a

particular record.


Consider a query like this one:


SELECT * FROM [Order Details] WHERE OrderID = 10704


With an index on the OrderID field, Jet doesn’t

have to read every row of the table to locate the desired

records. Instead, it can read the OrderID index and use

that index to locate the correct records in the file. Then

Jet needs only read those pages from the file that

contain the desired information. Of course, because Jet

reads data a page at a time rather than a record at a

time, Jet will probably read some extra data. But Jet

certainly won’t read the entire table to resolve a query

such as this one unless there’s no useful index or the

table is small. With small tables, Jet may decide that it’s

faster to read the entire table than it would be to bother

with reading the index and then reading the table.

Of course, this still doesn’t make Jet as efficient

as a true client/server database where network traffic

is concerned. If you execute the exact same query

against a SQL Server database, even less information

will cross the wire. Access will send only the SQL

statement (or the name of an existing view or stored

procedure) to the server. The database server will then

locate the appropriate records (probably by performing

the same indexed search that I just described) and send

only the resulting records back. Thus, the client/server

version doesn’t have the overhead of downloading

the entire index or the other records that are located

on the same data pages. It also avoids Jet’s two accesses

to the database file: once to read the index, once to

fetch the data.


So is that why most experienced developers

recommend moving the SQL Server for networked

databases? Not really. With today’s network speeds,

it’s not often that the download of a single index is

going to make a difference that’s visible to the end

user. Normally, it’s not the speed but the number of

simultaneous users that dictates a move to a client/

server database. This, too, is a consequence between

the processing schemes used by a file/server database

and a client/server database.


When you have five or 10 (or 50) clients sharing

a single file/server database, you have that many

programs actually modifying the shared file. The

different copies of Jet don’t talk to each other, so they

have to communicate by locking sectors on the server,

and they all have to do their job perfectly. Turn off a

computer, or kick out a network cable, and you can

leave things in an inconsistent state for everyone.

In contrast, it doesn’t matter whether a client/

server database has one client or 100, the disk file is

only being read and modified by a single program: the

database server manager. The database server manager

can act as a traffic cop and gatekeeper for all of the

clients. The manager has complete knowledge about

the database file because it’s the only application

modifying the file. Turn off your computer and the

server will notice and release any locks that it was

holding on your behalf. The result is that client/server ...


Read More Here:

Access Answers