Permissions with VBA

[Steve Marsden]

I have a secured database which comprises several tables. Generally I want
users who log on to only have read access to the tables and this how their
permissions are set.

However, there is one place in my system where some VB code runs and it
needs to update the tables they only have read permissions to.

How can I change the permission temporarily with VB code so that the VB code
can update the table and then set the permission back to read only?

I have tried the following similar to that listed in the security FAQ but
this fails for the users presumably because they don't have permission to
change permissions!

Set db = CurrentDb()
Set con = db.Containers("Tables")
Set doc = con.Documents("Customers")
doc.UserName = "Users"
doc.Permissions = doc.Permissions Or dbSecInsertData

' code here to update customers table

doc.Permissions = doc.Permissions And Not dbSecInsertData

 

[Tim Ferguson]

However, there is one place in my system where some VB code runs and it
needs to update the tables they only have read permissions to.

Look up the WITH OWNERACCESS option in SQL help.

The principle is that you, as admin, create and own the query and therefore
have the rights to do the update or whatever. The users are given read and
execute permission on the query, so they can get the query to do its job
even though they would not be able to do it themselves.

It's easier to do than to explain!

All the best


Tim F