Ways to Share MS Access Database in Multi-User Environment

This article provides methods to share an Access database, and the other factors that should be consider while selecting best option. Also, recommended a best solution when Microsoft Access database corruption issues occurs.

Overview

Microsoft Access database is a collection of various database objects which includes tables, queries, forms, macros, reports, etc. These database objects helps user to use the stored data within tables also to update, delete, insert data in the table. When it comes to share the database, in most of the cases user need to share the tables, because the whole data is stored within tables. The file format in which these data stored is MDB File format in MS Access 2003 & below version and ACCDB file format in MS Access 2007 & above versions. It should be there in mind while sharing tables that is to whom the data is shared must be using the same table. Queries, forms, reports, macros, etc. do not contain the original data, so not necessary everyone should use the same database these data objects.

There are various ways to share Microsoft Access database. Depending on user’s needs and the resources available with them, they can select best way to share MS Access database among the variety of options for sharing Access database.

Methods Used to Share MS Access Database

Split Database

This option user can use for sharing Microsoft Access database when they do not have a SharePoint site or other database server. When a database is split, database is reorganised into two database files a back-end database file and a front-end database file. Back-end database file contains data tables and font-end database file contains all other MS Access database objects (forms, queries, reports, etc.). And when data is shared every user with whom data is shared get a copy of front-end database. After using Split database wizard to Split MS Access database. The front-end database file can be shared with other users.

Share Microsoft Access Database Over Network using Network folder

Sharing Access database using a network folder is simplest way of sharing database. Mostly this method is not recommended because it less secure then other methods as each user has full copy of database and this will increase the risk of unauthorised access. Also, this method has several limitations such as at the same time only few may able to use database, user need not to customize design of database.

Simple Steps to share a database by using a network folder

• Set-up a shared network folder
• Check that MS Access is open in shared mode for all users
• User must have read/write permission to access database. Copy database file to Shared folder
• Short-cut of database files should be created on user’s system

These were the basic steps for sharing a MS Access file using network folder.

Sharing MS Access Database on a Sharepoint Site

For sharing database on a Sharepoint, Windows Sharepoint services must be running on the server. Sharepoint features helps to access an application from different location which makes data available for user through web browser. User can do this in two ways by publishing Access database or by moving tables to Sharepoint lists and then link with MS Access database. In this way by moving tables to lists each user can easily modify there copy of database where as in publishing this benefits cannot be taken.

To Publish a database on Sharepoint, MS Access database files should be save in MS Access (.accdb file) file format.

To Move data to Sharepoint list and link to them user has to do three steps:

• Move Access data to Sharepoint lists
• Create link for these lists
• Distribute these links to other users
• Install other drivers on system which will help to use Access with other database server

User can also use Move to Sharepoint Wizards to create link.

Use Access with SQL Server or Other Database Server

To share Microsoft Access database user can use MS Access with SQL Server or other database server. For this they need other database server product like SQL Server. This method works as Split database works. The database is split in which the tables are stored and each user has the copy of database in which links of the tables along with macros, queries, reports, etc. are there. These database server are optimised to share data among many user. Benefits of database server may vary like, SQL Server supports very large database which is much more higher then the MS Access alone. Works more efficiently by performing queries in parallel. Use Access with SQL Server is a great choice.

Basic steps are shown here to use an Access database with database Server:

• Move data from MS Access to other database Server
• Link database server tables with MS Access files
• Create a valid user account on Server

Conclusion

Various option to share MS Access database file was discussed above. User can easily choose a suitable option, according to there needs and resources available to them. Sometimes user has to face some corruption issues in the shared MS Access (MDB/ACCDB) file while sharing Access database files over a network, or on Sharepoint, or on splitting database, or on sharing with other database server to combat with these type of corruption user need a best and reliable solution. And the recommended solution is repair corrupt database files with MS Access database repair tool.