About sharing an Access Database

There are several ways you can share data in a multiuser environment.

Share the entire Access database You can put the entire Access database on a network server or in a shared folder. This is the easiest method to implement. Everyone shares the data and uses the same forms, reports, queries, macros, and modules. Use this strategy if you want everyone to use the Access database the same way or if you can't support users creating their own objects.

Share only the tables in the Access database You can put only the tables on a network server, and keep other database objects on users' computers. In this case, the Access database's performance is faster because only data is sent across the network. When you split a database into a back end (tables) and a front end, users can customize forms, reports, and other objects in their front-end databases without affecting other users.

Share Access database objects or data access pages on the Internet You can output one or more database objects to static HTML (HTML: The standard markup language used for documents on the World Wide Web. HTML uses tags to indicate how Web browsers should display page elements such as text and graphics and how to respond to user actions.) or server-generated HTML (server-generated HTML: An Active Server Pages (ASP) or IDC/HTX file that is output from a table, query, or form, connected to an ODBC data source, and processed by the Internet Information Server to dynamically create read-only HTML files.) format, or create data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), and then display them in a browser, such as Microsoft Internet Explorer, on the World Wide Web (World Wide Web (WWW): The multimedia branch of the Internet that presents not only text, but also graphics, sound, and video. On the Web, users can easily jump from item to item, page to page, or site to site by using hyperlinks.).

Replicate the Access database If you use two computers, such as an office computer and a portable computer, you can use Microsoft Windows Briefcase to make replicas (replica: A copy of a database that is a member of a replica set and can be synchronized with other replicas in the set. Changes to the data in a replicated table in one replica are sent and applied to the other replicas.) of your Access database and keep those replicas synchronized (synchronization: The process of updating two members of a replica set by exchanging all updated records and objects in each member. Two replica set members are synchronized when the changes in each have been applied to the other.). Also, several users at different locations can work on their own copies at the same time and then synchronize them over the network, either through a dial-up connection or on the Internet.

Create a client/server application If you work in a client/server environment, you can take advantage of the extra power and security it provides by creating a client/server application. You store your data in tables on a database server such as Microsoft SQL Server instead of in local tables in Microsoft Access. The Access application (the client) retrieves the data it needs from the server. The server maintains data integrity and runs any queries that it can evaluate.

When you open an Access database file (.mdb) in shared mode, Microsoft Access also creates a locking information file (.ldb) with the same file name (for example, Northwind.ldb) and in the same folder as the database file. This locking information file stores the computer name (such as mypc) and security name (such as Admin) of each shared user of the database. Microsoft Access uses this information to control concurrency. In most cases, Microsoft Access automatically deletes the locking information file when the last user closes the database file.

In a multiuser environment, more than one person might be working with the same records at the same time. Since other people can change or even delete the same data you're trying to edit, you might occasionally conflict with others as they work.

Microsoft Access helps you keep track of the status of records as you edit them, and makes sure you're using the latest data. When two or more people try to edit the same record, Microsoft Access displays messages that help you resolve conflicts. For example, if you try to save a record that another user has locked, Microsoft Access displays the name of the person who locked that record.

To help you keep track of the status of records, Microsoft Access displays the following symbols in the current record selector (record selector: A small box or bar to the left of a record that you can click to select the entire record in Datasheet view and Form view.).

Symbol	Meaning
	This record is the current record and hasn't been edited.
	You have edited this record, but you haven't saved your changes yet. As long as this symbol is displayed, other users can't see the changes you've made to the record, and they won't be able to edit the record, if you have the record locked. To free the record for use by others, either save or undo your changes.
	This record is locked by another user. You can't edit it. If you try to type in a locked record, Microsoft Access sounds a beep.

You can also set options that control the locking of data and the refreshing of data in a shared database.

Default record locking settings

No Locks Microsoft Access does not lock the record you're editing. When you try to save changes to a record that another person has also changed, Microsoft Access displays a message giving you the options of overwriting the other user's changes to the record, copying your version of the record to the Clipboard, or discarding your changes. This strategy ensures that records can always be edited, but it can create editing conflicts between users.
Edited Records Microsoft Access locks the record you're editing, so no other user can change it. It might also lock other records that are stored nearby on your disk. If another user tries to edit a record that you've locked, Microsoft Access displays the locked record indicator in the other user's datasheet. This strategy ensures that you can always finish making changes that you start. It is a good choice if you don't have editing conflicts often.
All Records Microsoft Access locks all records in the form or datasheet (and underlying tables) you're editing for the entire time you have it open, so no one else can edit or lock the records. This strategy is very restrictive, so choose it only when you know you're the only person who needs to edit records at any one time.

When you edit data in a linked SQL database (SQL database: A database that is based on Structured Query Language (SQL).) table by using ODBC (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.), Microsoft Access doesn't lock records; instead, the rules of that SQL database govern locking. In this instance, regardless of the record-locking setting you choose for your database, Microsoft Access always acts as though the No Locks setting has been selected.

Page-level and record-level locking

