[AccessD] Problem of a listbox's response on network... Part 1

Jim Lawrence accessd at shaw.ca
Sun Apr 12 14:16:33 CDT 2015 

Hi Jim:

I admit that I am mixing data access and the data engine. How can they be separated? 

IMHO, If you have set up an ADO type connection properly there is not evidence to ADO causing data corruption. I have never experienced it but with DAO, sigh...

I agree with you that just connecting ADO and/or DAO, DAO is always faster but this is where data access and data engine come into play. DAO just doesn't scale and is prone to data corruption, but it is only on larger networks...at least that has always been my experience. DAO may have improved its stability since my separation from the product as I have not used it since around 2000...as it is a 15 years old view.

Data corruption is always possible but for one feature that should be extensively used...I believe it is only available with ADO. Before staring a transaction with the data server, I always initialize it with a "BeginTrans" statement, then run the transaction and if there are any errors, the transaction is rolled back, otherwise it is committed. I can not say that an error with the data has never resulted from this method, but in over a decade I have been completely unaware of any data corruption caused using transaction method. Note additionally, I never keep a persistent connection with the data source...connect, do the business and disconnect.       

One more scenario that I have experienced is with branch sized networks. The government would buy ministry wide site licences and then standardise on a single desktop image, which could be deployed through out the entire network. This worked very efficiently but in some cases, no one every figured out why, MS Access would get confused. Using non persistent connections and/or the ADO provider eliminated such issues...and as it resolved the problem no one followed up on solving it. Maybe you know the cause and have a solution?

Jim
    
----- Original Message -----
From: "Jim Dettman" <jimdettman at verizon.net>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Saturday, April 11, 2015 9:53:56 AM
Subject: Re: [AccessD] Problem of a listbox's response on network... Part 1

Jim,

  Again, you mixing up the data access library with the database engine.  If
your using JET and you have a flakey network, you run just as much chance of
corruption with ADO as you do with DAO.

  Neither DAO or ADO talk directly to a MDB file; they make calls to the
database engine (and with ADO it has to go through a OLEDB provider, which
is why DAO is faster for talking to MDBs) and it's the engine that
manipulates the MDB file.  When your using JET, that manipulation takes
place client side as there is no server side process.

Two points that bear this out:

1. If you were running an app with nothing but ADO that was JET based, and
you flipped the power off during a write, the DB would corrupt just as it
would if you were using DAO.
2. If you use DAO and access SQL Server via ODBC with it, no matter what you
do, you will not see a corruption.

 DAO in of itself has nothing to do with corruption.  It's the RDBMS being
used that determines that.  

 So if your using a JET based DB as it should be used (on a local LAN), then
DAO is the best lib to use.  If your using anything else, then ADO is a
better choice.

Jim.   

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Friday, April 10, 2015 05:15 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem of a listbox's response on network... Part 1

Hi Jim:

This is all a combination of things, particularly of how DAO is used.
Traditionally speaking a DAO connection to its data source is set to a
persistent connection...drop the network a few times (power outages,
rebooted server, licensing issues (even with site licenses) etc; coupled
with no timeouts.) and there is data corruption. If it is not caught in time
the whole BE MDB could end up being unreadable (back in 2003, the ministry I
was working in banned the creation and use of branch built Access
application for that exact reason..I was allowed to continue as I only used
ADO and never had any data corruption issues).  Maybe newer Access versions
have been able to resolve this but I am not aware of it. IMHO, DAO just does
not handle extenuating circumstances when disconnecting from its data-store.
It appears to rip free from the BE and can have issues reconnecting.

OTOH ADO, by its nature, is designed to disconnect gracefully from its data
source. I may be wrong but I have never heard of an ADO connection or
disconnection resulting data corruption. It is because the ADO protocol and
the data sources that the protocol uses tend to be far more robust and
function much better in  unstable environments...among older equipment,
extended networks, over the internet and in the Cloud.   

According to a Microsoft article, the ADO protocol (ADO.Net) not only is
able to connect to RDBMS data sources, but now and more so in the future, be
able to connect to a variety of Big Data store, like MongoDB and other
similar products.
 
Jim

----- Original Message -----
From: "Jim Dettman" <jimdettman at verizon.net>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Friday, April 10, 2015 3:27:26 AM
Subject: Re: [AccessD] Problem of a listbox's response on network... Part 1

Jim,

 <<In addition, the extra functionality and stability that ADO has far
outstrips DAO.>>

 Your mixing things up there; DAO vs ADO and using different data stores.
DAO in of itself does not cause corruption and it is no less stable than
ADO.

 It's the RDBMS that determines the stability, not the data lib (unless of
course it has an out right bug).

Jim.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Friday, April 10, 2015 02:16 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem of a listbox's response on network... Part 1

Hi Mark:

It depends on how much data you are working with. If you are strictly
comparing DAO interfacing with an MDB and/or you are comparing ADO's
performance via ODBC then, yes DAO runs quicker but... 

As soon as the amount of data starts approaching a few 100K of records,
and/or you are using ADO-OLE data connections and/or you are accessing a
professional level DB, like MS SQL, MySQL/MariaDB, Oracle, Postgrese and so
on then ADO performance pulls ahead dramatically.

