adUseClient vs adUseServer

[Schmidt]

Most of the time i only use aduseclient but when i was aware of aduseclient to use client resources and aduseserver uses server resources, i tried to use aduseserver to fetch data to msflexgrid and as a result, no data was fetch but when i returned it to aduseclient, data were fetch and placed to msflexgrid. Why is that?

An ADO-Recordset is a "Wrapper-Class for Records" which in
adUseClient-mode combines:
- a free positionable "Cursor" (an internally movable Pointer to the current-Record)
- true "Container-Mode" (ability to completely disconnect from the Server-DB, storing "the Set of records" InMemory)
- reliable RecordCount-, Sort- and Filter-methods, directly usable after returning from the DB-Select (having "the Set" InMemory behind the Rs)

In adUseServer-Mode the ADO-Recordsets are basically reduced to "pure Cursor Mode".
(imagine such a "Cursor" as a "Stripe, consisting only of the Fields/Columns of only one single Record")

When you use the Cursor-Positioning-Methods (MoveFirst, MoveNext, AbsolutePosition)
on an ADO-Recordset, then in adUseClient-Mode the positioning can be performed on
the already "InMemory-Cached" copy of all the Records, the "SQL-Select-String" described.

In adUseServer-Mode such a positioning (usually) needs more efforts, since the
"next Record you want to position the ADORs to" has to be requested from
the Server itself.

adUseServer is the "more complex mode" (under the covers) - and it puts quite
some strain on serverside Resources (but it is the somewhat "lighter" mode,
memory-wise on the client-side).

With todays PCs which have Memory in the Gigabyte-Range, its usage is not that
worthwile as it was 15 years or so ago.

So, adUseClient is the way to go today IMO - much easier to handle for the ADO-
OleDB- and ODBC-Drivers (especially on "non-MS-Drivers", as e.g. MySQL) - and
with much less "surprises" when you use such Rs (as e.g. in your FlexGrid-Filling).

In adUseClient-Mode you can rely on, that directly after you selected an Rs, you
have all the contents of the Select (all the Records) InMemory - and that the
Rs will be immediately "responsive" with the correct RecordCount, but also with
its internal (InMemory-based, non-DB-related) Filter- and Sort-Methods.

adUseServer is useful only in a few (rare) cases (e.g. it performs better with
some Bulk-Inserts in a tight "AddNew"-loop - but that's it basically).

Modern DB-access-schemes all prefer something like adUseClient-Mode these
days (e.g. in ADO.NET you don't have something like adUseServer-mode anymore).
Though for direct "single-Record-access" to DB-Engines there often are special
"Cursor-Classes" available in most larger Frameworks as e.g. .NET and Java.

Olaf