Microsoft Access gurus: Locking records in tables

Discussion in 'OT Technology' started by samp27, Aug 26, 2005.

  1. samp27

    samp27 New Member

    Joined:
    Oct 28, 2004
    Messages:
    555
    Likes Received:
    0
    Location:
    Bay Area
    I was wondering if its possible to lock records in a table given a field meets a certain criteria.

    For instance, at work, we have a trouble ticket database. Once the ticket status is updated to "Close", I dont want anyone to change anything for that particular record (other than the admin if need be).

    Now the permissions for the table have already been set. A user can do everything but delete records. I have to give them the ability to update because users come back to the record to add more information.

    I'm trying avoid users changing recordsets once the ticket is closed.

    I have tons of vba coding done for this database (used on the forms), so if theres something I can add, I'd like to know as well.

    Thanks guys for any help..
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well obviously from a GUI standpoint it's easy, but if you're worried about someone going into the table itself, you can't do it. You have to move the records to another table.
     
  3. samp27

    samp27 New Member

    Joined:
    Oct 28, 2004
    Messages:
    555
    Likes Received:
    0
    Location:
    Bay Area
    When users want to update data, they use a form. The form gets its data from a query. That query ONLY shows open tickets. When users log into the database, the main window isnt shown (only the forms that I have created). I believe I have taken every precaution I can think of for users not to see the main table (or be able to pull the data once its closed) but the smart ones can figure out the F11 method or the SHIFT method when logging in.

    Edit: When you say GUI, you mean the form? Correct? I know there are probably multiple ways of doing it, but how do you suggest I should do it?
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Make an on current event that checks the field called status and then change the form.allowedits to false

    And yes I mean the form = gui
     
  5. samp27

    samp27 New Member

    Joined:
    Oct 28, 2004
    Messages:
    555
    Likes Received:
    0
    Location:
    Bay Area
    This method only freezes edits from the form end. Blah... I'll try to figure something out. Thanks.
     
  6. StevesVR4

    StevesVR4 Get Arrested

    Joined:
    Jul 1, 2003
    Messages:
    7,328
    Likes Received:
    0
    Giving the users direct access to the database is just asking for trouble. :hsugh:
     
  7. samp27

    samp27 New Member

    Joined:
    Oct 28, 2004
    Messages:
    555
    Likes Received:
    0
    Location:
    Bay Area
    How am I giving direct access to a database?
     
  8. StevesVR4

    StevesVR4 Get Arrested

    Joined:
    Jul 1, 2003
    Messages:
    7,328
    Likes Received:
    0
    I am no Access guru but this just plain sounds bad. I assume users are directly accessing the Access database and you are attempting to hide the main table views but people know work arounds. Thus, they have direct access to the database. You need a server program with clients that regulates access and determines what people can do. The server can have its own id and the users don't need direct access to the database.
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Exactly, there's no way to do what you want on a record level unless you archive the record once it's closed into a different table. That table could have Add privileges but not Edit privileges.
     
  10. samp27

    samp27 New Member

    Joined:
    Oct 28, 2004
    Messages:
    555
    Likes Received:
    0
    Location:
    Bay Area
    The database is secured. Users dont have admin rights. In Microsoft Access, unless there is another way for users to create and update records, without using ASP, please let me know.

    I've already hid the main table views, but I really cant tell you if the users know the work arounds. The only thing the user sees is a form. Thats it.

    I use Microsoft Access as a database so I can have client/server in one program. As the database grows, so will the need for new software.
     
  11. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    It's quite simple - you just create another database that links to the data in yours.

    Having said that, I don't think you'll have a problem.
     
  12. samp27

    samp27 New Member

    Joined:
    Oct 28, 2004
    Messages:
    555
    Likes Received:
    0
    Location:
    Bay Area
    I knew this was going to get complicated because I'm about to ask some stupid questions.

    With linking data, either by creating a new read-only table or database, if the original data is changed, wont it affect the linked data. Heres where I'm getting at: Is it possible to just grab the closed record and then cut off the link for that record. Its most likely not possible, but then how would linking data be beneficial?

    Btw, thanks.
     
  13. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    What I meant was that if someone wanted to change the data by bypassing your protections, all you would need to do is create your own database and link to your data. The user rights are still the same (well depending on the workgroup settings) regardless of where the data is going. So, if I really wanted to bypass your security settings, that's what I would do.

    Ok, so anyway, what I said before is still valid, the security settings for a table cannot be controlled on the record level.

    As for the advantage of linking data, the best advantage is that you can produce different versions of the front end (forms, queries) without affecting the data in the backend. Also, each client can run a local copy of the front engine (faster) whilst sharing a backend database.

    As I said before, the only way to do what you want is to archive the closed records in a different table that doesn't allow edits to common users.
     

Share This Page