Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query-Security-Permissions (Access 97)

    Hi All,

    In a secured database, depending on the criteria, a query is deleted and recreated on the fly. A group has been given the following permissions for the query.

    Read Design Read Data
    Modify Design Update Data
    Administer Insert Data
    Delete Data


    When the query is deleted and recreated, it defaults to the following permissions.

    Read Design Read Data
    Update Data
    Insert Data
    Delete Data

    I have tried to look through the help files at Containers but can't seem to find how to re-add the Modify Design and Administer permissions to the group for the query. Does anyone have any ideas?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query-Security-Permissions (Access 97)

    Because you're deleting the query, the permissions won't apply to the recreated query. You would have to apply those permissions to new queries to make it work. Instead of doing that, why not just change the SQL property of the query on the fly. That will keep the existing permissions and not require special permissions on new queries for your users. We use this technique in our secured databases and it works quite well.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query-Security-Permissions (Access 97)

    Charoltte, thanks for replying.

    I am not sure where to begin in implementing your suggestion... "Instead of doing that, why not just change the SQL property of the query on the fly. That will keep the existing permissions and not require special permissions on new queries for your users. "

    How do you change the SQL property of the query on the fly?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query-Security-Permissions (Access 97)

    You do it in code by setting a querydef object reference and then changing the string in the SQL value of the querydef. Here's some aircode to demonstrate

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("MyQuery")
    qdf.SQL = strSQL
    qdf.Close
    Set qdf = Nothing
    Set dbs = Nothing

    In this case, strSQL represents the SQL you want to put into the query. Obviously, you can't do this from the query grid, but if you are generating the SQL in code based on form selections by the users, then this is the most straightforward way to do it and it won't change the permissions on the query since you aren't deleting it.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query-Security-Permissions (Access 97)

    Thanks Charlotte. Works great!!

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query-Security-Permissions (Access 97)

    Hi Charlotte,

    Is there a way to modify the code you gave me to extract/view the SQL from/in a query?

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

    Re: Query-Security-Permissions (Access 97)

    SQL is a property of a QueryDef object. In Charlotte's code, this property is set to another string:

    qdf.SQL = strSQL

    But you can also retrieve the SQL property and store its value in a variable for inspection and manipulation:

    Dim strSQL As String
    strSQL = qdf.SQL
    MsgBox "The SQL for " & qdf.Name & " is:" & vbCrLf & vbCrLf & strSQL

  8. #8
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query-Security-Permissions (Access 97)

    Excellent!

Posting Permissions

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