[AccessD] Performance tips anyone?

Replace any DoCmd.RunSQL's with CurrentDB.execute.

Steve


-----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




On 7/5/07, Annie Courchesne, CMA <accma at sympatico.ca> wrote:
>
> Hi all,
>
>
>
> I have a customer that complains about his database (BE/FE A97 running 
> in runtime mode) is slow.  The number of concurrent user keep growing 
> over the years and it's up to 10 or 12 now.
>
>
>
> What I'm looking at right now is to optimize the whole database and 
> upgrade to Access 2003.  I've look at the performance tips from this 
> page
> (http://www.granite.ab.ca/access/performancefaq.htm) and I've found 
> some pretty usefull information.
>
>
>
> Anyone has other tips on getting this database more performing?
>
>
>
> I was also wondering if using a dedicated server for the database 
> would help to improve performance?
>
>
>
> And what about SQL Server 2005 Express?  I've read here that it's free 
> and has a large capacity (more than enough for what I need).  Will it 
> really help in speeding up the database?  How hard is it to set up?  
> Any good documentation I can read on this?
>
>
>
> Thanks to all of you!
>
>
>
>
>
> Annie Courchesne, CMA
>
>
>
> --
> 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