There are several ways to share data in a Microsoft Access project (.adp) and several considerations to keep in mind when working in a multiuser environment. This topic provides reference information about:
Using Access projects in a multiuser environment
Sharing a Microsoft SQL database on the Internet
Replicating an SQL Server database
In a multiuser environment, each user needs their own copy of the Access project file that is connected to the same Microsoft SQL Server database. You cannot share the same Access project file among two or more users.
When you open an Access project, it's always opened in exclusive mode. If another user has opened an Access project first, and you try to open the same Access project while that user still has it open, Access alerts you that the file is opened exclusively and gives you the option of opening a read-only copy.
In practice, when you are running a shared application, you simply provide a copy of the Access project file to each user of the application. In this case, each user has their own copy of forms, reports, data access pages, macros, and modules. But they are in fact sharing one SQL Server database and the tables, views, stored procedures, and database diagrams that reside in that database. Therefore, it's important in a multiuser environment to also provide adequate security on these shared database objects so their integrity is not compromised. Learn about protecting an Access project.
Return to top
In a multiuser environment, more than one person might be working with the same record at the same time. Because other people can change or even delete the same data that you're trying to edit, you might occasionally conflict with others as they work. An Access project uses a technique called optimistic record locking to handle record contention.
If you modify a record, and someone updates that record before you save it, Access displays a message box and warns you that if you save the record, you will overwrite the changes that the other user has made. You can do one of the following:
Because other users may add, modify, or delete records that you are accessing, you should periodically refresh the display of data in Datasheet or Form view by using the Refresh command (Records menu) to re-query and see up-to-date data.
Return to top
You can create data access pages, or output one or more database objects to server-generated HTML or static HTML, display these Web pages in a browser, such as Microsoft Internet Explorer, and access data though these Web pages. Learn about the three types of Web pages Access creates.
Return to top
If you use two computers, such as an office computer and a portable computer, you can make replicas of your SQL Server database and keep those replicas synchronized. Many users at different locations can work on their own database copies of a Master database at the same time and then synchronize them over the network, either through a dial-up connection or the Internet. Learn about replication in an Access Project.
Return to top