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

Fri Apr 10 01:30:44 CDT 2015

Yes... This has been my experience too.  Personally I was say always use ADO over DAO.

If you are ONLY using the local copy of Access and the data set is small than DAO is ok.  I always use ADO regardless as then it is easy to swap the connection to SQL Server or whatever in the future in things start to outgrow MS Access, which (for me at least) is a frequent occurrence. 

That is just my experience.  This stuff is a bit like religion.  Everyone has an opinion, they all say there 'version' of the truth is best and want to join their 'team', but in reality none of them are perfect and much of it is just babble.  There is no universal truth, just what works for you at the time.  Tech also changes over time, so what was great back then is perhaps not so good now.

I firmly fall into the ADO and unbound control camp when it comes to Access databases, which is pure heresy for some folks. But you know.  Go with what works for you.  Pro and cons for both ADO and DAO.

I like the power, flexibility and scalability of ADO.  Same reasons I prefer an Android phone over an iPhone I guess (another tech theological battle ground I know...).

I have many good reasons to favour ADO over DAO, but some other folks have just as many reasons to go the other option as well.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Friday, 10 April 2015 4:16 PM
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