Results 1 to 10 of 10

Restrict Access to certain tables based on front end

  1. #1
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262

    Restrict Access to certain tables based on front end

    Not sure if this is possible, but I have two different back ends for two groups of people. Group1 has control over table1, and Group2 has control over table2. However, group1 needs to sometimes extract/use/view information in table2, and vice versa for group2 and table1. Group1 and Group2 have their own front ends, call them front1 and front2. Sometimes, group1 needs to view/use but NOT modify information in table2, which is also linked to front1, and vice versa for group2 and table1/front2. So basically, we can see each other's linked tables.



    I want to put permissions on front1 so that table2 cannot be edited from front1, and table1 cannot be edited from front2. group1 should only have full control over table1, and group2 should only have full control over table2, but group1 cannot have any modification control over table2, and vice versa. Is this possible or no?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,329
    I think possible, but can you tell us more about the application. You have 2 back ends and 2 front ends. Is this all MSAccess?How many people in Groups? Where are groups located?

  3. #3
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Yes, this is all MS Access 2010. I'm leaving room for any number of people in each group, but right now it's in the single digits. We're all in the same building, on the same local server.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,329
    What sort of data (subject matter) is in table1 and table2?

    Scenario for discussion:

    A Group contains 1 or more Users
    There are 1 or more Groups
    A User is only in one Group

    Assuming 2 groups G1 and G2
    A Group has CRUD(create,read, update and delete) rights on 1 specific Table
    G1 has CRUD on Table1
    G2 has CRUD on Table2

    G1 needs ReadOnly Access to T2
    G2 needs ReadOnly Access to T1

    But how current must the Readonly Table be?

    What sort of processes procedures do you envisage?
    Do you really need 2 back ends?
    Sounds like it could be 2 separate databases DB1 and DB2 where usage is G1/DB2 and G2/DB2.

    You have thought more about the requirements and the physical implementation than readers, so how about giving us a little more background/ideas which led to this potential arrangement.

  5. #5
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    A database of parts. Both contain part number field (which have a relationship), table1 is pure facts on the part, table 2 is pricing information on the part.

    The current status of a read only table does not matter because 1) It is linked to a backend, and 2) The Refresh button is your friend.

    The tables in question already have procedures in place via through forms, and I would like to keep it that way (all editing done through forms). I do need 2 back ends because there are other tables in one back end that do not belong in the other and vice versa. I need to keep SOME of the information (table data) separate using separate back ends.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,329
    Sounds like you have it under control.
    Good luck with the project.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,355
    What a PITA! I suggest you do not 'need' 2 BEs and 2 FEs. One backend and one frontend using form/report designs and code can manage what each user works with.

    You are having to essentially integrate the two anyway with linking. Data is editable through links so you still have to use form/report designs and code to manage what each user can work with.
    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.

  8. #8
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    It is more organized for me if I have two back ends because group1 has quite a few forms and tables that I dont even want group2 to know of their existence. I'm truly only working in one myself. I'm building the other for Group2 people, and once I show them how it works, it will be theirs to use. I don't even want the groups to have access to each other's forms or anything. Both groups have their own forms/queries/reports. They only have table1 and table2 in common.

    Here's my problem: When I drop the link for table2 into front1, or table1 into front2, those linked tables are editable from the front ends. In other words, group1 can edit table2 (which should only be under group2's control), and vice versa. I want to restrict this in the front ends.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,355
    Like I said ...

    Possible measures:

    Set db options that hide ribbon, disable right click shortcut menus, disable function keys and then publish FE as accde and hope users don't track down the original accdb file and/or the backends.

    Implement password protection.

    Migrate to MySQL or SQL Express.
    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.

  10. #10
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    I will give it a try! Thanks

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

Similar Threads

  1. Restrict linking table access to one file
    By gottnoskill in forum Import/Export Data
    Replies: 1
    Last Post: 04-15-2014, 06:04 PM
  2. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  3. Restrict Access to Form Based on fOSUserName()
    By ricker090 in forum Programming
    Replies: 1
    Last Post: 11-28-2012, 04:07 PM
  4. Replies: 1
    Last Post: 03-13-2010, 12:32 PM
  5. Restrict form access to certain people
    By DannyBoy7783 in forum Forms
    Replies: 2
    Last Post: 06-09-2008, 06:18 AM

Posting Permissions

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