Nickel tour of ADO's different cursor types

I've been finding a lot of questions about ADO cursors and how they behave, so here's a nickel tour of the different cursortypes.

Default cursortype (I'll abbreviate to ct hereinafter) is forward only, sometimes called firehose cursor. It only supports movefirst and movenext, and is useful for situations where you are making top to bottom passes in a recordset, such as when populating listboxes with data from a lookup table. It's the most efficient since it doesn't have to support moving around in a table.

Static ct is a fully-populated, non-updateable recordset, analogous to snapshot in DAO.

Keyset ct is a non-populated, updateable recordset. By non-populated, they mean that it only returns a set of keys, and doesn't actually return the record associated with that key until you move to it. You might find it interesting to open a keyset recordset and check the recordcount property. It will not be accurate unless you do a movelast, which will populate the recordset. This is a very useful recordset when needing to return a large amount of data, but only look at and/or update a few records from that set. While updates and deletions are visible to other users of the db, insertions are not.

dynamic ct is fully-populated. All changes to the rs are visible to all users. Think TicketMaster, for example.

Now. All client side (cursorlocation=aduseclient) recordsets are static. If you set the locktype to adlockbatchoptimistic, they are disconnectable and batch updateable, too. This means that you can open a recordset in this way, close the connection, and still have the recordset. You can make changes to the local copy, and you can even save them locally to data files with the save method. When you are ready, you can reopen the connection and call the batchupdate method. There's more to know with this, esp. about conflict resolution. Anyway, think salesmen on the road with their laptops who check in in the morning and get their appointments, as well as uploading their notes about what happened to yesterday's appointments.

I try to use client side recordsets whenever appropriate. I'm all for offloading functionality to the client and giving the poor old server a break. Main reason is that clients' hardware is typically underused, and servers overused. I should point out that client side solutions in general are inherently more scaleable, in that whenever you add a client, by definition you add processing power, which is of course not true on a server. Minimizing round trips to a server is a great idea in my mind, especially if the server is accessed via the internet.

Bob Rodes