adUseServer versus adUseClient

[patjones]

Hi:

This is pretty straightforward , but I can't get as straightforward an answer as I'd like in my books or in other forums.

What's the difference between adUseServer and adUseClient when using ADO to connect to a back end database?

I've seen some comments in various places about performance differences, but since I'm new to ADO I think my main source of confusion lies in what the difference between using server and client resources is.

Thanks!

Pat

[janders468]

I am not sure about if you are actually using a client server database (where access is the front end to some other back end), however if you are using access, whether or not the tables reside in more than one database (a 'split' database) there are still some differences. If you tell access to use the client then certain metadata and processing will be kept and processed by Access whereas if you don't then the assumption is that the information will be held on the server and Access will not keep this information for you. The main place where this has come up for me (I am sure there are many others) is using the recordcount property of an ado recordset. If the recordset is set to adUseServer access will assume that the record count is being held and processed on the server, but if you tell it to hold it on the client then it will available to you. There is a performance hit for carrying around this extra memory. That's the main difference I know of, but would be happy if someone could enlighten the conversation further

[ADezii]

Hi:

This is pretty straightforward , but I can't get as straightforward an answer as I'd like in my books or in other forums.

What's the difference between adUseServer and adUseClient when using ADO to connect to a back end database?

I've seen some comments in various places about performance differences, but since I'm new to ADO I think my main source of confusion lies in what the difference between using server and client resources is.

Thanks!

Pat

Hello Pat, here is some information that I gathered for you regarding ADO Cursor Location, which should be helpful:

1. If you are using Jet, the Cursor Location will always be on the Client PC, because Jet always runs on this machine.
2. When you are using a Client-Side Cursor, ADO will pas your request to the Microsoft Cursor Service for OLE DB. This Component is designed to enhance the functionality of Recordsets retrieved from any OLE DB Provider. In most cases, Client-Side Cursors with an Access Database only adds overhead to data access, because data is being cached twice on the same machine: once in the Jet Database Engine and once in the Cursor Service.
3. You must use Client-Side Cursors for some advanced functionality, namely:
   1. If you wish to use the Sort Method on a Recordset.
   2. If you want to create a Disconnected Recordset that will later reconnect to the Database, and Update multiple Records in a Batch.
   3. Both of the above scenarios will not work with Server-Side Cursors.
4. When you use Jet to provide your data (as opposed to using SQL Server or some other OLE DB Provider), you should use a Server-Side Cursor whenever possible. You'll get better performance, and you won't have an extra layer of ADO (the Client-Side Cursor is provided by ADO) between you and Jet.
5. The generally accepted practice is that you should use the default Server-Side Cursors unless you discover that the additional functionality of Client-Side Cursors is necessary for your Application.

[patjones]

Hello Pat, here is some information that I gathered for you regarding ADO Cursor Location, which should be helpful:

1. If you are using Jet, the Cursor Location will always be on the Client PC, because Jet always runs on this machine.
2. When you are using a Client-Side Cursor, ADO will pas your request to the Microsoft Cursor Service for OLE DB. This Component is designed to enhance the functionality of Recordsets retrieved from any OLE DB Provider. In most cases, Client-Side Cursors with an Access Database only adds overhead to data access, because data is being cached twice on the same machine: once in the Jet Database Engine and once in the Cursor Service.
3. You must use Client-Side Cursors for some advanced functionality, namely:
   1. If you wish to use the Sort Method on a Recordset.
   2. If you want to create a Disconnected Recordset that will later reconnect to the Database, and Update multiple Records in a Batch.
   3. Both of the above scenarios will not work with Server-Side Cursors.
4. When you use Jet to provide your data (as opposed to using SQL Server or some other OLE DB Provider), you should use a Server-Side Cursor whenever possible. You'll get better performance, and you won't have an extra layer of ADO (the Client-Side Cursor is provided by ADO) between you and Jet.
5. The generally accepted practice is that you should use the default Server-Side Cursors unless you discover that the additional functionality of Client-Side Cursors is necessary for your Application.

Thank you all for your replies!

In my situation, I have a split database, with the back end residing on one network drive, and the front end sitting on another. Where I ran into this issue was in trying to populate a combo box with values from a table:

 
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
 
rst.CursorLocation = adUseClient
rst.Open strDataSource, conn, adOpenKeyset, adLockOptimistic
 
Set cboTitle.Recordset = rst

Here, "strDataSou rce" is a SQL string that I set elsewhere in the code, and which tells Access which table to look in to pull the values. The problem was, I wasn't setting rst.CursorLocat ion explicitly. And, no matter what I did (modifying the SQL string, combo box properties, etc.) - the combo box just wouldn't populate correctly. In particular, the columns were all out of order; and a column that I hadn't even specified in the SQL statement was showing up in the combo box.

But when I set CursorLocation to adUseClient, everything fell into place. Does this make any sense?

Pat

[ADezii]

Thank you all for your replies!

In my situation, I have a split database, with the back end residing on one network drive, and the front end sitting on another. Where I ran into this issue was in trying to populate a combo box with values from a table:

 
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
 
rst.CursorLocation = adUseClient
rst.Open strDataSource, conn, adOpenKeyset, adLockOptimistic
 
Set cboTitle.Recordset = rst

Here, "strDataSou rce" is a SQL string that I set elsewhere in the code, and which tells Access which table to look in to pull the values. The problem was, I wasn't setting rst.CursorLocat ion explicitly. And, no matter what I did (modifying the SQL string, combo box properties, etc.) - the combo box just wouldn't populate correctly. In particular, the columns were all out of order; and a column that I hadn't even specified in the SQL statement was showing up in the combo box.

But when I set CursorLocation to adUseClient, everything fell into place. Does this make any sense?

