Speeding up access on a network

Jimbo Razza

Board Regular
Joined
May 21, 2005
Messages
114
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Board Regular
Joined
May 21, 2005
Messages
114
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,452
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,181,421
Messages
5,929,805
Members
436,696
Latest member
Mr Rice

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top