You can share an Access database in several ways, depending on your needs and available resources. This article looks at the available options, the benefits of each one, and places to learn more.
In this article
- An overview of ways to share Access data
- Share a single database
- Share a split database
- Share data on a SharePoint site
- Share data by using a database server
An overview of ways to share Access data
Database applications change and grow over time. Many factors affect needs and performance, including the number of concurrent users, the network environment, throughput, latency, database size, peak usage times, and expected growth. If your database solution is successful, it will probably need to evolve. Fortunately, Access offers an evolutionary path from simple to advanced, so you can scale your solution over time. The following table summarizes Access scenarios and workloads to help you choose that path.
Share a single database
This option is the simplest and has the fewest requirements. It also offers the least functionality. In this method, the database file is stored on a shared network drive, and all users share the database file at the same time. Some limitations include reliability and availability when multiple users change data at the same time because all database objects are shared. This method can also reduce performance because all database objects are sent across the network.
This option might work if only a few people will use the database at the same time and users don't need to customize the database design. However, this method is less secure than other sharing methods because each user has a full copy of the database file. That increases 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 users' computers. This is the default setting, but check to be sure. If a user opens the database in exclusive mode, it will interfere with data availability.
- Start Access, and then select File > Options.
- In the Access Options dialog box, select Client Settings.
- In the Advanced section, under Default open mode, select Shared, select OK, and then exit Access.
Copy the database file to the shared folder. After you copy the file, make sure that the file attributes allow read and write access to the database file. Users must have read and 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 don't 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 finish more quickly.
- Enhanced security: Users access the back-end database through linked tables. It's less likely that intruders can get unauthorized access to the data through the front-end database.
- Improved reliability: If a user runs into 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 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 you can view and edit the latest data in both SharePoint and 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 create reports based on 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 move the data from all your tables at the same time and 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 those locations. The file might be downloaded locally for editing and then uploaded again after you save your changes to SharePoint. If more than one person opens the Access database from SharePoint, multiple copies of the database might be created and some unexpected behavior might occur. This recommendation applies to all types of Access files, including single databases, split databases, and the .accdb, .accdc, .accde, and .accdr file formats. For more information about 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 gives you many benefits, but it 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. The benefits of this sharing method depend on the database server software that you use, but they generally include user accounts and selective access to data, strong data availability, and integrated data management tools. Also, most database server software works well with earlier versions of Access, so not all 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 support very large, terabyte-sized databases, which are about 500 times the current limit for an Access database file (two gigabytes). Database server products usually work efficiently by processing queries in parallel and minimizing additional memory requirements when more users are added.
- Increased availability: Most database server products let you back up your database while it is in use. As a result, you don't have to force users to exit the database to back up data. Database server products also usually handle concurrent editing and record locking efficiently.
- Improved security: No database can be made completely secure. However, database server products offer strong security that can help protect your data from unauthorized use. Most database server products offer account-based security, which lets you specify who can see which tables. Even if someone improperly gets the Access front-end, account-based security helps prevent unauthorized use of data.
- Automatic recoverability: In the event of system failure, such as an operating system crash or power outage, some database server products have automatic recovery mechanisms that restore a database to the last consistent state in 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 you work with large data sets.
- Azure SQL Server: In addition to the benefits of SQL Server, it offers dynamic scalability with no downtime, intelligent optimization, global scalability and availability, no hardware costs, and less administration.