Access Spped over a Wide Area Network

ru1 · March 16th, 2006, 12:45 PM

Hi Everyone. I have created a monster. Well, sort of. Here is the deal. I have pushed down to everyones desktop an Access database with forms, reports, and queries. They are all linked to an Access database that sits on our home server with just the tables and relationships.

Now, it all works great, but performance is a problem. I have all my tables indexed, so now I don't know how to improve on this performance problem.

Any suggestions would be appreciated.

Thanks,

Ru1

Loralee · March 17th, 2006, 11:59 AM

Chuck,

If you haven't been through the archives you may find some of those answers in this thread: http://p2p.wrox.com/topic.asp?TOPIC_ID=5188

HTH,
Loralee

Bob Bedell · March 17th, 2006, 12:50 PM

Hi Loralee and all,

Since http://p2p.wrox.com/topic.asp?TOPIC_ID=5188 is getting to be a couple of years old and gets revisited every now and then, I just wanted to do a little house cleaning and update a few things.

First, the question gets asked:

Does ADO bring all of the table data to the client when Queried?

While I answered âYesâ initially, the correct answer would be âNot necessarily.â John Viescasâ post (the last post in the thread) makes the extremely important point that:

Quote:

quote:
You must also design your application to minimize the data fetched to the client machine. It is NOT true that JET on the client always does a full table scan. If you apply a filter on an indexed field, JET first fetches the index blocks and then sends specific requests to the file server for the data pages it needs to satisfy the request. So, it's important to both define appropriate indexes on your tables AND to NEVER request "all the tea in china" by opening a form or report unfiltered.

Second, I understood at the time that it is not possible to bind an ADO recordset to a continuous form. Turns out there is a solution to this problem, which appeared on theis forum recently at:

http://p2p.wrox.com/topic.asp?TOPIC_ID=5029

Hth,

Bob