patjones 931
Recognized Expert Contributor
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
10 14975
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 8,834
Recognized Expert Expert
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: - If you are using Jet, the Cursor Location will always be on the Client PC, because Jet always runs on this machine.
- 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.
- You must use Client-Side Cursors for some advanced functionality, namely:
- If you wish to use the Sort Method on a Recordset.
- If you want to create a Disconnected Recordset that will later reconnect to the Database, and Update multiple Records in a Batch.
- Both of the above scenarios will not work with Server-Side Cursors.
- 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.
- 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 931
Recognized Expert Contributor
Hello Pat, here is some information that I gathered for you regarding ADO Cursor Location, which should be helpful:- If you are using Jet, the Cursor Location will always be on the Client PC, because Jet always runs on this machine.
- 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.
- You must use Client-Side Cursors for some advanced functionality, namely:
- If you wish to use the Sort Method on a Recordset.
- If you want to create a Disconnected Recordset that will later reconnect to the Database, and Update multiple Records in a Batch.
- Both of the above scenarios will not work with Server-Side Cursors.
- 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.
- 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, "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
ADezii 8,834
Recognized Expert Expert
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, "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
- Is the Back End Database in Access? If not Access, what Database Management System?
- The Table this is being used to 'pull' the values into the Combo Box, does it reside in the Front or Back End?
- 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?
- Can you post a sample SQL String, what a typical strDataSource may look like?
- What are the specifics on the Combo Box: Number of Columns, Bound Column, etc.?
patjones 931
Recognized Expert Contributor - Is the Back End Database in Access? If not Access, what Database Management System?
- The Table this is being used to 'pull' the values into the Combo Box, does it reside in the Front or Back End?
- 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?
- Can you post a sample SQL String, what a typical strDataSource may look like?
- 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 8,834
Recognized Expert Expert
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 8,834
Recognized Expert Expert
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 931
Recognized Expert Contributor
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 "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".
ADezii 8,834
Recognized Expert Expert
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 "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.
patjones 931
Recognized Expert Contributor
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Wayne Wengert |
last post by:
I have an ASP page in which I reference quite a few ADO constants such as
adOpenStatic, adLockReadOnly and they work fine but a reference to
adUseServer comes up as undefined?
I have a typelib...
|
by: Joel |
last post by:
Gentlemen,
I still don't understand why... But the only way MySql works is if you
set rstTable.CursorLocation = adUseClient. (if it's not set it won't
..Update, load a grid, doesn't know...
|
by: Dieter Vanderelst |
last post by:
Dear all,
I'm currently comparing Python versus Perl to use in a project that
involved a lot of text processing. I'm trying to determine what the most
efficient language would be for our...
|
by: Joshua D. Drake |
last post by:
Hello,
I think the below just about says it all:
http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg
Sincerely,
Joshua Drake
|
by: Andrew Robinson |
last post by:
I need to create a shared static field for use within a number of different
classes. Which one should I be using or are they all really the same thing?
public class Widget
{
private Widget() {}...
|
by: Jon Lapham |
last post by:
I have a table that stores TEXT information. I need query this table to
find *exact* matches to the TEXT... no regular expressions, no LIKE
queries, etc. The TEXT could be from 1 to 10000+...
|
by: Xah Lee |
last post by:
Tabs versus Spaces in Source Code
Xah Lee, 2006-05-13
In coding a computer program, there's often the choices of tabs or
spaces for code indentation. There is a large amount of confusion about...
|
by: Vishakh |
last post by:
Hello,
I am trying to create pivot tables using data from CSV files. Here is an example:
String conStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\\";
String query = "SELECT * FROM...
|
by: aj |
last post by:
DB2 8.2 LUW FP14
Is there any real difference between
select blahblahblah... where blah IN (select blah......)
versus
select blahblahblah... where blah = ANY (select blah.....)
versus
select...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |