Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: change to "run with owners permission" on many queries using code?

    I have a database with LOTS of queries, which are all set to run with users permission. I need to change to owners permission.

    Can anybody please provide some sample code to do this with vba, so I do not have to manually edit each one?
    Last edited by kedaniel; 02-24-04 at 13:18.

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Re: change to "run with owners permission" on many queries using code?

    What I am looking for is some way to say "for each query, set run permission to Owner's".

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: change to "run with owners permission" on many queries using code?

    Originally posted by kedaniel
    I have a database with LOTS of queries, which are all set to run with users permission. I need to change to owners permission.

    Can anybody please provide some sample code to do this with vba, so I do not have to manually edit each one?
    Just a possibility. Using the WITH OWNERACCESS OPTION at the end of your SQL statement gets you owners permissions. Why couldn't you open up your database in code and loop through the queries changing the SQL property by appending the WITH OWNERACCESS OPTION on to the end.

    For instance:

    Dim db as database, qdf as querydef
    Set db = CurrentDb
    For each qdf in db.QueryDefs
    qdf.SQL = qdf.SQL & " WITH OWNERACCESS OPTION"
    Next


    I have not tried this yet but it sounded plausible.

    Maybe.....?
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Re: change to "run with owners permission" on many queries using code?

    Originally posted by basicmek
    For each qdf in db.QueryDefs
    qdf.SQL = qdf.SQL & " WITH OWNERACCESS OPTION"
    Next
    Thanks :-) , looks interesting, will test it
    - However, isn't there a semicolon at the end of the existing statemetns that needs to be "moved do the end"?

Posting Permissions

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