[AccessD] Performance tips anyone?

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