PDA

View Full Version : How to accelerate Access



mary.h
08-11-2004, 01:42 AM
Hi,
I've built a database in Access with frontend and backend db where all users have the frontend on their PC and accessing data which is hosted in the backend on a server computer.

The database task is to generate statistics as charts. That I've realized as reports with an OLE.Graph object. The code to retrieve and assembling the data runs in the frontend db.

Are there anything like "good practice" how to make Access code faster?
Something like prefer queries to sql or viceversa...

I am really lost here and would be very happy if somebody could give me hints even books, etc. where to get more info on this.

Kind regards,
mary.h

P.S. Hope you understand what I mean. I am not a native english speaker as you could guess :-)

Jacob Hilderbrand
08-11-2004, 02:04 AM
Check out the resources here on the site. There are a several listings for Access that may be of assistance.

http://www.vbaexpress.com/resources.htm#access

I am sure some other members will have some more specific info to help you out.

SJ McAbney
08-11-2004, 02:37 AM
Some suggestion regarding queries:

Use stored queries instead of creating them temporarily in SQL.
Only select the fields you need into the queries;
Ensure the appropriate fields are indexed;


As for code, what do you have?

VicRauch
08-11-2004, 06:38 AM
Another thing that will help your queries run faster is to, via the query Criteria, eleminate the unwanted data in the first query, or as soon as possible. Serveral years back I had a report on one customer that took 30 to 40 minutes to run. By selecting only the one customer in the first query, then doing the summary after having only the one customer, the report only took about 45 seconds to run. This client had a LOT of data.

Vic

deanziegler
08-11-2004, 09:55 AM
<agrees 100% with vicrauch .. i made same mistake too with cluttered queries / subqueries (data in queries on unrelated records i really didnt need at runtime ) and to use "intellegent joins" and "groupbys" .... thanks to a friend (DW) i learned to fine tune my query skills and my programs ran 100's of times faster .. its good advise

eed
08-11-2004, 10:31 AM
As for books you can reference for more information, the Access Developer's Handbook (Volume 1, Desktop Edition) by Getz, Litwin, and Gilbert has an entire chapter of tips and approaches for making your database run faster and more efficiently. I don't know what release of Access you're working with - I'm using the Access 2000 edition of the book, but I have the impression that the other editions of this book will probably contain similar information for the other Access releases.

Frankly, I am of the opinion that this book is a great tool anyway. But you will have to decide if it's worth the investment just for the "speeding" chapter. Tip: Do not pay full price at a typical bookseller until you have first checked at Half.com. I got my copy of the Access 2000 Developer's Handbook for only $25 (brand new, unused, unmarked, undamaged - just warehouse overstock).

Hope that gives you something to try. Good luck!