Pat

1. Is the Back End Database in Access? If not Access, what Database Management System?
2. The Table this is being used to 'pull' the values into the Combo Box, does it reside in the Front or Back End?
3. Have you tried assigning the RowSource Property of the Combo Box to the SQL String (strDataSource) directly as opposed to using ADO and assigning the created Recordset to the Recordset Property of the Combo Box?
4. Can you post a sample SQL String, what a typical strDataSource may look like?
5. What are the specifics on the Combo Box: Number of Columns, Bound Column, etc.?

[patjones]

1. Is the Back End Database in Access? If not Access, what Database Management System?
2. The Table this is being used to 'pull' the values into the Combo Box, does it reside in the Front or Back End?
3. Have you tried assigning the RowSource Property of the Combo Box to the SQL String (strDataSource) directly as opposed to using ADO and assigning the created Recordset to the Recordset Property of the Combo Box?
4. Can you post a sample SQL String, what a typical strDataSource may look like?
5. What are the specifics on the Combo Box: Number of Columns, Bound Column, etc.?

1) The back end DB is in Access...

2) The table resides in the back end.

3) I haven't tried this because how will Access know where to find the
table that is referenced in the SQL string without providing
information about the connection to the back end?

4) A typical SQL string looks like:

 
strDataSource = "SELECT fldTitleCode, fldTitle FROM tblTitles ORDER BY fldTitle"

5) NumberOfColumns = 2, BoundColumn = 1, ColumnWidths = "0.0
in ; 2.5 in", RowSourceType = "Table/Query"

[ADezii]

I did some further research for you as it specifically relates to your circumstances, and there seems to be absolutely no doubt that a Server-Side Cursor should be used. The main reason stated was to prevent the double Cursor creation by both ADO and Jet with the resultant overhead and time consumption. Just 1 more question, I need to see how your Connection (conn) is created. I'm assuming that conn is a Modular or Global Variable but 'where' does the actual Declaration reside, and what is the actual Connection String used?

[ADezii]

I did some further research for you as it specifically relates to your circumstances, and there seems to be absolutely no doubt that a Server-Side Cursor should be used. The main reason stated was to prevent the double Cursor creation by both ADO and Jet with the resultant overhead and time consumption. Just a couple more questions, I need to see how your Connection (conn) is created. I'm assuming that conn is a Modular or Global Variable but 'where' does the actual Declaration reside, and what is the actual Connection String used? Are the Back End Tables Linked or is this process handled through the Connection Object?

[patjones]

I did some further research for you as it specifically relates to your circumstances, and there seems to be absolutely no doubt that a Server-Side Cursor should be used. The main reason stated was to prevent the double Cursor creation by both ADO and Jet with the resultant overhead and time consumption. Just a couple more questions, I need to see how your Connection (conn) is created. I'm assuming that conn is a Modular or Global Variable but 'where' does the actual Declaration reside, and what is the actual Connection String used? Are the Back End Tables Linked or is this process handled through the Connection Object?

Thanks...

I declare conn globally (in the code module where I put all my global variables and subs) like

Public conn As ADODB.Connection

and then I have a global subroutine that will allow me to open the connection wherever else in the other modules I want to (naturally, I have a corresponding sub to close it when I am done using it):

 Public Sub openConn() 
 
'Set connection and open it
 
Set conn = New ADODB.Connection
 
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False; Data Source = J:\BENEFITS\Pat's Stuff\dbLeaveTracking_BE.accdb"
 
End Sub

This connection string is the way in which the front end communicates with the back end. I am not using linked tables to connect the front and back ends.

If continuing to use "adUseClien t" works, I guess it's OK (except for the performance hit). It would just be instructive to know what it is about this situation that causes it to work as opposed to "adUseServe r".

[ADezii]

Thanks...

I declare conn globally (in the code module where I put all my global variables and subs) like

Public conn As ADODB.Connection

and then I have a global subroutine that will allow me to open the connection wherever else in the other modules I want to (naturally, I have a corresponding sub to close it when I am done using it):

 Public Sub openConn() 
 
'Set connection and open it
 
Set conn = New ADODB.Connection
 
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False; Data Source = J:\BENEFITS\Pat's Stuff\dbLeaveTracking_BE.accdb"
 
End Sub

This connection string is the way in which the front end communicates with the back end. I am not using linked tables to connect the front and back ends.

If continuing to use "adUseClien t" works, I guess it's OK (except for the performance hit). It would just be instructive to know what it is about this situation that causes it to work as opposed to "adUseServe r".

This is a real puzzler that has me stumped. For experimental purposes, why not Link a single Table then execute a SQL Statement against the Linked Table, making it the RowSource for the Combo Box. If this works, which it obviously should, then I would imagine that the problem relates to some connectivity issue with the Server, based on the Connection Object (conn). Maybe, it is being viewed as some sort of Disconnected Recordset, which may require a Client-Side Cursor. Just speaking out loud - sorry I can't be more helpful. If you ever resolve this problem, please let me know.

[patjones]

This is a real puzzler that has me stumped. For experimental purposes, why not Link a single Table then execute a SQL Statement against the Linked Table, making it the RowSource for the Combo Box. If this works, which it obviously should, then I would imagine that the problem relates to some connectivity issue with the Server, based on the Connection Object (conn). Maybe, it is being viewed as some sort of Disconnected Recordset, which may require a Client-Side Cursor. Just speaking out loud - sorry I can't be more helpful. If you ever resolve this problem, please let me know.

Could it be due to the fact that the back end is residing on just a network drive (our J:\ drive)? It's not really a server, and it may not even contain an installation of Access (don't know whether that matters or not). Seeing that this is the case, "adUseServe r" could cause problems, right?

Thanks.

Pat