Fixing Problems with adUseServer

Problem

Developers experience serious problems using ADO with CursorLocation=adUseServer setting. Many of these problems are bugs in OLE DB providers themselves. Especially problematic is using adUseServer in conjunction with LockType=adLockBatchOptimistic, so called "batch update mode". It just won't work with some OLE DB providers, when you add or delete rows.

Solution

If you use True DataControl with CursorLocation=adUseServer, you won't experience many of these problems, True DataControl will work around them. You must bear in mind, however, that some of the provider problems can be resolved only if you set TData.SafeMode=True. This is because working around them can affect performance. Working around LockType=adLockBatchOptimistic problems does not require SafeMode=True. Other problems, such as those concerning Recordset.Filter (also occurring in connection with TData query mode), require SafeMode=True. Of course, there can be provider problems that TData is unable to work around.

Note

True DataControl Lite will provide the same improvement for ComponentOne True DBGrid 8.0. So you can use True DataLite (free of charge) to solve adUseServer problems for True DBGrid 8.0. To benefit from this improvement using controls other than TDBGrid, you must purchase the full True DataControl version.

Explanation

It is not accidental that adUseServer causes problems, nor it is surprising that they are especially bad in batch update mode. The problems are not in ADO itself, as it may seem, but in OLE DB providers used with ADO. Using CursorLocation=adUseClient means that you are not working with an OLE DB provider directly. Instead, adUseClient brings an intermediary into the picture, Microsoft Client Cursor Engine (CCE). CCE fetches all the data into memory first (thus causing a performance problem sometimes, see Performance on large recordsets), and then it operates on memory-resident data, which is much simpler, so it naturally is more stable. But you can't always rely on adUseClient, both for its performance problems and because it disables some functionality such as dynamic recordsets (reflecting the changes made by other users), database validation rules, etc. When you switch to adUseServer, the CCE component no longer stands between your application and the OLE DB provider, ADO talks directly to the provider, and this is when provider problems start to show up. Using batch update mode aggravates the situation because it means that more than one row can have pending updates at any given time. Many OLE DB providers simply can't handle this situation correctly.

Using True DataControl will help you to avoid this situation. True DataControl, working as an intermediary between OLE DB providers and your application, eliminates these problems. Obviously, we cannot guarantee that it will solve all OLE DB provider problems forever, but it eliminates most problems with batch update mode, notification non-reentrancy and others.