You can specify the granularity of locking that Access uses in a shared database. If you use page-level locking, Access locks the 4K page (the area of memory where the record is located), and editing a record might cause other records stored nearby to be locked as well. However, performance is generally better when you use page-level locking.

If you use record-level locking, Access locks only the record you are editing. This becomes the default behavior for access to data through a form (form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.), a datasheet, and code that uses a recordset (recordset: The collective name given to table-, dynaset-, and snapshot-type Recordset objects, which are sets of records that behave as objects.) object to loop through records, but not through action queries, nor through code that performs bulk operations using SQL statements.

Update retry and refresh interval settings

You can use the Update Retry Interval and Number of Update Retries settings to specify how often and how many times Microsoft Access tries to save a record that is locked by another user. You can also use the ODBC Refresh Interval and Refresh Interval settings to control how often Access refreshes your data. Refreshing only updates data that already exists in your datasheet or form. It doesn't reorder records, display new records, or remove deleted records and records that no longer meet specified criteria. To view these changes, you must requery the underlying records for the datasheet or form.

You cannot save design changes to a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) while other users have the Access database open. The only way to ensure that changes are saved is by opening the Access database in exclusive (exclusive: A type of access to data in a database that is shared over a network. When you open a database in exclusive mode, you prevent others from opening the database.) mode.

In general, when you try to make a design change to a database object (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) (except tables and queries) or an item in shared mode, Access will temporarily promote you to exclusive mode for the Access database if you are the only user of the Access database at that time. When you save all your design changes and close all the Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) windows, Access returns the Access database to shared mode. In the interim, other users will not be able to open the Access database.

If another user has the Access database open in shared mode and you try to make a major design change, such as modifying a form, Access alerts you that you might not be able to save your changes. However, if another user has the Access database open in shared mode and you try to make a minor design change, such as changing printer settings, Access doesn't alert you that you might not be able to save your design changes. In both cases, you might want to wait until you are the only user of the Access database so that you can save your major design changes and Access can save your minor design changes.

A data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.) is handled differently. Although creating, renaming, moving, and deleting a data access page still requires promotion to exclusive mode (because this requires changing information in the Access database), editing a data access page does not require promotion to exclusive mode (because the corresponding HTML file exists in the file system outside the database).

Differences between major and minor design changes

If you don't have exclusive access to an Access database, Access will alert you when you might not be able to save the following major design changes:

Changes to database objects (except table and queries) made in Design view
Changes to a form property sheet in Form view (Form view: A window that displays a form to show or accept data. Form view is the primary means of adding and modifying data in tables. You can also change the design of a form in this view.)
Compiling the project (project: The set of all code modules in a database, including standard modules and class modules. By default, the project has the same name as the database.), modifying project properties, or adding or removing a reference in the Visual Basic Editor
Renaming, pasting, or deleting a database object
Saving a database object as another type of database object
Adding or modifying controls on a command bar
Editing custom groups in the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.)
Creating, renaming, moving, and deleting a data access page

If you don't have exclusive access to an Access database, Access doesn't alert you when you might not be able to save the following minor design changes:

Changes to datasheet formatting properties, such as line styles and fonts
Freezing, unfreezing, hiding, and showing datasheet columns
Adjusting datasheet column width and row size
Changes to the filter or sort order for a form or datasheet
Changes to the state of a subdatasheet (whether expanded or collapsed)
Changes to the OLE/DDE link (OLE/DDE link: A connection between an OLE object and its OLE server, or between a Dynamic Data Exchange (DDE) source document and a destination document.) of an OLE (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.) object or changes to the contents of an unbound object frame (unbound object frame: A control that you place on a form or report to contain an unbound object. An unbound object is an object, such as a picture, whose value isn't derived from data stored in a table.)
Changes to printer settings
Changes to a command bar, such as its location and whether or not it's visible
Changes to the layout of the Database window

Strategies for sharing Access database design and development

If you are going to share the design of an Access database, consider using the following strategies:

Assign specific objects or groups of objects to specific developers. For example, assign one developer to design forms and another to designs reports. Then, each developer can work on a private copy of the Access database. When your development team is ready for testing, assembly, and production, each developer can export the database objects from their private copy of the database to a main Access database.
Use a source code control program. If you use Visual SourceSafe, it's a good idea to run the Performance Analyzer (on the Tools menu, point to Analyze, and then click Performance) on a regular basis to maintain optimum performance.

Tips for saving design changes in a shared database

If another user has a table open or is viewing data in queries, forms, or reports based on the table, you can't make any changes to the table's design. Microsoft Access informs you that it is read-only.
If objects are dependent on one another, update all of them at the same time so that other users don't open inconsistent versions of the objects. For example, if you want to add fields to a form, make sure to add the fields to the underlying query before you update the form.
If you change a query that another user has open, that user must close and reopen the changed object to use the latest version. With Microsoft Visual Basic for Applications (VBA) modules, other users can't run the updated Function or Sub procedures until they close and reopen the Access database.

About sharing an Access database on a network (MDB)