VBA code granting privileges

[Lincoln]

I have a secured database created with Access 2003, and used by Office
2007 users. I need to grant privileges to a query; that for reasons of
design, is deleted and recreated based on user input. It seems that
deleting the query removes the privileges with it. Obviously their may
be other ways of doing this, but I am locked in.

I am looking to grant privileges to a query, lets call it,
"my_deleted_and_replaced_query" using VBA code in a module, and need
help with the syntax. It should go something like this:

Grant privileges to my_deleted_and_replaced_query for all users or
groups in the database.

Lincoln

 

[Salad]

I have a secured database created with Access 2003, and used by Office
2007 users. I need to grant privileges to a query; that for reasons of
design, is deleted and recreated based on user input. It seems that
deleting the query removes the privileges with it. Obviously their may
be other ways of doing this, but I am locked in.

I am looking to grant privileges to a query, lets call it,
"my_deleted_and_replaced_query" using VBA code in a module, and need
help with the syntax. It should go something like this:

Grant privileges to my_deleted_and_replaced_query for all users or
groups in the database.

Lincoln

Do you have to delete it?
Could you do something like
qdf.SQL = "Select * From Customers"
and later
qdf.SQL = "Select * From Employees"

 

[Lincoln]

Do you have to delete it?
Could you do something like
        qdf.SQL = "Select * From Customers"
and later
        qdf.SQL = "Select * From Employees"

Salad,
Thanks for the response; but I am not looking to re-engineer the code
right now. It is really a simple line of code, I am just not familiar
enough with Raw SQL syntax. Any help you can give would be
appreciated. A little more on what really happens.

My code looks like this:

////////////////////////////////////////////////////////////////////
Dim dbs As dao.Database
Dim qdf As dao.QueryDef
Dim StrSQl As String
Set dbs = CurrentDb

DoCmd.DeleteObject acQuery, "OutputQuery"
StrSQl = Forms("Distribution")("advance_search").Form.RecordSource
Set qdf = dbs.CreateQueryDef("OutputQuery", StrSQl)
///////////////////////////////////////////////////////////////////

After the user makes a selection the query "OutputQuery" is created; I
simply want to add a statement that re-establishes permissions for all
members of the database.

This line of code does not generate an error, but it does not work; I
am sure my syntax is fudged up...
??????' StrSQl = "grant all privileges on OutputQuery to Users" ????

Lincoln

 

[Salad]

Salad,
Thanks for the response; but I am not looking to re-engineer the code
right now. It is really a simple line of code, I am just not familiar
enough with Raw SQL syntax. Any help you can give would be
appreciated. A little more on what really happens.

My code looks like this:

////////////////////////////////////////////////////////////////////
Dim dbs As dao.Database
Dim qdf As dao.QueryDef
Dim StrSQl As String
Set dbs = CurrentDb

DoCmd.DeleteObject acQuery, "OutputQuery"
StrSQl = Forms("Distribution")("advance_search").Form.RecordSource
Set qdf = dbs.CreateQueryDef("OutputQuery", StrSQl)
///////////////////////////////////////////////////////////////////

After the user makes a selection the query "OutputQuery" is created; I
simply want to add a statement that re-establishes permissions for all
members of the database.

This line of code does not generate an error, but it does not work; I
am sure my syntax is fudged up...
??????' StrSQl = "grant all privileges on OutputQuery to Users" ????

Lincoln

Let's say the current SQL for Query1 is "Select * From TimeCards
This sub sets the SQL to 3 other statements.
If you run it a second time, it will be "From CustomersDetail"

Sub QuerySQL()
Dim qdf As QueryDef
Set qdf = DBEngine(0)(0).QueryDefs("Query1")
msgbox "Current SQL " & qdf.SQL
qdf.SQL = "Select * From Customers"
msgbox qdf.Name & " " & qdf.SQL
qdf.SQL = "Select * From Employees"
msgbox qdf.Name & " " & qdf.SQL
qdf.SQL = "Select * From CustomersDetail"
msgbox qdf.Name & " " & qdf.SQL
Set qdf = Nothing
End Sub

I don't know how much reegineering is required to modify your code with
qdf.SQL = strSQLStatement
but it doesn't seem like much.