Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: Too many problems for securing DB

    This is my situation :

    I made a database (located on the server) for storing the courses our employees followed during the years.
    One of the tables of the DB is called 'tblEmployees' and contains the ID of all the employees (who ever worked for the company) and the ID of the department they belong to.

    I made a query (qryActiveEmployees) which will give me only the employees who are currently working for the company.

    I use this query as source for comboboxes in almost every form.

    BUT...they asked me to provide a kind of security.
    Meaning is that users who belong to a certain department (sales) only can consult the data of the employees who also belong to that department (sales).
    So depending on the user who logged in to the database I have to append a where-statement to my original query to obtain only the employees of the same department as the user. ("Where department = 'sales'")

    This is my first question : how can I modify an access-query using VB-code.
    I tried the QueryDef-stuff, but I can't get it working.
    Till now I always used ADO (and I want to keep this) but it looks like QueryDef is only DAO.
    Do I something wrong or does anyone have any other idea?

    Suppose I can do this, than there is another problem.
    If a user from another department (purchasing) logged on into the database the query will be modified ("where department = 'purchasing'")
    But this means the user from the sales-department is now also working with the employees of the purchasing-department.

    Therefor I was thinking to create a local database everytime a user logs in with a table containing the employees of the same department as the user.
    But how can I create a local database on startup and delete it when the user logs of.

    I'm not sure if this is the right way, but at the moment I don't see any other solution.


    Thanks for your help.

  2. #2
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    forget creating copies and modifying queries....

    to cut long story short, look up help for parameters (ADO or DAO)....

    it's quite simple and instead of setting where to check for constant you create parameter in query and put it in criteria field inside [].....

    then you call it from vba, just set parameter property with value you want query to check.....

  3. #3
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by Strucnjak
    forget creating copies and modifying queries....

    to cut long story short, look up help for parameters (ADO or DAO)....

    it's quite simple and instead of setting where to check for constant you create parameter in query and put it in criteria field inside [].....

    then you call it from vba, just set parameter property with value you want query to check.....
    I don't want to look stupid, but at the moment this is a level too high for me.
    So if you think it's quite simple maybe you don't mind help me with the details.
    Let me now if you are ready and I will give you more direct questions and more details.

  4. #4
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    you'll have to work on details, but here is what you should do:

    since you already know ADO, look up parameter object, you can find it in help even with example. learn how to create parameter and set its value.....

    the other side is query. build the query and then go to parameters on query menu. give a name to the parameter and type. use that same name in criteria row just put in [] for example if your parameter is named DEPARTMENT and type text, you should put in criteria [DEPARTMENT]

    now on ADO side you should name your parameter DEPARTMENT, and set a value of department you want to filter out....

    this should work....

    details for ADO you really have to look up and study all of it....

  5. #5
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by Strucnjak
    you'll have to work on details, but here is what you should do:

    since you already know ADO, look up parameter object, you can find it in help even with example. learn how to create parameter and set its value.....

    the other side is query. build the query and then go to parameters on query menu. give a name to the parameter and type. use that same name in criteria row just put in [] for example if your parameter is named DEPARTMENT and type text, you should put in criteria [DEPARTMENT]

    now on ADO side you should name your parameter DEPARTMENT, and set a value of department you want to filter out....

    this should work....

    details for ADO you really have to look up and study all of it....
    OK I will get me something to drink and will go through the helpfiles searching for answers.
    But if I don't get it all figured out I will be back.

Posting Permissions

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