Programming Security Permissions using VBA

[cani]

Hello,

I had an access (mdb file) 97 database that used code to assign security permissions to a table for a particular group. It worked fine (the code is below). I then upgraded to Office XP and the file was converted into 2000 file format. The procedure stopped working. Now I get the following message:

"Run-time error '3265': Item not found in this collection"

When I debug, it is bombing out on setting the document ("Set DocSPS = CntSPs.Documents(VarObjName)" below)

Code (function):

Function SETPERMISSIONSUB2(ByVal lngPerm2 As Long, ByVal intObjType2 As Integer, ByVal intAction2 As Integer, Optional ByVal varObjName2 As Variant, Optional ByVal varAccount2 As Variant) As Boolean

'Adds permissions for table

Dim dbSPS2 As DATABASE
Dim CntSPS2 As Container
Dim DocSPS2 As Document
Set dbSPS2 = CurrentDb()
Set CntSPS2 = dbSPS2.Containers(intObjType2)
CntSPS2.Documents.Refresh
CntSPS2.UserName = varAccount2
Set DocSPS2 = CntSPS2.Documents(varObjName2)
DocSPS2.UserName = varAccount2
DocSPS2.Permissions = DocSPS2.Permissions Or lngPerm2
SETPERMISSIONSUB2 = True

End Function

Code (Call):

Call SETPERMISSIONSUB(dbSecInsertData, 7, 2, "BUDGET" & [NEWDATABASE], "GCS Controller&quot

After converting to 2000 file format, I've also tried SQL grant/revoke statements and ADOX, etc., by copying code from a variety of Access programming books but have been unsuccesful--don't know whether I'm missing a library reference item to get these grant/revoke or ADOX statements working or whether I need to convert the file into an Access project (whatever that is).

Can someone help me to assign permissions using code? Although I would prefer to stick with DAO since basically everything else in the database is DAO, I would try anything as long as I can revoke the modify design permission on the table for a particular group.

 

[Junior1544]

i have a feeling that you don't have the DAO 3.6 reference in vba... try selecting that reference and moving it above ado... and let us know how it works... (this can be done by getting into the vba window from within access and selecting Tools > References... try that...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[cani]

Thanks, Junior.

"Microsoft DAO 3.6 Object Library" is currently the 3rd item on the list below "Visual Basic For Appications", and "Microsoft Access 10.0 Object Library". "Microsoft ADO Ext 2.5 for DDL and Security" is number 5 on the list.

Any other suggestions?

Thanks again.