Jim Dettman
jimdettman at verizon.net
Thu Jul 5 09:25:22 CDT 2007
Mark, You hit on a key point; with JET, and a well designed and written app, it's not performance that is an issue, but one of corruption. With a typical read/write app, by the time you get past 40 or so users, it's almost impossible to keep the environment stable enough to prevent corruptions from occurring. Case in point, years ago I was aware of one JET based reporting/ready only app that ran perfectly fine with 200+ users. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Thursday, July 05, 2007 10:12 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Performance tips anyone? I've been able to handle about 40(on a LAN)...lot of transactional stuff...but with more than 40 I seemed to end up with corruption every other day. I had about a 1 second screen transition. Mark A. Matte >From: "jwcolby" <jwcolby at colbyconsulting.com> >Reply-To: Access Developers discussion and problem >solving<accessd at databaseadvisors.com> >To: "'Access Developers discussion and problem >solving'"<accessd at databaseadvisors.com> >Subject: Re: [AccessD] Performance tips anyone? >Date: Thu, 5 Jul 2007 00:29:03 -0400 > >Anita, > > >I think that 10-12 users is the absolute maximum you will get out of an >Access database it will continue to get slower over the years as the >database grows. At some stage you would probably have to upgrade to SQL >server. > >Errrrr! Wrong answer. > >I have a database with 25 users in the database every day. The BE is >currently about 800 mbytes. This BE has tables with hundreds of thousands >of records in some tables, 30K-50K records in the main tables (claimant / >claim). I open a VERY complex tabbed form with about 20 tabs on it, with >subforms on each tab (JIT subforms). > >Users on fast machines open the form in about 1.2 seconds. Users on very >old slow machines take about 5 to 6 seconds. > >Speed of the individual workstation is the single largest determinate of >acceptable speed. A high speed processor and LOTS of memory (1 gig for >Windows XP Pro) are essential. Moving to a 1 gbit lan made a big >difference >as well (which requires a gigabit NIC in the machines as well). > >And of course all of the things you mentioned in terms of FE optimizations. > >John W. Colby >Colby Consulting >www.ColbyConsulting.com >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Anita Smith >Sent: Wednesday, July 04, 2007 9:00 PM >To: Access Developers discussion and problem solving >Subject: Re: [AccessD] Performance tips anyone? > >Annie, >There are a number of things you can do to speed up an Access Database. I >haven't looked at the link you provided, but here are a few suggestions >based on my experience over the years: > >* Make sure your tables contain indexes on the fields frequently used for >criteria in queries >* Put the criteria on the ONE side of the relationship in queries >* Don't open forms and reports with large underlying recordsources. If >possible open the forms with only 1 underlying record - not a whole table. >* I have found that opening a form specifying the procedure arguments makes >it open faster > DoCmd.OpenForm "MyForm", acNormal, , , acFormEdit instead of > DoCmd.OpenForm "MyForm" > >Upgrading to A2003 probably won't make much difference. > >Using Runtime mode should not make it run any slower either. > >SQL server Express is a great option, but you would probably have to >rewrite >the form and report recordsources to really take advantage of it. It is not >hard to set up. I think there is an upgrade wizard that will upgrade an >Access Database to SQL Server. > >I think that 10-12 users is the absolute maximum you will get out of an >Access database it will continue to get slower over the years as the >database grows. At some stage you would probably have to upgrade to SQL >server. > >I hope this helps a bit. > >Anita > >-- >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migratio n_HM_mini_2G_0507 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com