How do I grant permissions to a table in Microsoft Access using SQL?

[erkle]

I know how to grant permissions in the GUI but I need t be able to do this through code. Can anyone help?

 

[cmmrfrds]

Are you using access 97 or 2000?

It can be done with DAO or ADO. The librairies and methods will be different for each. If personally use ADO and could give you some sample code to get you started. If you prefer DAO, hopefully somebody here can provide an example.

Let me know if you want an ADO example.

 

[gdervage]

I would also like a copy of the sample ADO Code.
Thanks

 

[erkle]

Yes, please send me the ADO code to grant permissions to a table of Access database.

 

[cmmrfrds]

This is a very basic example, but a good starting point. Paste this function in the standards module and run it from a macro. Of course, where table names are needed you will use your table names. There is a setpermissions method as well as getpermissions method. While in vba code right click on one of the methods or properties to bring up the ADOX library where you can explore the methods, properties, and objects.


Function JetSecurity()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Set cg.ActiveConnection = CurrentProject.Connection

Dim ur As User, gp As Group
For Each ur In cg.Users
Debug.Print "users = "; ur.Name
'- 0 = no permissions, the -number is a bitmap for a set of enumerations
Debug.Print "permissions = "; ur.GetPermissions("IDTable", adPermObjTable)
Next
For Each gp In cg.Groups
Debug.Print "groups = "; gp.Name
Next

End Function