[AccessD] ..Performance ADO - DAO ..

Rocky Smolin - Beach Access Software bchacc at san.rr.com
Tue Oct 21 11:45:22 CDT 2003


Thank would be much appreciated.  I looked through it once and tried to do
the mod but failed.

Rocky

----- Original Message ----- 
From: "John Colby" <jcolby at colbyconsulting.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Tuesday, October 21, 2003 9:20 AM
Subject: RE: [AccessD] ..Performance ADO - DAO ..


> I thought I'd modded his code and placed the modified version on my site.
> I'd have to look though.  I know I did modify it for my own use.
>
> John W. Colby
> www.colbyconsulting.com
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin -
> Beach Access Software
> Sent: Tuesday, October 21, 2003 10:50 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] ..Performance ADO - DAO ..
>
>
> Andy:
>
> I use Tribble's relinker.  Is there a simple mod to his code which would
> accomplish this?
>
> Thanks and regards,
>
> Rocky Smolin
> Beach Access Software
>
> ----- Original Message -----
> From: "Andy Lacey" <andy at minstersystems.co.uk>
> To: "'Access Developers discussion and problem solving'"
> <accessd at databaseadvisors.com>
> Sent: Tuesday, October 21, 2003 4:43 AM
> Subject: RE: [AccessD] ..Performance ADO - DAO ..
>
>
> > Absolutely endorse this. I've got a big MDB with lots of tables. It was
> > taking about 20 MINUTES to relink before I kept a pointer to the data
> > open. Now does it in 10-15 SECONDS. Massive difference.
> >
> > Andy Lacey
> > http://www.minstersystems.co.uk
> >
> > > -----Original Message-----
> > > From: accessd-bounces at databaseadvisors.com
> > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> > > Stuart Sanders
> > > Sent: 21 October 2003 11:06
> > > To: 'Access Developers discussion and problem solving'
> > > Subject: RE: [AccessD] ..Performance ADO - DAO ..
> > >
> > >
> > > Not necessarily true...
> > >
> > > For example if you attempt to relink or refresh links on a
> > > multiuser backend, maintaining an open db connection to the
> > > db can save minutes.  I've seen a 5 minute relink operation
> > > without an open connection go down to 15 seconds with an open
> > > connection.  As opposed to opening and closing the BE db for
> > > each table to be relinked.
> > >
> > > Stuart
> > >
> > > > -----Original Message-----
> > > > From: accessd-bounces at databaseadvisors.com
> > > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Haslett,
> > > > Andrew
> > > > Sent: Tuesday, 21 October, 2003 5:39 PM
> > > > To: 'Access Developers discussion and problem solving'
> > > > Subject: RE: [AccessD] ..Performance ADO - DAO ..
> > > >
> > > >
> > > > I believe you are don't it correctly IMO.
> > > >
> > > > There is negligible performance loss in closing and re-opening
> > > > connections that I'm aware of.
> > > >
> > > > Cheers,
> > > > A
> > > >
> > > > -----Original Message-----
> > > > From: marcel.vreuls at achmea.nl [mailto:marcel.vreuls at achmea.nl]
> > > > Sent: Tuesday, 21 October 2003 6:37 PM
> > > > To: Access Developers discussion and problem solving
> > > > Subject: [AccessD] ..Performance ADO - DAO ..
> > > >
> > > >
> > > > Dear group,
> > > >
> > > > Just to let you know. Turning of the subdatasheets and keeping the
> > > > connection open shows the following figure.
> > > >
> > > > 10MB network (both tables contain about 15.000 records)
> > > >      Reading a customertable       was 10 seconds  now 3 seconds
> > > >      Reading a order table               was 7 seconds
> > > > now 2 seconds
> > > >
> > > > 100 MB network (both tables contain about 15.000 records)
> > > >      Reading a customertable       was 3 seconds        now
> > > 1 seconds
> > > >      Reading a order table               was 2 seconds
> > > > now 1 seconds
> > > >
> > > >
> > > > In the replies someone asked why I closed all database connections
> > > > after use. I do this to keep memory use to a minimum. When I keep
> > > > those database
> > > > connections open at a certain time the database gives memory
> > > > errors. But
> > > > reading and thinking I just thought of  something. The structure I
> > > > currently use is
> > > >
> > > >    Function fncUpdateOrderStatus() as boolean
> > > >       Dim db as dao.databse
> > > >       Dim rs as dao.recordset
> > > >
> > > >       set db = currentdb()
> > > >
> > > >       > the works>
> > > >
> > > >       db.close
> > > >       rs.close
> > > >       set db = nothing
> > > >       set rs= nothing
> > > >    end function
> > > >
> > > >
> > > > So I define the db many, many times and close it also. Is
> > > it better to
> > > > dim the db as global-public variable and use this in each
> > > > function???????? so
> > > > in each function i can remove the Dim db and close.db???
> > > >
> > > > Gr. marcel
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Charlotte Foust"
> > > <cfoust at infostatsystems.com>@databaseadvisors.com op
> > > > 20-10-2003 17:47:40
> > > >
> > > > Antwoord aub aan Access Developers discussion and problem solving
> > > >       <accessd at databaseadvisors.com>
> > > >
> > > > Verzonden door:     accessd-bounces at databaseadvisors.com
> > > >
> > > >
> > > > Aan:  "Access Developers discussion and problem solving"
> > > >       <accessd at databaseadvisors.com>
> > > > cc:
> > > >
> > > > Onderwerp:     RE: [AccessD] Performance ADO - DAO or ??
> > > >
> > > >
> > > > Two things that bog down Access 2000 and 2002 are Name
> > > Autocorrect and
> > > > Subdatasheets.  If you turn those two things off, you'll see an
> > > > improvement in performance.  Subdatasheets also need to be
> > > turned off
> > > > in the backend.
> > > >
> > > > Charlotte Foust
> > > >
> > > > -----Original Message-----
> > > > From: marcel.vreuls at achmea.nl [mailto:marcel.vreuls at achmea.nl]
> > > > Sent: Monday, October 20, 2003 3:37 AM
> > > > To: Access Developers discussion and problem solving
> > > > Subject: [AccessD] Performance ADO - DAO or ??
> > > >
> > > >
> > > > Dear group,
> > > >
> > > > It has been a while since I looked into this group. My daughter (7
> > > > months) took up most of my time recently. But work goes on
> > > so I would
> > > > like to ask
> > > > you the following.
> > > >
> > > > I am strugling with the following. I have about 6 access 2000
> > > > applications  distributed among several customers. All database use
> > > > DAO and have a FE
> > > >  and BE. It works fine but know customers start complaining about
> > > >  performance. Expecially on a network envirnement. I have
> > > >  - Looked through all the queries and changed all the SELECT
> > > > *  queries
> > > >  with only the data wich is used
> > > > - closed all recordset and database connection at the end of
> > > > eacht function
> > > > of module.
> > > > -  Removed databound forms as much as possible. This is a lot
> > > > of work and I
> > > > do not know if i want to do this.
> > > >
> > > > But this all did not improved much.
> > > >
> > > > I am thinking about the following
> > > > - Is it a option to move to ADO instead of DAO. Does this improve
> > > > performance?. In my tests it does not matter much but perhaps I am
> > > > missing something
> > > > - The performance analyser in Access tells me to use fewer
> > > > controls on a
> > > > form. If I do this does this improve performance
> > > > - moving to sql server. But this will cost my customer a lot
> > > > of money and
> > > > the won´t be happy.
> > > >
> > > >
> > > > Any suggestions, ideas??
> > > > I am thinking to rewrite the programms to VB, anyone
> > > experience, tips,
> > > > tricks in this.
> > > >
> > > > Thansk marcel
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "dave sharpe" <davesharpe2 at cox.net>@databaseadvisors.com op
> > > 19-10-2003
> > > > 06:32:54
> > > >
> > > > Antwoord aub aan Access Developers discussion and problem solving
> > > >      <accessd at databaseadvisors.com>
> > > >
> > > > Verzonden door:     accessd-bounces at databaseadvisors.com
> > > >
> > > >
> > > > Aan:  "Access Developers discussion and problem solving"
> > > >      <accessd at databaseadvisors.com>
> > > > cc:
> > > >
> > > > Onderwerp:     Re: [AccessD] ado recordcount
> > > >
> > > >
> > > >
> > > > John - This article may be of benefit
> > > > Dave
> > > >
> > > > PRB: ADO: Recordcount May Return -1
> > > >
> > > > http://support.microsoft.com/default.aspx?scid=http://support.
> > > > microsoft.com: 80/support/kb/articles/q194/9/73.asp&NoWebContent=1
> > > >
> > > >
> > > >
> > > > ----- Original Message -----
> > > > From: "John Colby" <jcolby at colbyconsulting.com>
> > > > To: "AccessD" <AccessD at databaseadvisors.com>
> > > > Sent: Saturday, October 18, 2003 11:38 PM
> > > > Subject: [AccessD] ado recordcount
> > > >
> > > >
> > > > I am opening a recordset (raw table) that contains records.
> > > > Move last, move
> > > > first. EOF and BOF are both false.
> > > >
> > > > Recordcount = -1
> > > >
> > > > What gives?  How do I tell how many records in the recordset?
> > > >
> > > > John W. Colby
> > > > www.colbyconsulting.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
> > > >
> > > >
> > > > *****************************************************************
> > > > Dit bericht is bij binnenkomst gecontroleerd op de
> > > > aanwezigheid van virussen. Er zijn geen (bekende)
> > > > virussen gevonden.  Active
> > > > *****************************************************************
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ********************* DISCLAIMER *********************
> > > > De informatie in dit e-mail bericht is uitsluitend
> > > > bestemd voor de geadresseerde. Verstrekking aan
> > > > en gebruik door anderen is niet toegestaan.
> > > > Door de electronische verzending van het bericht
> > > > kunnen er geen rechten worden ontleend aan de
> > > > informatie.
> > > > ************************************************************
> > > >
> > > > _______________________________________________
> > > > 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
> > > >
> > > >
> > > > *****************************************************************
> > > > Dit bericht is bij binnenkomst gecontroleerd op de
> > > > aanwezigheid van virussen. Er zijn geen (bekende)
> > > > virussen gevonden.  Active
> > > > *****************************************************************
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ********************* DISCLAIMER *********************
> > > > De informatie in dit e-mail bericht is uitsluitend
> > > > bestemd voor de geadresseerde. Verstrekking aan
> > > > en gebruik door anderen is niet toegestaan.
> > > > Door de electronische verzending van het bericht
> > > > kunnen er geen rechten worden ontleend aan de
> > > > informatie.
> > > > ************************************************************
> > > >
> > > > _______________________________________________
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > > >
> > > > IMPORTANT - PLEASE READ ********************
> > > > This email and any files transmitted with it are
> > > confidential and may
> > > > contain information protected by law from disclosure.
> > > > If you have received this message in error, please notify the sender
> > > > immediately and delete this email from your system.
> > > > No warranty is given that this email or files, if attached to this
> > > > email, are free from computer viruses or other defects. They
> > > > are provided on the basis the user assumes all responsibility for
> > > > loss, damage or consequence resulting directly or indirectly from
> > > > their use, whether caused by the negligence of the sender or not.
> > > > _______________________________________________
> > > > 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