Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Location
    Duncan, SC
    Posts
    3

    Unanswered: Create a Read-Only and Write version of a DB?

    Hey dBforums-ers,

    I'm fairly noob to the Access database game...just started here and there this summer!

    My Question:
    What's the preferred/best practice/or ok to use method in order to have a read-only (view forms and print reports) user version and also a write (able to add/edit the data) version of an Access DB?

    My Setup:
    DB created with Access 2010. Users will be using Runtime2010. DB split into FE on each user's PC and BE on a shared network drive. About 8 users will occasionally use this DB. Some will just need to view forms and print reports, others will be changing the data occasionally. The way I've set my DB up is that you click a button to bring up an editable form to edit any data. So with that in mind:

    Combing the web I've basically gotten these 3 solutions:
    1. Easiest for me: Create 2 FE's. More work to make changes w/2, but I'm ok with that...for now. My particular DB only brings up an editable form when you click a button. So I could just copy the FE and just quickly delete these few buttons to essentially create a Read-Only version.
    -or-
    2. Incorporate the User Level Security from an older mdb DB, or some form of it that would allow read-only and write permissions maybe.
    -or-
    3. Create workgroups on the network and assign permissions to the files there...not sure of the details on that but I think that's what a few places mentioned doing.

    Anyways...regardless of my potential solutions above I'm looking for other peoples input on how to just have a Read-only (view forms and print reports) user version and also a write (able to change the data) version of the DB.

    Thanks in advance and you have probably answered this before so feel free to just link to a post if you like. I appreciate any feedback on this.

    -Psychoma
    (Background in HTML/CSS, new to VBA-Macros but I can generally follow along with what it's trying to do...incorporating it is another story...)

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    How about creating access levels within the database to recognize who is logged in to a computer? That is usually my preferred method as it is seamless for the end user.

    I usually start by creating a table with Username and AccessLevel fields
    Example:
    UserName.............AccessLevel
    John Doe..............Regular User
    Jane Doe..............Admin User

    I then create a module and place in this code:
    Code:
    Public Function Environ(Expression)
    On Error GoTo Err_Environ
    
    Environ = VBA.Environ(Expression)
    
    Exit_Environ:
    Exit Function
    
    Err_Environ:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Environ
    
    End Function
    Then on the form, I place an unbound text box called UserName. I set the Visible property to No.

    I place this code on the form's On Load:
    Code:
    Me.UserName = Environ("username")
    Then I use code to set whether fields are visible or not based on the access levels.

    Here is an example in one of my databases:
    Code:
    Me.UserName = Environ("username")
    Me.Greeting = "Hello, " & DLookup("First", "qry_Email", "[Q Number]=[Forms]![frm_Switchboard]![UserName]") & " " & DLookup("Last", "qry_Email", "[Q Number]=[Forms]![frm_Switchboard]![UserName]") & "!"
    Me.UserType = DLookup("Rights", "qry_Email", "[Q Number]=[Forms]![frm_Switchboard]![UserName]")
    
    If Me.UserType = "Regular User" Then
    Me.cmdNotCompleted.Visible = False
    Me.cmdTables.Visible = False
    Me.cmdUnassigned.Visible = False
    Me.cmdLockout.Visible = False
    Me.Label20.Visible = False
    Me.Label21.Visible = False
    Me.Label22.Visible = False
    Me.Label24.Visible = False
    Me.cmdclose.Top = 3.1667 * 1440
    Me.Label25.Top = 3.1667 * 1440
    Me.Command26.Top = 2.0417 * 1440
    Me.Label27.Top = 2.0417 * 1440
    Me.Command28.Top = 2.4167 * 1440
    Me.Label29.Top = 2.4167 * 1440
    Me.cmdCompleted.Top = 2.7917 * 1440
    Me.Label23.Top = 2.7917 * 1440
    End If
    
    If Me.UserType = "Admin User" Then
    Me.cmdLockout.Visible = False
    Me.Label24.Visible = False
    Me.cmdclose.Top = 4.2917 * 1440
    Me.Label25.Top = 4.2917 * 1440
    End If
    Hope this helps.

  3. #3
    Join Date
    Nov 2011
    Location
    Duncan, SC
    Posts
    3
    hhhmmm...the code looks like it would accomplish exactly what I'm looking to do!! But I have a couple questions in getting there:

    1. In your example the 2nd line Me.Greeting is an optional line?

    2. In your example the 3rd line "Me.UserType....". Did you create a log-in at your switchboard form which ties in who the username is so it knows what to compare the AccessLevel to?

    Thanks for your help!

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Yes, the Me.Gretting is optional.

    What I did was I made a field called Greeting, so when John Doe logs in, the main menu says at the top of the screen "Hello, John Doe!"

    For Me.UserType....Yes, I put this field on the main menu so it would recognize the user level. I forgot to mention this. I also set that visible property to False.

Posting Permissions

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