I am using adOpenDynamic and the CursorLocation by default is set to Server (V6) and we the program can works both with acess and SQL Server. Now we a problem with recordCount which returns -1. I found following solutions:
Changing the CursorLocation from Server to client OR keeping the CursorLocation as Client and changing adOpenDynamic to adOpenStatic or adOpenKeyset. Based on the similarity between Dynamic and Keyset it seems that it si better to change it with adOpenKeySet.
Now this is my question , do you think that it is better to keep the adOpenDynamic and just change the CursorLocation to Client OR keeping the Server and changing adOpenDynamic to KeySet ?
Thank you.
The answers is yes and no.
Those various options exist for a reason, and it most comes down to functionality and performance.
With the cursor location set to server, you will not "get" all of the data at once. Some of it will come back and the rest will stay on the server until you request it. That request is being done when you move to the next record in the recordset. This is usually better for performance when you have a really large recordset because your app can start using the data before all of it has been set over the network. This is not so good for network usage because there are a lot of round trip "conversations" that occur. It's also not great for the server because you are consuming server resources until you close the recordset.
When you use a client cursor, all of the data is passed over the network as quickly as possible, but your app cannot use the data until it has all passed to the client. With large recordsets, this can take a while and your app could lag as a result. Client recordset are friendly to the server and network, but harder on the client because of the delay.
Think of it this way.... Suppose you have a lot of data you want to display in a grid. With a server cursor, you can display the first "page" of data. Then, when the user scrolls down on the grid, you grab the next 20 rows and display those. So, every time the user scrolls, there is a slight lag (probably not noticeable). With a client cursor, all of the data would need to be loaded in the grid before the user can do anything with it.
In my app, I am very careful about only returning the data I need from the database (I limit the columns and the rows). I almost always you a client side cursor with adOpenStatic. Basically, I call the database, get the data, and then close the recordset as quickly as possible. I then issue separate sql statements for insert, update, and delete.
My best advice to you is to first learn what all of these options do for you, and then use the right one. This probably means that you will end up using several methods throughout your app depending on the functionality you need.