Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2009
    Posts
    93

    Unanswered: Employee Records

    I have a test FrontEnd interface in Access Adp format to edit/update data in SQL. I would like Employees to update their own records but how would i go about giving them the frontend interface and only seeing their own records and not everyone else records also. Any ideas/experiences appreciated

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    put an userid column in each table where that type of data exists?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2009
    Posts
    93
    Thanks for reply Brett. I'm trying to understand what you mean being a novice.

    All employees currently have their own unique employee id. Could i use this somehow in a login type setup to Access. Is this possible?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Does each user connect to the database with their network login, or a sql server login (with password), or do they use a shared login?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2009
    Posts
    93
    They would be connecting with their network login Blindman

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by sullyman View Post
    They would be connecting with their network login Blindman

    Then their data must be qualified by that ID...is that a correct statement

    In other words, is their ID on each row of their own personal data?

    SELECT * FROM personalData WHERE UID = 'X002548'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2009
    Posts
    93
    Hi Brett, Thanks. As i am in early stages, i could out their employee id in each table no problem. The only thing is, is that each employee does not know that ID So i will have to try and perhaps make some kind if login same where i give them a login name and password and then this login name looks up this Employee ID and returns the records. Would this work do you think?

    It would be great if i could get the system to automatically pick up their currently logged in windows username and then reference it against the employee id and return their records

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT SUser_Name()
    George
    Home | Blog

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Right. Store the Username of the login that owns the data in a column in the tables. Then create views of the tables that filter to show only those records associated with the current username, and grant access only to those views.

    select ....
    from table
    where Username =SUser_Name()
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Oct 2009
    Posts
    93
    Thanks BlindMan. I think i got it working. It is pulling up records as you said. Magic!

    For employees adding records, i will have to place an add record button to capture their unique ID to place in the table. How would i go about this

    I have the process in my head but not sure how to construct it as follows:

    Create a new record in the RecordsAdd Table where SUser_Name() = Employee.Login and insert into RecordsAdd.Empoyeeid Employee.Employeeid


    Employee (Table)
    Employeeid = zz123
    Login = jbloggs

    RecordsAdd (Table)
    Employeeid = zz123 etc.
    Details = Information employee enters

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you can just set SUser_Name() as the default for the column, or perhaps put a trigger on the table to capture it.
    This way, every record they insert will belong to them.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The blind dude is magic...he's a magic maaaaan...maaaaannn....maaan

    Try, try, try to understand...he's a magic man

    WAIT

    Don't you keep the credentialed login in session?

    Don't you use stored procedures?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sullyman, you appear to have posted the same question twice http://www.dbforums.com/microsoft-ac...e-records.html

    Please refreain from doing that!
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gvee View Post
    Sullyman, you appear to have posted the same question twice
    the best way to deal with this is to have a moderator simply merge the threads

    yes, all the replies from both threads will likely be intermingled, but this is as it should be

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2009
    Posts
    93
    Sorry guys

Posting Permissions

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