Query-Security-Permissions (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks Charlotte. Works great!!

Reply | Quote

Hi Charlotte,

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

Reply | Quote

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

Reply | Quote

Excellent!

Reply | Quote

Excellent!

Reply | Quote

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

Reply | Quote

Hi Charlotte,

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

Reply | Quote

Thanks Charlotte. Works great!!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote