Microsoft Access database slow over WiFi?

MS-Access-over-Wi-Fi-network

Microsoft Access is able to be configured for multi-user functionality. Some people will simply put the whole database file into a shared folder, or on a network drive. Others will split the database into a front end and a backend, and put the front end on each user’s computer and link it to the backend, which will be on the shared folder network drive. The later method being the recommended configuration.

As a standard precaution in any Microsoft Access programming contract, I stipulate to my clients that any multi-user setup for Microsoft Access is only for up to 5 concurrent users maximum, that is 5 users actively using the database at the same time, and further, that those users should have physically wired connections. That means blue cable out the back of the computers. Any number of users can use an Access database if they just open MS Access, use the database, and then close Access, before the next user opens Access.

However, one thing that will limit Microsoft Access multi-user capability is a Wi-Fi network.

When Microsoft Access fetches data, any data, because it it just accessing a file, it needs to read the index of the tables pertaining to that query, or form, report, etc, and pass the indexes over the network. Then the computer can zero in on the actual requested record. That is extra traffic to pass across a network regardless of what filters, or query restrictions are applied.

All the ‘smarts’ happen on the actual computer running Microsoft Access, and not on the computer which is just storing the database. This means that all the data must be passed to the computer runnning Microsoft Access, then the filters and query restrictions are applied on this computer, after all the data has passed over the network.

Over a modern fast network with 1Gb or even 100Mb speed, this probably will not matter, not unless the database is massive.

However, over a Wi-Fi network, which, until recently, has a considerably slower speed, it takes much longer to pass all that data.

On a Wi-Fi network the speed for each user is shared, regardless of whether any other users are using Microsoft Access.

The bottom line is if you are using Microsoft Access in a multi-user situation use a wired connection if you can.

If you only have 1 or 2 users that need to use the database over Wi-Fi, then ensure that the wireless router and PC network cards are up-to-date (purchased since 2016). They should use 802.11AC protocol

There is one other option if you might be stuck in a heritage house where a cable run is impossible. It involves installing Microsoft SQL server express on the computer that is serving the file. Also read my article on 9 Speed Hacks for Microsoft Access

 

Share on Google+Share on FacebookTweet about this on TwitterBuffer this pageShare on LinkedInEmail this to someoneDigg thisPin on Pinterest