In addition, the extra functionality and stability that ADO has far
outstrips DAO.

In summary, if I was creating a small network, in a stable environment, with
a limited set of data, DAO-MDB would be the database of choice but the first
moment the data corrupted, the connections started dropping and the number
of records grew to exceed 100K, it would be time to start thinking of an
upgrade.

Jim

----- Original Message -----
From: "Mark Simms" <marksimms at verizon.net>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Thursday, April 9, 2015 5:15:48 PM
Subject: Re: [AccessD] Problem of a listbox's response on network... Part 1

ADO = Slow D O.
DAO will be at least 2x faster.

> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Arthur Fuller
> Sent: Thursday, April 09, 2015 7:04 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Problem of a listbox's response on network...
> Part 1
>
> I would suggest that it is time for someone to write a test program
> that
> addresses either a publicly available table or one that can be
> manufactured
> easily by any interested party. The basic test would be to populate a
> listbox with 1000 rows of data from said table.
>
> Test 1, using a local attached MDB
> Populate the listbox using DAO
> Populate the listbox using ADO.
>
> Test 2, using an attached MDB on the network
> Populate the listbox using DAO
> Populate the listbox using ADO.
>
> Test 3, using an attached SQL Server database
> Populate the listbox using DAO
> Populate the listbox using ADO.
>
> Obviously results will vary given hardware, cabling, and Access
> version,
> but the results should be proportionally similar across all testbeds.
> SQL
> version, Any volunteers?
>
> Arthur
>
> P.S.
> Another idea also occurs to me, relevant only to the MDB on the
> network. I
> personally have never tried this, but the possibility exists: read the
> data
> once into a global array in the client MDB, and populate the listbox
> from
> there anytime it's needed. This would avoid all subsequent reads during
> the
> life of the program.
>
>
> On Wed, Apr 8, 2015 at 5:26 PM, Bill Benson <bensonforums at gmail.com>
> wrote:
>
> > Very interesting. My follow up would be, how much data can you fit
> into
> > your sportscar versus the 18 wheeler? I would say the sportscar can
> get
> > there faster but needs to take more trips...
> >
> > Seriously, where is the final analysis on this? John C is saying DAO
> is
> > present at all times directing traffic, yet Jim is saying that ADO is
> > faster than DAO.
> >
> > I am now thoroughly confused.
> >
> > On Wed, Apr 8, 2015 at 11:41 AM, Jim Lawrence <accessd at shaw.ca>
> wrote:
> >
> > > Hi Janet:
> > >
> > > Here is some questions answered about using ADO...1 of 3
> > >
> > > Regards
> > > Jim
> > >
> > > ----- Original Message -----
> > > From: "Jim Lawrence" <accessd at shaw.ca>
> > > To: "Access Developers discussion and problem solving" <
> > > accessd at databaseadvisors.com>
> > > Sent: Friday, January 24, 2014 11:40:10 PM
> > > Subject: Re: [AccessD] Problem of a listbox's response on
> network...
> > Part 1
> > >
> > > Hi Mark:
> > >
> > > It does depend on where your program is pulling data.
> > >
> > > There is no substitute for speed when a local DAO connection is
> pulling
> > > and displaying a single record or small group of records from a
> local MDB
> > > database but have a DAO connection download 15K of records from a
> remote
> > > server and fill a table with the results...
> > >
> > > An ADO connection can do that in one to two seconds. It is like
> comparing
> > > a sports car to an 8 wheel semi, when it comes to moving data.
> > >
> > > In addition, shut down the central MDB database a few times through
> out
> > > the day and you would be lucky not to corrupt your database. ADO
> type
> > > connections expect delays...rebooted a MS SQL and when it restarted
> the
> > ADO
> > > data stream continued processing.
> > >
> > > There are trade offs for sure; DAO is great for small 2 to a 50
> maximum
> > > number users, in stable environments but if you are using
> industrial
> > sized
> > > data, ADO is the only way to go.
> > >
> > > Jim
> > >
> > > ----- Original Message -----
> > > From: "Mark Simms" <marksimms at verizon.net>
> > > To: "Access Developers discussion and problem solving" <
> > > accessd at databaseadvisors.com>
> > > Sent: Friday, January 24, 2014 6:55:13 PM
> > > Subject: Re: [AccessD] Problem of a listbox's response on
> network...
> > Part 1
> > >
> > > Not to mention that ADO is SLOOOOWWW-D-O.
> > > Omigosh, I love the speed of DAO. Yes, AC2010 is a bit slower than
> > > AC2003....but so-be-it.
> > >
> > > > Excuse me?  DAO is the database engine AND (more importantly)
> object
> > > > model for all of Access.  DAO
> > > > is for programmers who need to program to the metal of forms,
> > > > querydefs, controls and so forth.  If
> > > > you use ADO, it is all a layer on top of DAO.
> > > >
> > > > I am not disagreeing that ADO has its place, but "for power
> users" is
> > > > just plain wrong.  There is
> > > > not an electron that flows through Access that DAO does not
> steer.
> > > >
> > > > John W. Colby
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
>
> --
> Arthur
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

More information about the AccessD mailing list