Webmaster Forum / ASP / Database Access / September 2003

You can go here to get more detail:
http://msdn.microsoft.com/library/en-
us/ado270/htm/mdaobj01_19.asp

This page lists the cursor types in order from most
expensive to least expensive. You should rarely use a
keyset or dynamic cursor in an asp page.

The default cursor that you will get if you open a
recordset without changing any properties is a server-
side, forward-only cursor. The following pieces of code
are equivalent:

**************************************************
dim rs,cn
'create and open a connection using the cn variable
set rs = cn.execute("select ...",,1)

or

dim rs,cn
'create and open a connection using the cn variable
set rs = server.createobject("adodb.recordset)
rs.open "select ...", cn
**************************************************

Both examples will result in a server-side forwardonly
cursor, which is the cheapest and fastest. If you want
more control over the features (cursor-type, etc.) you
need to use the second syntax: the execute method always
returns the default cursor. To get a different server-
side cursor, set the cursortype property before opening
the recordset, or use the cursortype argument in
the .Open statement:

**************************************************
dim rs,cn
'create and open a connection using the cn variable
set rs = server.createobject("adodb.recordset)
rs.cursortype = adOpenKeyset 'requires ado constants
rs.open "select ...", cn

or

dim rs,cn
'create and open a connection using the cn variable
set rs = server.createobject("adodb.recordset)
rs.open "select ...", cn, adOpenKeyset
**************************************************

If you want a client-side cursor, set the cursorlocation
property before opening the recordset:

**************************************************
dim rs,cn
'create and open a connection using the cn variable
set rs = server.createobject("adodb.recordset)
rs.cursorlocation = adUseClient 'requires ado constants
rs.open "select ...", cn
**************************************************
Be aware that the only type of cursor that is possible
with a client-side cursor is a static cursor. So even if
you ask for a keyset or dynamic cursor, you will get a
static cursor.

There are several reasons to use a server-side instead of
client-side cursor with Access:
Performance and resources - When a client application
connects to an Access database, it loads its own copy of
the database engine into memory. When data is requested
from the database, it gets loaded into a cache, which has
its own cursor engine, in the client's memory and is then
passed to the requesting app.

With a server-side cursor, the data goes directly from
the cache to the client app.

With a client-side cursor, the data still goes into the
local cache first, but it is then passed to the ADO
Cursor Engine which builds the client-side cursor which
is passed to the requesting app. So the data winds up
being handled by two cursor engines on the client machine
instead of one.

So, should you never use a client-side cursor with an
Access database? It depends on what functionality you
need. For the most part, in asp applications, you only
need a server-side forward-only cursor, because you're
just grabbing the data, looping through it once to create
display elements and that's it.Using a more expensive
cursor than you need is the best way to impair your app's
scalability. However, if you need client-side
functionality, such as the ability to disconnect a
recordset or save it to disk, then you should not
hesitate to use a client-side cursor.

HTH,
Bob Barrows