Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting/restricting a form? (Office XP)

    Ok, I have a form that I want to restrict a part of. My main form has many command buttons to open separate forms, and throughout those separate forms, I want the top half to be read only...or if I can restrict certain columns to read only, and others to be editable. Is there a way to do this? Is there a way to have a table opened that the first, say,10 columns are read only and those after it are editable?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Splitting/restricting a form? (Office XP)

    Access does not provide security for tables at the level of individual rows or columns. In a form bound to a table, you can lock controls for editing by setting the Locked property to Yes. Usually, you do this in design mode, but if necessary, you can do it in code in the On Open or On Load event of the form.

    Users who know how to get into the database directly will be able to circumvent this type of security.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/restricting a form? (Office XP)

    What if I have a FE/BE design? What kind of measures would I have to take to ensure they can't access it directly? I'm going to have a password on the back end...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Splitting/restricting a form? (Office XP)

    The only really good way of securing a database is to apply user-level security. You'd need it on the backend to ensure that users can't get at the tables directly, and on the frontend to ensure that users can't tamper with the design of forms. (Another way is to create an .MDE database, but <!profile=ashrafk>ashrafk<!/profile> demonstrated recently that it is possible to break into an .MDE database.)

    See <!profile=WendellB>WendellB<!/profile>'s tutorial on user-level security The Secrets of Security; it contains many useful links. Another good reference is <!profile=jacksonmacd>jacksonmacd<!/profile>'s security paper available at Database security in Microsoft Access.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Splitting/restricting a form? (Office XP)

    Hans' comments on security are spot-on, but there is a way to arrange things using Access User Security and two tables that might achieve what you want. If you take your table and split it into two tables, one with the first 10 columns (fields), and a second with the same primary key as the first table, and the remaining columns. You can then create a form and a subform and display the read-only data on the main form and the editable data on the subform. You still need Access User Security so you can set the first table to be read only for all but some sort of administrator login. Hope this gives you some additional ideas.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/restricting a form? (Office XP)

    The same result can be accomplished by retaining just one table, but creating two RWOP queries. Apply different permissions to the two RWOP queries, and base the form and subform on the two queries. This has the benefit of keeping all the info in a single table.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/restricting a form? (Office XP)

    Wendell, love your ideas, I will definitely try them. Instead of tables though, could I use queries? I don't really want to split those tables. I'll try the query thing....how do you *define* a subform? The only time I've seen it is when I create forms from two tables.....could I just edit the autoform? Either way, whatever works/whatever suggestions you have I will definitely try. - Thanks!

    What is an RWOP query?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/restricting a form? (Office XP)

    RWOP: Read With Owner's Permission. See the Security FAQ for a full explanation. there is a link on my web site.

    In essence, you remove all permissions for your users to the base tables. Then you create new queries, and include only the fields and records that you want the users to see. Fields are included/excluded by dragging them to the query grid. Records are included/excluded by applying various selection criteria. Then you change one of the properties of the queries from "Users" to "Owners". This allows the users to run the query with the permissions that the owner has on the base tables, rather than the permissions that the user has on the base tables.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/restricting a form? (Office XP)

    There isn't a small *blurb* of code or something I can enter somewhere on either my forms or my queries?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/restricting a form? (Office XP)

    Each form has an "AllowEdits" property, and each subform has a "Locked" property. You can manipulate them using VBA, probably in the Form_Open event. You can use the CurrentUser() function to determine who is running the application and make the appropriate changes to the AllowEdits and Locked properties.

    Under this scheme, any user who opens the tables or queries directly will bypass your "security".
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/restricting a form? (Office XP)

    What if I disable the Shift bypass key, and only allow them to see the forms I create when they open the database? If they don't know the password, then they can't bypass my menus and have administrator access to the tables/queries (only Read-Only)...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Splitting/restricting a form? (Office XP)

    If you haven't applied user-level security, they can still link to tables in the database from another database, or use ODBC from Excel.

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/restricting a form? (Office XP)

    There is user-level security, and they're on a FE database.....maybe redundant, but the guys I'm working with aren't too bright, and they're really not looking to TRY to get into the tables, I'm doing the security part for my own knowledge..it isn't a requirement.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •