Cursor Location

Most of the new VB programmer face problems with Cursor Location when it comes to creating connection with data base. Even I did face the problems while working with the database in my early days. I found a very nice document on web (did not remember the exact source) describing Cursor Location in details. So sharing the same with you all.

What Are Cursors?
In ADO, when we talk about cursors, we are essentially talking about a set of rows. When you execute a query that returns rows of data, such as SELECT * FROM mytable, the resulting data is handled using a cursor. A cursor can be located either on the client with the adUseClient argument, or on the server with the adUseServer argument. In addition, the are 4 types of cursor: adOpenForwardOnly, adOpenStatic, adOpenDynamic, and adOpenKeyset.
The different types and locations will be discussed in further detail below. Your choice of cursor type and cursor location will affect what you are able to do with the data you retrieve, and how changes made to the data by other users are reflected in your copy of the data.
Cursor Location
The ADODB.Connection object (the ADO object used to broker all data exchanges between the VB application and the MySQL server) has a property known as CursorLocation which is used to set/retrieve the cursor location that will be used by any recordset objects that access their data through the connection object.
The CursorLocation property can only be set while the connection is closed, and the property will be inherited by any recordset objects that access their data through the given connection object. Recordset objects can also explicitly set a cursorlocation different than the connection objects cursorlocation as long as it is set before the recordset is open. The two options available for this property are adUseClient and adUseServer, with adUseServer being the default property.

adUseServer
When using the adUseServer server-side cursorlocation, responsibility for handling the data generated by a query lies with the database server. MySQL itself does not support server-side cursors, so the data handling is actually done by the Connector / ODBC driver. The benefit of server-side cursors is that we gain access to the dynamic cursor type. This allows us to see any changes to the data that are made by other users in the data our application is accessing.
For example: let's say we are selling tickets to a concert with our application, we need to know that a given seat is available for sale in real-time to ensure we do not double-book the seat. With a server-side cursor, we can be sure that the data we are manipulating is the most current possible. In addition, we have the ability to lock the data we are working on as we edit it, to make sure our changes are going to be posted to the database successfully.
With a server-side cursor (adUseServer), we have access to the adOpenDynamic and adOpenForwardOnly cursor types, and all four of the recordset lock types, which will be discussed below.
It should be noted that using a server-side cursor, and the adOpenDynamic cursor in particular, will result in a significant performance loss, and should be avoided if at all possible. In addition, certain functionality, such as the RecordCount property of a Recordset and the GetChunk and Appendchunk function for handling BLOB data, will fail or return abnormal results when used with a server-side cursor.
adUseClient
Client-side cursors, specified with the adUseClient keyword, are handled internally by ADO. These cursors offer more functionality than their server-side counterparts, and also result in less load being placed on the server. Most advanced ADO functionality is designed for use with client-side cursors, and I personally use client-side cursors for all my applications (with one exception).
When using a client-side adUseClient cursor, only the adOpenStatic cursor is available, and we cannot use the adLockPessimistic lock type (see below).
Client-side cursors also help decrease load on our MySQL server, since with a static cursor data is sent to the client and then the server has no further communications with the client. This allows your server to scale a lot better than with server-side cursors.