Ways to share an Access database

This article provides an overview of the methods that you can use to share an Access database, and the factors that you should consider when you decide which method is right for you. It also provides links to more detailed information about each method.

This article assumes that the people who will share the database all have Access installed on their computers. For more information about distributing a database to people who do not have Access installed, see the article Deploy an Access 2007 application.

Although you can share data by creating and distributing reports, this article does not discuss using reports to share data. For more information about sharing data by distributing reports, see the article Distribute a report electronically.

 Tip    Try Office 2010 In Access 2010, you can publish a database to Access Services on SharePoint, and then use the database in a browser – and not just Internet Explorer.
Read an article or try Office 2010.

In this article


Overview

An Access database is made up of objects, such as tables, queries, and forms. Tables store data. Every other kind of database object helps you use the data that is stored in tables. When you want to share a database, you usually want to share the tables, because they contain your data. When you share tables, it is important to make sure that everyone is using the same tables — so that everyone is using the same data.

Other database objects (queries, forms, reports, etc.) do not contain data, so it is less important that everyone use the same objects. In fact, there can be benefits to using different copies of such objects. Depending on your needs and resources, you have a variety of options for sharing your database.

Methods of sharing

  • Split database     This is a good choice if you do not have a SharePoint site or a database server product. The tables go in one Access file, and everything else goes in another Access file, called a front-end database. The front-end database contains links to the tables in the other file. Each user gets her own copy of the front-end database, so only tables are shared.
  • Network folder    This is the simplest option, but it also offers the least functionality. The database file is stored on a shared network drive, and people use the database file simultaneously. Reliability and availability can become issues if there are multiple simultaneous users changing data. All database objects are shared.
  • SharePoint site    This is a good option if you have a server running Windows SharePoint Services. Several points of integration with SharePoint help make database access more convenient. There are two methods: publishing a database, and linking to lists.

When you publish a database, you can take advantage of several SharePoint list features, and can open forms and reports from the SharePoint View menu. Because the database file is stored on the SharePoint site, all database objects are shared.

When you link to SharePoint lists, data is shared, but database objects are not. Each user gets her own copy of the database.

  • Database server    This method is similar to splitting a database in that 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. You should use this option if you have a database server available. Database servers are optimized for sharing data among many users. Benefits will vary depending 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. Only tables are shared.

Factors to consider

Split database Network folder SharePoint site Database server
Requires database server software? N N N Y
Requires Windows Server 2003 or later? N N Y N
Data availability Good Adequate for small groups with light data-editing Best Best
Security Depends on additional measures Least secure method Best Best
Flexibility Flexible. Can easily develop new database features without disrupting work. Each user can modify the design of objects in her 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. Depends on method of sharing. With publishing, can control which forms and reports are available. With linking, users can modify their own copies of the database. Flexible. Can easily develop new database features without disrupting work. Each user can modify the design of objects in her own copy.

Top of Page Top of Page

Split a database

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.

To split a database, you use the Database Splitter Wizard. After you split the database, you distribute the front-end database to your users.

The benefits of a split database include the following:

  • Improved performance    The performance of the database usually improves significantly because only the data is sent across the network. In a database that is not split but is shared by using a network folder, the database objects themselves — tables, queries, forms, reports, macros and modules — are sent across the network, not just the data.
  • Greater availability    Because only the data is sent across the network, database transactions such as record edits are completed more quickly, which leaves the data more available to edit.
  • Enhanced security    If you store the back-end database on a computer that uses the NTFS file system, you can use NTFS security features to help protect your data. Because users access the back-end database by using linked tables, it is less likely that intruders can obtain unauthorized access to the data by stealing the front-end database or by posing as an authorized user. By default, Windows XP, Windows Vista, and Windows Server 2003 use the NTFS file system.
  • 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. Because the user only accesses data in the back-end database by using linked tables, the back-end database file is much less likely to become corrupted.
  • Flexible development environment    Because each user works with a local copy of the front-end database, each user can independently develop queries, forms, reports, and other database objects without affecting other users. Similarly, you can 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 step-by-step instructions and more information about splitting a database, see the article Split a database.

Top of Page Top of Page

Share a database by using a network folder

The simplest way to share a database is to put it on a shared network folder. Although this is the simplest method, it is also the most limited. Before you consider this method, all the following conditions should be met:

  • No more than a few people are expected to use the database at the same time.
  • No Memo fields are present in the database, or if they are, they will not be simultaneously updated by different users.
  • Users do not 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

  1. 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.

  1. 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 others' use of the data. Perform the following procedure on each computer:
    1. Start Access.
  1. Click the Microsoft Office Button Button image, and then click Access Options.
  1. In the left pane of the Access Options dialog box, click Advanced.
  2. In the right pane of the Access Options dialog box, in the Advanced section, under Default open mode, select Shared.
  3. Click OK, and then exit Access.
  1. 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.
  2. On each user's computer, create a shortcut to the database file.

When you enter the path to the database file in the Target property of the shortcut, use 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 Top of Page

Share a database by using a SharePoint site

Access offers various points of integration with Windows SharePoint Services. You can benefit from centrally located data, and can also use SharePoint features to help manage application access and to make data available via a Web browser. For an overview of SharePoint integration, see the article Introduction to integrating data between Access and a SharePoint site.

To share a database by using SharePoint, you can either publish your database, or you can move your tables to SharePoint lists and then link to them from your Access database.

Publish a database to a SharePoint site

When you publish a Microsoft Office Access 2007 database to a Windows SharePoint Services 3.0 site, other members of your organization can work with the database. You can share the tables with other people who use the SharePoint site while you continue to use Access as the front end for the forms, reports, and queries in the database. If other users have Access installed, you can also make the queries, forms, and reports available to them as views.

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 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.

When you publish a database to a SharePoint site for the first time, Access provides a list of Web servers that makes it easier to navigate to the location where you want to publish, such as a document library. After you publish the database, Access remembers the location so that you don't need to locate the server again when you publish any changes.

Publishing a database to a SharePoint site is available only for database files that are saved in the Office Access 2007 format.

For more information about publishing a database to a SharePoint site, see the article Publish a database to a SharePoint site.

Move your data to SharePoint lists and link to them

With this method, each user can modify her 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. This method has the same benefits as using a split database. In addition, because 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:

  1. Move the data to SharePoint lists.
  2. Create links to those lists.
  3. 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.

Use the Move to SharePoint Site Wizard

  1. On the External Data tab, in the SharePoint Lists group, click Move to SharePoint.

 Note   This option is available only if your database is saved in Office Access 2007 format.

  1. Follow the steps in the Move to SharePoint Site Wizard, including specifying the location of your SharePoint site.

To cancel the process, click Stop.

  1. 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.

  1. Click Finish when the wizard completes its actions.

If the wizard displays a warning, you should review the log table and take any actions necessary to ensure that your data was migrated successfully. 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.

For more information about moving your data to SharePoint lists, see the article Move the tables in an Access database to a SharePoint site and link to them.

Top of Page Top of Page

Use Access with 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.

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 use 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:

  1. Move data from the tables in an Access database to tables on the database server.
  2. Link to the database server tables from within the Access database file.
  3. Create appropriate user accounts on the database server.
  4. Distribute the Access database file.
  5. Install any necessary database drivers on users' computers.

For specific information on using Access with SQL Server, see the topic, Move Access data to a SQL Server database by using the Upsizing Wizard.

Top of Page Top of Page

 
 
Applies to:
Access 2007