Creating Object and Setting Permissions for it Through Code--Is it possible?

  • Thread starter Thread starter Brian Smith
  • Start date Start date
B

Brian Smith

I would like to able to create a query on the fly and have any user (even
those with only read permission for new queries) to be able to run this
query and see the results. Is it possible to assign permissions to an object
that is created through code as soon as that object is created?

Thanks.

Brian
 
Sure. See CreateQueryDef for how to create a new query. As for setting
the permissions, I don't have Access here to check, but something along
the following lines should work:

(untested)

dim db as database, con as container, doc as document
set db = currentdb()
set con = db.containers![querydefs]
set doc = con.documents![Your_Query_Name]
with doc
.user = "the user you want to set the permissions for"
.permissions = acReadDef OR ... (whatever)
end with
set doc = nothing
set con = nothing
set db = nothing

I can't remember whether a "document" object (named 'doc' in the code
above) has a "group" property to let you set permissions for a group
(instead of a user). If it does - that's how you do that! If it
doesn't, I can't remember & would have to check.

As I say - untested.

HTH,
TC
 
TC said:
Sure. See CreateQueryDef for how to create a new query. As for setting
the permissions, I don't have Access here to check, but something along
the following lines should work:

(untested)

dim db as database, con as container, doc as document
set db = currentdb()
set con = db.containers![querydefs]
set doc = con.documents![Your_Query_Name]
with doc
.user = "the user you want to set the permissions for"
.permissions = acReadDef OR ... (whatever)
end with
set doc = nothing
set con = nothing
set db = nothing

I can't remember whether a "document" object (named 'doc' in the code
above) has a "group" property to let you set permissions for a group
(instead of a user). If it does - that's how you do that! If it
doesn't, I can't remember & would have to check.

As I say - untested.

HTH,
TC
Click to expand...

TC, thanks for putting me on the right track concerning the permissions. You
were very close in what you posted off the top of your head. For the benefit
of others, here is the code I used for a query called MyQuery and two user
groups (Full Data Access and Read-Only Access).

Dim db As Database, con As Container, doc As Document
Set db = CurrentDb()
Set con = db.Containers![Tables]
Set doc = con.Documents![MyQuery]
With doc
.UserName = "Read-Only Access"
.Permissions = dbSecReadDef Or dbSecWriteDef
.UserName = "Full Data Access"
.Permissions = dbSecReadDef Or dbSecWriteDef
End With
Set doc = Nothing
Set con = Nothing
Set db = Nothing

From playing around and finding some documentation it appears that you need
to use Tables as the container and UserName refers to users and groups. This
last point leads to another question. Does this imply that you cannot have a
User with the same name as a Group?

Brian
 
Yes, you are right about the Tables container. That container is for
tables /and/ queries, I had forgotten that :-)

Yes, you can not have a group with the same name as a user as far as I
remember. Each user & group has an entry in the MSysAxccounts table of
the workgroup file. That table is probably primary-keyed by user/group
name, so you could not have two users - or two groups - or a user and a
group - with the same name.

Well done for getting it working!

TC
 
Back
Top