There are several ways that you can share an Access database depending on your needs and resource availability. In this article, we’ll take a look at the options available, the benefits of each option, and resources for more information.
In this article
An overview of ways to share Access data
Database applications change and grow over time. Many factors impact needs and performance including the number of concurrent users, the network environment, throughput, latency, the size of the database, peak usage times, and expected growth rates. In short, if your database solution is successful, it probably needs to evolve. Fortunately, Access has an evolutionary path, from simple to advanced, that you can take over time to effectively scale your solution. The following table summarizes Access scenarios and workloads to help you choose that path.
Share a single database
This is the simplest option and has the least requirements, but also provides the least functionality. In this method, the database file is stored on a shared network drive, and all users share the database file simultaneously. Some limitations include reliability and availability if there are multiple simultaneous users changing data since all database objects are shared. This technique can also reduce performance as all the database objects are sent across the network.
This option might work for you if only a few people are expected to use the database at the same time and users don’t need to customize the design of the database. But this method is less secure than other methods of sharing a database, because each user has a full copy of the database file, increasing the risk of unauthorized access.
To share a database by using a shared folder:
-
In a home or small business environment, share a folder with specific people. For more information, see File sharing over a network in Windows.
-
Make sure that Access is set to open in shared mode on all of the users' computers. This is the default setting, but you should check to be sure — if a user opens the database in exclusive mode, it will interfere with data availability.
-
Start Access and under File, click Options.
-
In the Access Options box, click Client Settings.
-
In the Advanced section, under Default open mode, select Shared, click OK, and then exit Access.
-
-
Copy the database file to the shared folder. After you copy the file, make sure that the file attributes are set to allow read/write access to the database file. Users must have read/write access to use the database.
-
On each user's computer, create a shortcut to the database file. For more information, see Create a desktop shortcut for an Office program or file.
Share a split database
This is a good choice if you do not have a SharePoint site or a database server. You can share a split database over a Local Area Network (LAN). When you split a database, you reorganize it into two files — a back-end database that contains the data tables, and a front-end database that contains all the other database objects such as queries, forms, and reports. Each user interacts with the data by using a local copy of the front-end database.
The benefits of splitting a database include the following:
-
Improved performance Only the data is shared across the network not the tables, queries, forms, reports, macros and modules.
-
Greater availability Database transactions such as record edits are completed more quickly.
-
Enhanced security Users access the back-end database through linked tables; it is less likely that intruders can obtain unauthorized access to the data via the front-end database.
-
Improved reliability If a user encounters a problem and the database closes unexpectedly, any database file corruption is usually limited to the copy of the front-end database that the user had open.
-
Flexible development environment Each user can independently develop queries, forms, reports, and other database objects without affecting other users. You can also develop and distribute a new version of the front-end database without disrupting access to the data that is stored in the back-end database.
For more information, see Split an Access database.
Share data on a SharePoint site
There are several ways to share Access data on a SharePoint site:
-
Linking The linking process connects to data in another program, so that you can view and edit the latest data both in SharePoint and in Access without creating and maintaining a copy of the data in Access. If you don't want to copy a SharePoint list into your Access database, but instead want to run queries and generate reports based on the contents of that list, you can link to the data.
-
Moving When you move data from Access to a SharePoint site, you create lists on the SharePoint site that remain linked to tables in your database. The Export Tables to SharePoint Wizard helps you to move the data from all your tables at the same time and to maintain their relationships.
For more information, see Import, link, or move data to SharePoint.
Warning Although you can save an Access database file to OneDrive or a SharePoint document library, we recommend that you avoid opening an Access database from these locations. The file may be downloaded locally for editing and then uploaded again once you save your changes to SharePoint. If more than one person opens the Access database from SharePoint, multiple copies of the database may get created and some unexpected behaviors may occur. This recommendation applies to all types of Access files including a single database, a split database, and the .accdb, .accdc, .accde, and .accdr file formats. For more information on deploying Access, see Deploy an Access application.
Share data by using a database server
You can use Access with a database server product such as SQL Server to share your database. This method offers you many benefits, but does require additional software — a database server product.
This method is similar to splitting a database because the tables are stored on the network, and each user has a local copy of an Access database file that contains links to the tables, along with queries, forms, reports, and other database objects. Benefits of this sharing method depends on the database server software that you use, but generally include user accounts and selective access to data, excellent data availability, and good integrated data management tools. Moreover, most database server software works well with earlier versions of Access, so not all your users must use the same version. Only tables are shared. For more information, see Migrate an Access database to SQL Server, Import or link to data in an SQL Server database, and Link to or import data from an Azure SQL Server Database.
Benefits of sharing a database by using a database server
-
High performance and scalability In many situations, a database server offers better performance than an Access database file alone. Many database server products also provide support for very large, terabyte-sized databases, approximately 500 times the current limit for an Access database file (two gigabytes). Database server products generally work very efficiently by processing queries in parallel (using multiple native threads within a single process to handle user requests) and minimizing additional memory requirements when more users are added.
-
Increased availability Most database server products allow you to back up your database while it is in use. Consequently, you do not have to force users to exit the database to back up data. Moreover, database server products usually handle concurrent editing and record-locking very efficiently.
-
Improved security No database can be made completely secure. However, database server products offer robust security that will help protect your data from unauthorized use. Most database server products offer account-based security, allowing you to specify who can see which tables. Even in the event that the Access front-end is improperly obtained, unauthorized use of data is prevented by account-based security.
-
Automatic recoverability In case of system failure (such as an operating system crash or power outage), some database server products have automatic recovery mechanisms that recover a database to the last state of consistency in a matter of minutes, with no database administrator intervention.
-
Server-based processing Using Access in a client/server configuration helps reduce network traffic by processing database queries on the server before sending results to the client. Having the server do the processing is usually more efficient, especially when working with large data sets.
-
Azure SQL Server In addition to the benefits of SQL Server, offers dynamic scalability with no downtime, intelligent optimization, global scalability and availability, elimination of hardware costs, and reduced administration.
