By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

adUseServer versus adUseClient

patjones
Expert 100+
P: 931
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
May 14 '08 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 112
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
May 19 '08 #2

ADezii
Expert 5K+
P: 8,637
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.
May 20 '08 #3

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim rst As ADODB.Recordset
  3. Set rst = New ADODB.Recordset
  4.  
  5. rst.CursorLocation = adUseClient
  6. rst.Open strDataSource, conn, adOpenKeyset, adLockOptimistic
  7.  
  8. Set cboTitle.Recordset = rst
  9.  
Here, "strDataSource" 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.CursorLocation 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
May 20 '08 #4

ADezii
Expert 5K+
P: 8,637
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim rst As ADODB.Recordset
  3. Set rst = New ADODB.Recordset
  4.  
  5. rst.CursorLocation = adUseClient
  6. rst.Open strDataSource, conn, adOpenKeyset, adLockOptimistic
  7.  
  8. Set cboTitle.Recordset = rst
  9.  
Here, "strDataSource" 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.CursorLocation 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.?
May 20 '08 #5

patjones
Expert 100+
P: 931
  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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. strDataSource = "SELECT fldTitleCode, fldTitle FROM tblTitles ORDER BY fldTitle"
  3.  
5) NumberOfColumns = 2, BoundColumn = 1, ColumnWidths = "0.0
in ; 2.5 in", RowSourceType = "Table/Query"
May 20 '08 #6

ADezii
Expert 5K+
P: 8,637
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?
May 21 '08 #7

ADezii
Expert 5K+
P: 8,637
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?
May 21 '08 #8

patjones
Expert 100+
P: 931
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

Expand|Select|Wrap|Line Numbers
  1. 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):

Expand|Select|Wrap|Line Numbers
  1.  Public Sub openConn() 
  2.  
  3. 'Set connection and open it
  4.  
  5. Set conn = New ADODB.Connection
  6.  
  7. conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False; Data Source = J:\BENEFITS\Pat's Stuff\dbLeaveTracking_BE.accdb"
  8.  
  9. End Sub
  10.  
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 "adUseClient" 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 "adUseServer".
May 21 '08 #9

ADezii
Expert 5K+
P: 8,637
Thanks...

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

Expand|Select|Wrap|Line Numbers
  1. 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):

Expand|Select|Wrap|Line Numbers
  1.  Public Sub openConn() 
  2.  
  3. 'Set connection and open it
  4.  
  5. Set conn = New ADODB.Connection
  6.  
  7. conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False; Data Source = J:\BENEFITS\Pat's Stuff\dbLeaveTracking_BE.accdb"
  8.  
  9. End Sub
  10.  
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 "adUseClient" 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 "adUseServer".
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.
May 21 '08 #10

patjones
Expert 100+
P: 931
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, "adUseServer" could cause problems, right?

Thanks.

Pat
May 22 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.