Microsoft Access, Microsoft’s relational database made for small- and medium-sized businesses, is an excellent database that can be optimized to be used by multiple users on a network.
Many users use Microsoft Access on an individual computer and can be more productive when the database is networked. Once networked, the database can be used by multiple users and can update in real-time.
When using Access on a network, there are ways to optimize both the database and the network to make the user’s experience better. From the standpoint of a network administrator, this article is written assuming the end user has basic computer skills. The novice user should find this relatively easy.
A Basic Understanding of Networking and Security
Before networking an Access database, you should understand that security is the most important thing to be concerned with.
Your first step is to locate the Access database you want to network. After locating the database, move the database by copying it to a folder on the computer you want to act as a server computer.
On the server computer, create a user for each person who will be using the database.
In Windows 7, right click on the folder and select Share With, Specific People.
Next select the users and give permissions (Read or Read/Write) to the folder.
These permissions should be set based on the level of trust you have in the users of the database. Someone with Read permissions will not be able to update, add or delete information from the database.
(Example of permissions)
After you give permissions, you will see a path that allows other users to be networked to the computer.
For other users to use the computer, they would enter \\servername\foldername in the run or search box in Windows and hit the enter key. This should bring up the database icon. Once this is done, the end user can double-click on the database and use the database according to the permissions that were set.
Improving Networking Performance
Databases will grow in size. You can optimize your database by shrinking the database every time it is closed. (This tip is also good for stand-alone databases that are not networked.)
To shrink your database -
Office 2003 / 2007
- Click on Tools
- Select Compact on Close
- Click on File
- Current Database
- Select Compact on Close
This option ensures the size of your database is optimized when opened across a network.
Please continue to the next page for more Microsoft Access network tips.
Splitting a Database
By splitting a database, you create a ‘frontend’ database that connects across your network that connects to the main database. This option is the optimal way to network a database and requires some analytic skill in understanding the concepts.
- On your pc, make a copy of the Microsoft Access database that you want to split.
- Start with the database file on your local hard drive, not on the network share you created. Don’t allow anyone to work with the database at this time. If the database file is currently shared from your local hard disk drive, you can leave it where it is.
- Open the copy of the database that is on your local hard disk drive.
- On the Database Tools tab in your open database, navigate to the Move Data group, now click on Access Database.
- At this time the Microsoft Access Database Splitter Wizard will start.
- Click on Split Database.
- You will then see a Create Back-end Database dialog box; specify a name for the database and a file type, and you will see a location for the back-end database file (where you put the main database).
Basically you will have a small ‘frontend’ database that maintains information as the user uses the database locally. The information is then transferred and stored on the back-end database.
In a normal network scenario, if you double-click on a large database, you will see or experience a delay. By splitting the database, performance is improved. More information on splitting databases can be found on Microsoft’s support page.
Optimizing Your Computers on a Network with Access
When networking your computers, you should be aware of how you are connected. If your computers are on a 1 gigabit network (1000 mbps), you should have gigabit network cards on your computer (assuming the computers are wired). All devices in your network should be the same. In other words, if you have network cards that are 100mbps network cards, you should use a 100 mbps switch.
Because many devices are wireless, you should use 802.11n wireless cards and an 802.11n wireless router or access point. Wireless cards that are rated 802.11b are too slow for large Access databases. Cards should be 802.11g (54 mbps) as a minimum. The 802.11n as mentioned above is rated for 150+ mbps and is ideal when connecting wirelessly.
On wired networks, you should check the Speed and Duplex settings on your network card (found under the Device Manager). Although most are set to Auto Detect, Auto or Auto Sense, you should adjust these to Full or Half and see what your optimal time is when you open the database across the network. When making any adjustments to your network, ensure the database to prevent any data loss.
Note: Databases should be backed up often and stored ‘off-site’ to prevent the loss of data in the event your computer crashes or in case of natural disasters.