Speeding up access on a network

[Jimbo Razza]

I've created a database that is being accessed by multiple people over a network and some of the users are experiencing lag due to their physical location having a slower connection on the network.

I'm investigating ways to speed up the performance of the database and one article I found suggested:

"Share only the tables in the Access database You can put only the tables on a network server, and keep other database objects on users' computers."

Are they simply talking about linked tables? Or is there another way to achieve this? Experiences I've had in the past with linked tables over a network have not been much better.

 

[SydneyGeek]

The first part of sharing a DB is splitting into front and back ends. Just doing that won't necessarily speed up user response times but it is essential for reducing corruption.

Things to look at are:
1. Back end on a network drive, ALL users with a separate front end on their local machine. This will reduce network traffic because only data flows to each machinge, not the information required for the graphical interface.

2. Never let users browse all records in a form. Always show filtered data sets. This can be achieved in a number of ways; one is to provide a search form which then launches the main data form.

3. If you have combo boxes with long lists, consider either (a) placing the lookup tables in teh front end DBs if the records don't change too frequently, or (b) only populating the lists once users have typed 3 or more characters.

There are other ideas, but these will help.

Denis

 

[Jimbo Razza]

Cool

But how do I create a front end and back end database?

Are they just individual access databases with the back end holding the tables and data and the frontends having linked tables and the queries and forms?

 

[SydneyGeek]

Yes. If you haven't done it before, go to Tools > Database Utilities > Split Database and follow the prompts. The Wizard will do the work for you and create the links to the back end.

Denis

 

[Andrew Fergus]

Please correct me if I'm wrong Denis but isn't the process of splitting a database irreversible? If so, then I recommend you take a copy of your database first.

Andrew

 

[SydneyGeek]

MS tells you it's irreversible but in practice it's simple.

In the front end:
1. Delete every table link
2. File > Get External Data > Import, browse to the back end, and pull in all the tables.

Denis