Ways to share an Access database

There are several ways that you can share an Access database. The method that you should use depends on your needs and resources. This article provides information to help you decide which sharing method is right for you. It also provides links to more detailed information about each method.

This article assumes that all the people who share the database have Access installed on their computers. The following table contains links to articles about ways to share that don't require users to have Access installed.

Other ways to share Required to use For more information, see...
Access Runtime deployments Access Runtime Deploy an Access 2007 application
Reports E-mail; may require other programs depending on report format Distribute a report electronically
Web app

Web browser; Access Services infrastructure (can be hosted)

 Note   This article does describe how you can use Access Services on a SharePoint site to share a database with other users who do have Access installed. The article linked in the cell at right describes how to share a database with users who do not have Access installed.

Build a database to share on the Web
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 and has the least requirements, 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    You have several good options if you have a server running SharePoint, especially a server running Access Services, a new component of SharePoint Server. Several points of integration with SharePoint help make database access more convenient.

There are three SharePoint sharing methods:

  • Publishing a database by using Access Services    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). Access must be installed to use standard Access objects, but because the database file is stored on the SharePoint site, all database objects are shared.

 Note   When you share a database over the Web and the database includes client objects, the database is called a hybrid database. Users without Access can only use the Web database objects, but users who do have Access installed can use all the database objects.

  • Saving a database to a document library    This method is similar to saving a database to a network folder. You should consider also using SharePoint lists to store the data, instead of Access tables, to further improve data availability.
  • Linking to lists    When you link to SharePoint lists, data is shared, but database objects are not. Each user uses 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 and all your users have Access installed. 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. 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.

Factors to consider

Split database Network folder SharePoint site Database server
Requires database server software? N N N Y
Requires SharePoint Foundation (formerly called Windows SharePoint Services)? N N Y N
Requires 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 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. 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. 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. 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 7 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 with the fewest system requirements, it also the least robust. All the following conditions should be true:

  • 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 data availability. Perform the following procedure on each computer:
    1. Start Access.
    2. Click the File tab, and then click Options.
    3. In the left pane of the Access Options dialog box, click Client Settings.
    4. In the right pane of the Access Options dialog box, in the Advanced section, under Default open mode, select Shared.
    5. Click OK, and then exit Access.
  2. 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.
  3. 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

By choosing SharePoint, you can benefit from centrally located data, and can also use SharePoint features to help manage user permissions and to make data available via a Web browser.

Ways to share a database by using SharePoint


Publish a Web database by using Access Services

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.

How it works

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.

 Note   To build a web database, you need Designer permissions on the SharePoint site where you want to publish it. For more information about SharePoint permissions, see the See Also section.

Forms and reports run in the browser

Forms, reports, and UI macros run inside the browser. This lets Access refresh data on the screen without having to redo the whole page.

You can create a form to help people navigate your application. A new control, the Navigation control, makes it easy to add standard Web-style navigation buttons to a form for this purpose.

 Note   The Navigation Pane (the feature that you use in Access to browse the objects in a database) is not available in a Web browser.

Data is stored in SharePoint lists

All of your tables become SharePoint lists, and records become list items. This lets you 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.

Share on the Internet

You can use SharePoint Online to publish your web database to the Internet. SharePoint Online is an Internet-facing SharePoint deployment, hosted by Microsoft. For more information, see the See Also section.

When you publish a database to Access Services for the first time, you specify a URL for the SharePoint Server where you want to publish. 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.

Using client objects in a hybrid database

You can create and save client objects in a database that you publish by using Access Services, but those client objects cannot be used in a Web browser. However, users who have Access insatlled can open the hybrid database by using Access, and then use the client objects that you published.

For example, you might have a customer service Web app. Your customers can use some parts of the app in a Web browser. Other parts of the app  — for example, a table and form that show incentives a customer service representative can offer  — are only available if the database is opened by using Access.

For more information about sharing a database by using Access Services, see the article Build a database to share on the Web.

Save a database file to a SharePoint document library

This method is a convenient way to manage access to a database. You can save a database to any SharePoint document library. This lets you integrate the management of Access database files with the rest of your documents and business information.

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

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.

  1. Open the database that you want to share.
  2. Click the File tab, and then click Share.
  3. Under Save Database As, in the Advanced section, click SharePoint.
  4. In the Save to SharePoint dialog box, browse to the document library that you want to use.
  5. Review the database file name and database file type, make any necessary changes, and then click Save.

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 Export Tables to SharePoint Wizard

  1. On the Database Tools tab, in the Move Data group, click SharePoint.

 Note   This option is available only if your database is saved in the .accdb file format.

  1. Follow the steps in the Export Tables to SharePoint 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.

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

Try Office 2010 today!

 
 
Applies to:
Access 2010