Ways to share an Access desktop database

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 and the benefits of each option, and give you resources for more information on a method that would work for you.

To make design changes to your database, you'll need Access installed on your computer.

Share data by using network folders

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.

Note: 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 network folder:

If one is not already available, set up a shared network folder.

For help with this step, see the help system for the operating system of the computer that you want to use to share your database. If the shared folder is on a network server, you might need assistance from the network administrator.
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. Complete the following procedure on each computer:
1. Start Access and under File, click Options. Note, if you're using Access 2007, click the Microsoft Office Button and then click Access Options.
2. In the Access Options box, click Client Settings. Note, if you're using Access 2007, click Advanced.
3. In the Advanced section, under Default open mode, select Shared, click OK, and 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. In the Shortcut Properties dialog, enter the path to the database file in the Target property by using a UNC address instead of a mapped drive letter. For example, instead of F:\sample.accdb, use \\computername\shared.accdb.

Note: This step can also be performed by the users themselves.

Top of Page

Share a split database

This is a good choice if you do not have a SharePoint site or a database server. You can use share split databases over a network or a SharePoint site. 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.

Benefits of splitting a database

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.

If this option would work for you, go to the instructions on Split an Access database.

Top of Page

Share a database on a SharePoint site

Important Microsoft no longer recommends creating and using Access web apps in SharePoint. As an alternative, consider using Microsoft PowerApps to build no-code business solutions for the web and mobile devices.

You have several good options if you have a server running SharePoint, especially a server running Access Services. Several points of integration with SharePoint help make database access more convenient. When you publish a web database, Access Services creates a SharePoint site that contains the database. All of the database objects and data move to SharePoint lists in that site.

When you publish a database, you move it to the Web. You can create Web forms and reports that run in a browser window, and can also create standard Access objects (sometimes called "client" objects to distinguish them from Web objects). You would need Access installed on your computer to use client Access objects, but all database objects on SharePoint are shared.

Note: When you have Access installed in your computer, you can use the client objects from a Web database otherwise you can only use the Web database objects.

Access Services provides a platform for you to create databases that you can use on the Web. You design and publish a web database by using Access 2010 and SharePoint, and people use the web database in a Web browser.

Note: You would need Designer permissions on the SharePoint site where you want to publish the database.

Forms, reports, and UI macros run inside the browser.

Data is stored in SharePoint lists if you're using a web database: All tables become SharePoint lists, and records become list items and you can use SharePoint permissions to control access to your web database.

Queries and data macros run on the server: All SQL processing happens on the server. This helps improve network performance by limiting traffic to result sets.

Saving a database to a document library

You can save a database to any SharePoint document library. This method is similar to saving a database to a network folder and provides a convenient way to manage access to a database. When you link to SharePoint lists, data is shared, but database objects are not. Each user uses their copy of the database.

For example, if your SharePoint site contains lists that track customer service issues and store employee information, you can create a database in Access as a front end for those lists. You can build Access queries to analyze those issues and Access reports to format and publish written reports for a team status meeting. If people have Access on their computers, you can make the Access queries and reports become available on the View menu for the SharePoint list. When people view the list on the SharePoint site, they can locate and open the queries, reports, and other Access objects by clicking the View menu. If people don't have Access, they can still use the data in the lists by using SharePoint views.

Open the database that you want to share.
Under the File tab, click Save As.
Click Save Database As, in the Advanced section, select SharePoint and then click Save As.
Notes:
- If you're using Access 2007, click the Microsoft Office Button > Publish > Document management Server.
- If you're using Access 2010, click File > Save & Publish > Save Database As > SharePoint.
In the Save to SharePoint dialog box, browse to the document library that you want to use.
Review the database file name and database file type, make any necessary changes, and then click Save.

For more information, see publishing to Access Services and import from or link data to a SharePoint list.

Top of Page

Share a database by linking to SharePoint lists

This method has the same benefits as using a split database and users can modify their own copy of the database, because the data is shared via the SharePoint site. Although you do not gain the same benefits as when you publish the database to a SharePoint site, you do gain the convenience of centrally located data. Since the data is in SharePoint lists, you can make it separately available over the network by using SharePoint features.

This method has three main steps:

Move the data to SharePoint lists.
Create links to those lists.
Distribute the database file.

You can use the Move to SharePoint Site Wizard to accomplish the first two steps, and any means at your disposal to accomplish the last step.

Using the Export Tables to SharePoint Wizard

On the Database Tools tab, in the Move Data group, click SharePoint.
Notes:
- This option is available only if your database is saved in the .accdb file format.
- If you're using Access 2007, on the External Data tab, in the SharePoint Lists group, click Move to SharePoint.
Follow the steps in the Export Tables to SharePoint Wizard, including specifying the location of your SharePoint site. To cancel the process, click Cancel.
On the last page of the wizard, select the Show Details check box to see more details about the migration.

This wizard page describes which tables have been linked to lists and provides information about a backup location and the URL for your database. It also provides a warning if some migration issues were encountered and provides the location of a log table where you can see more details about the issues.
Click Finish when the wizard completes its actions.

If the wizard displays a warning, review the log table and take necessary actions. For example, certain fields may not be moved or may be converted to another data type that is compatible with a SharePoint list.

Note: To view your lists on the SharePoint site, click Lists on the Quick Launch, or click View All Site Content. You may need to refresh the page in your Web browser. To make your lists appear on the Quick Launch on the SharePoint site or to change other settings, such as enable versions to be tracked, you can change the list settings on the SharePoint site. For more information, see Help on the SharePoint site.

Top of Page

Share a database by using a 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. Use this option if a database server is available and all your users have Access installed. 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.

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.

Basic steps to using Access with a database server

The exact steps required to use Access with a database server depend on the database server product that you use, but the basic steps are the same:
Move data from the tables in an Access database to tables on the database server.
Link to the database server tables from within the Access database file.
Create appropriate user accounts on the database server.
Distribute the Access database file.
Install any necessary database drivers on users' computers.
For specific information on using Access with SQL Server, see Move Access data to a SQL Server database by using the Upsizing Wizard.

Also see Link to or import from an SQL Server database.

Top of Page

Things to consider when deciding on a method

Method requirements	Split database	Network folder	SharePoint site	Database server
Does it require database server software?	N	N	N	Y
Does it require SharePoint	N	N	Y	N
Does it require Access Services running on a SharePoint Server?	N	N	Depends on your scenario: Linking to lists and saving to a document library do not require Access Services Publishing as a Web database or a web app does require Access Services	N
Data availability	Good	Adequate for small groups with light data-editing	Best. Allows for offline scenarios.	Best
Security	Depends on additional measures	Least secure method	Best	Best
Flexibility	Flexible. Can easily develop new database features without disrupting work. Users can modify the design in their own copy.	Less flexible. Development can be done with offline copy of database, which is then replaced. Does not allow users to individually modify database design.	Flexible. Leverages SharePoint permissions for access control and design changes. Allows for browser-based use of some database objects, such as forms.	Flexible. Can easily develop new database features without disrupting work. Users can modify the design of objects in their copy.

Top of Page

Microsoft Office Tutorials

Wednesday, September 12, 2018