Results 1 to 12 of 12
  1. #1
    Zan1818 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    21

    Locking Database Read-Only for Second Users

    Hello everyone,

    I'm trying to secure my database in a way that only the first user can edit/make changes to the database but if another user opens the database at the same time the first user is using it, they can only have read-only permission. Is there a way to do this in Access? The company has an Excel workbook that does this and I was wondering if I can do the same for Access.

    Thank you

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Excel does that by design because it is only one file that everyone opens and data is really part of the file design, same as with a Word document. The same will occur for Access db for design edits, but data edits will be allowed as long as users don't edit same record.

    What changes do you want to prevent - design or data? If data, why would you want to preclude data edits for secondary users? The main precept for a multi-user db is allowing multiple users to do data entry/edit simultaneously. If design, only one person should ever even think about doing edits - the developer and/or administrator. Db should be split and each user runs their own copy of frontend and revisions to frontend by developer/administrator distributed to users.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Zan1818 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    21
    This database is for a monthly activities report. Every month all the staff will come to this database and input their data on the form. I just want to make sure that if many users open the form at the same time and input their data the database won't crash or the data won't get save.

    I do have another copy of the database as a split database but the folder where the back-end database needs to be store can't be share with everyone in the office. Only a handful of the staff can see it so I can't make sure the link tables on the front-end database will be updated to the right location of the back-end database.

    I hope I made my self clear. I just need to make sure data entry at the same time can be done without a problem.

    Thank you

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Non-split db used simultaneously by multiple users is possible but I have read many warnings that this is prone to corruption and loss of data. I did develop one non-split db (that's what was demanded in spite of warnings) with multiple simultaneous users that ran for years without issue. It was supposed to be temporary until the 'final solution' was developed. Don't know if they still use it.
    Last edited by June7; 08-18-2017 at 06:27 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you don't provide users with their own copy of the front end you have to ask yourself 'do I feel lucky?'

    I don't understand this comment

    but the folder where the back-end database needs to be store where the back-end database needs to be store can't be share with everyone in the office
    if the users can't see the backend, how can they see the front end? Or why can't you put the backend where everyone can see it (i.e. where you presumably have the unsplit, shared db)

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I just want to make sure that if many users open the form at the same time and input their data the database won't crash or the data won't get save.
    Maybe much ado about nothing? If each user has their own FE, then technically, they are not using the same form, they're using a version of it.
    Secondly, if they're all entering data into the same table(s) at the same moment, so what? They're not on an existing record because the record being worked on doesn't exist. As long as they're not trying to import data from the same place, or aren't entering data directly in the table, there shouldn't be an issue?
    Lastly, if concerned about the possibility that 2 or more concurrent users are trying to edit or delete the same record, then use pessimistic record locking on the form. Or am I over simplifying the problem?
    I see this as a record locking issue (if it's an issue at all) and don't believe that matters if the shared db is a single or each user has their own.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If you make the decision to not split the dB, you might look into the Shared/Exclusive mode option

    FILE/OPTIONS/Client Settings/Advanced/Default Open Mode

    the options are Shared or Exclusive



    Another option:
    If you split the dB and each user gets their own copy of the dB, you can have code that checks to see if the user is allowed to make changes and locks the fields if they are not allowed..

  8. #8
    Zan1818 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    21
    I will try to explain me a little bit better. I have a database where every staff member in the office will enter a record once a month (this can be at the beginning or at the end of the month). They will enter this data through a form, not looking at the tables directly. They are not suppose to see the records of the rest of the staff (they want to keep it private), so I just hid the navigation panel and the form is the only thing that shows up when opening the database. Now, I want to avoid any problems if a staff member is inputting data at the same time as another user. I have thought of making a new copy at the end of every year to maybe avoid corrupting data? Splitting the data I don't believe will work because again, I don't want to put the back-end database (hence, where the tables and reports will be available) in a folder where all the staff can see it. But I believe if the folder where the back-end database is not visible for all the staff then the link tables won't work and I don't know if the information will be save everytime someone put a new record on the form.
    Please, I just want to make sure that if many users at the same time input data into the form they won't get any errors. Maybe just using a non-split database could do this but I wasn't sure.

    Thanks

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm totally confused by the explanation. I cannot see the difference in terms of accessibility to the tables between what you are doing and what is proposed by splitting the db and each user having their own copy of the front end. In fact if you split the db, you can password protect the back end so nobody can access the data except through the form. Hiding the navigation pane is totally insufficient to protect the tables because a user only has to hit F11 and there it is. And no matter what you do, if multiple users access the same front end file, you run the risk of corruption - even if they open it and code immediately boots them out again. The risk is small, but its there. Why take the chance?

    However I am now focussing on client work so will have to gracefully drop out of this thread. Good luck with your project

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Splitting the data I don't believe will work because again, I don't want to put the back-end database (hence, where the tables and reports will be available) in a folder where all the staff can see it
    This I don't get. If you have one db that everyone is going to share (that's what I'm gleaning here) then they already have at least the minimum required rights to the folder where the db is, so how can you be exposing the data any more than you are already doing by splitting?? You've already created the possibility that the tables, queries or reports can be seen. All one has to do is unhide the Nav pane and/or tweak options. It appears you don't understand how splitting works or can work for you. Maybe you ought to split, password protect the BE for opening and link the tables with a password. Then they can't get into the BE, but the password on the links will allow them to work on the tables via your forms. Yes fellow helpers, I know the password can be exposed but it requires a bit of knowledge I'll bet not many there have, and as long as you try to lock snoopers out of the FE, then it is about as good as Zan1818 will need AFAIC.

    Yes, a non split db can work if you design to be opened exclusively, but guess what happens if someone rushes off to an off site meeting while they've got it locked up? I see nothing in your posts so far that justify your concerns, and nothing that convinces me that you need a single db. Perhaps you should research split Access databases and security.

    By the time you read this, I may be unavailable for a few days.
    Last edited by Micron; 08-20-2017 at 07:24 PM. Reason: added info

  11. #11
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63
    You need a Users table, a Log in form and a VBA code to kick the user out of DB when he fails to enter correct password and clicks Cancel button. Only those users should be able to enter the DB who enter the correct password and press OK button. After that, a Welcome Screen (form) should be appeared where some buttons or links should be available to the forms which are relevant to current user. All other forms should be locked using AllowEdits = False (a form's property)

  12. #12
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63
    And splitting the database does not mean splitting the Data, it means all the tables (where the data is actually stored) will be available in the Back-End file only, and links to these tables will be available in Front-End file (in the form of Forms, Queries and Reports).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Locking the database
    By aaslezak in forum Access
    Replies: 1
    Last Post: 06-08-2015, 01:44 PM
  2. Replies: 4
    Last Post: 01-19-2015, 01:15 PM
  3. Form locking other users out of DB
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 03-15-2014, 10:49 AM
  4. Multiple Users Can't Read
    By Hulkdog in forum Access
    Replies: 7
    Last Post: 01-24-2012, 01:46 PM
  5. Access Locking other users out
    By David in forum Access
    Replies: 1
    Last Post: 07-02-2009, 09:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums