Access Security Open Exclusive (1 Viewer)

[MrsGorilla]

I was just wondering if anyone can explain to me the difference between setting the database to open in shared mode in the Tools-->Options menu vs. checking or unchecking the Open Exclusive box under the user security section? Is it just two different ways to do the same thing or are there different implications for using one over the other?

 

[mhartman]

Hello:

It's a "permissions" thing:

Types of permissions (MDB)
Show All
Hide All
Note The information in this topic applies only to a Microsoft Access database (.mdb).

The following table summarizes the permissions (permissions: A set of attributes that specifies what kind of access a user has to data or objects in a database.) that you can assign.

Permission Permits a user to
Open/Run Open a database, form, or report, or run a macro (macro: An action or set of actions that you can use to automate tasks.) in a database.
Open Exclusive Open a database with 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.) access.
Read Design View tables, queries, forms, reports, or macros in 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.).
Modify Design View and change the design of tables, queries, forms, reports, or macros; or delete them.
Administer For databases, set a database password, replicate (replication: The process of copying a database so that two or more copies can exchange updates of data or replicated objects. This exchange is called synchronization.) a database, and change startup properties.
For tables, queries, forms, reports, and macros, have full access to these objects and data, including ability to assign permissions.

Read Data View data in tables and queries.
Update Data View and modify, but not insert or delete, data in tables and queries.
Insert Data View and insert, but not modify or delete, data in tables and queries.
Delete Data View and delete, but not modify or insert, data in tables and queries.

Notes

Some permissions automatically imply the selection of others. For example, the Update Data permission for a table automatically implies the Read Data and Read Design permissions because you need these to modify the data in a table. Modify Design and Read Data imply Read Design. For macros, Read Design implies Open/Run.
To design forms, reports, macros, and modules (module: A collection of declarations, statements, and procedures stored together as one named unit. There are two types of modules: standard modules and class modules.) in a multiuser environment, you must open 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.) exclusively, which means you must have Open Exclusive permission on the Access database.
In general, if you want users to be able to access a linked table (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.), grant them Read Data and Read Design permissions on the table in the back-end database, and Modify Design permission on the table link defined in the front-end database so that they can conveniently re-link the table. If you want to restrict all access to the back-end table but still allow users to view the data and re-link the tables, remove all permissions on the back-end table and use queries in the front-end database with their RunPermissions property set to Owner's.

 

[MrsGorilla]

Thanks for the detailed explanation. Some of it I knew already, but I had a hard time finding a detailed explanation of the different types of permissions, particularly the difference between assigning Modify Design permissions and Administer permissions.

But now let me ask you this. Suppose you have given someone the permission to Open Exclusive on the Database object, but in the Tools-->Options menu on the General tab it is set to default to shared mode. Does that mean it will open shared unless they are trying to modify the design on something? Or does it always open in exclusive mode if you've granted that permission in the user security portion. Am I making myself clear?

 

[mhartman]

Hello:

It is my understanding that it has to be Exclusive or Shared, not both. Otherwise what would be point of the option?

Regards
Mark

 

[boblarson]

If you select Exclusive then each time you open it you will open in Exclusive so that others won't be able to open when you have it open. If the Shared mode is selected, you can still modify objects, if you have the security level to do so, but it won't let you save any changes if someone else connects up or is connected.

 

[MrsGorilla]

If you select Exclusive then each time you open it you will open in Exclusive so that others won't be able to open when you have it open. If the Shared mode is selected, you can still modify objects, if you have the security level to do so, but it won't let you save any changes if someone else connects up or is connected.

That's kind of the answer I was looking for, as that would make sense to me logically. However, I have another question. I was opening up my DB using my "test id" that I created to test permission levels in one of the groups I set up. When I try to open a report in design view to make changes I get the error message about not having exclusive access to the DB so I may not be able to save changes. However, I know I'm the only one in the DB as I haven't "gone live" with it yet and the copy of it I'm playing with resides on my hard drive. Any suggestions as to why that might be happening? Thanks for the input.

 

[boblarson]

Since it's been about 10 years since I last had to use Access Security, I may not be able to answer that one. Hopefully someone else might be able to chime in on that. Sorry.

 

[IvyGrad]

Here's another spanner in the works: What if you are linking a SharePoint list to Access so that you can create reports ... will the permissions in SharePoint carry over to Access in exclusive mode or do you have to reinvent the wheel in Access when linking to SharePoint?