Will an Access DB be faster if I split it - leaving the tables in the server and the rest locally?

Question

I currently have an Access db stored in a server, so every time a user has to used goes to the network location and open the access file.

Queries, forms, exports and everything is really slow. If I save the Access file in my local drive, its speed is extremely improved.

Will the overall experience of my users improve if I split the database and leave the tables in the network location and everything else is stored locally in the hdd of the user?

UPDATE: Different users are not using the DB at the same time.

Answer 1

As a general rule no. (Assuming in both cases the accDB file is on a server folder).

So the fact of being split or not does not really change the “data” that flows down the network pipe.

However, the forms, VBA code etc. when installed on each workstation does NOT have to traverse the network pipe, so in this fashion you can save some network bandwidth and some speed gains. However, in most cases the form load time is VERY small – the pulling of the data represents the bottle neck and slowdown.

So a split system with a front end installed on each workstation will NOT change the data speed, or the amount of data pulled over the network. However since the applcatation part is loaded local, then those parts will load faster since they never traverse the network pipe.

However, when you use Excel, you install that applcatation on each workstation (you might share some data/document file on the server).

And when you use Word, you again install that application on each workstation (you might share some data/document files on the server).

So for the last 30 years of the computer industry, you in general install he applcatation part on EACH desktop. Now that you are writing and building an applcatation, then once again you install that applcatation on each computer like everything else. So you want to keep in mind the difference between some data or data file, and that of application code you create and develop to run on each workstation.

And the above increases reliability by large amounts, since if one user has a code or a form freeze up, then all other users can continue to work. If all users share the same application code, then one mess up can cause everyone to stop working.

So from a data point of view, the answer is no. And there is some "overhead" with linked tables as opposed to non split. So sometimes you see some slowdown over non split. However, from a maintains and reliability point of view, splitting is high recommended. And for form + code heaving applications, then you do see some speed up since forms + code is loaded local as opposed to those applications forms and code being pulled across the network pipe.

Answer 2

If you have multiple users opening the same shared file over a network you are asking for corruption and many other issues.

In a multi user set up you should always split your database, and each user should have there own locally saved copy.

This will probably also help with your speed issues, as currently you are dragging all the form information and data across your network. Properly designed forms and queries will only pull in the minimum data required for the task, also reducing network traffic and load times