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 or server-generated HTML format, or create data access pages, and then display them in a browser, such as Microsoft Internet Explorer, on the World Wide Web.
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 of your Access database and keep those replicas synchronized. 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.
The locking information (.ldb) file
Editing data in a shared database
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.
| 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. |
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 table by using ODBC, 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, a datasheet, and code that uses a recordset 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.
Saving design changes in a shared database
You cannot save design changes to a Microsoft Access database 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 mode.
In general, when you try to make a design change to a database object (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 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 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
- Compiling the project, 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
- 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 of an OLE object or changes to the contents of an unbound object frame
- 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.