Can't Access Users collection in ADOX (Jet OLEDB)
When using the ADOX library, I find that I am unable to work with the
user-level security of
my Access databases.
I am working in VB 6, but have been able to replicate this same problem in
Access 2000. I have
the project references set to (among other things) Microsoft ADO Extension
for DDL and Security,
version 2.6. According to all the examples on MSDN, I should be able to use
this library to
create and construct a new Access 2000 database within my VB code (using the
OLE DB provider for
Jet, version 4.0).
For the most part, I am able to accomplish everything that I want to do
apart from handing the
user and group permissions in my new database. I can create the database,
add tables to it, add
fields to my tables, and get everything exactly as I want it. When I
attempt to refence the Users
or Groups collections, however, I get a run-time error 3251 (Object or
provider is not capable of
performing requested operation).
I have included some sample code that causes this problem for me, but I get
the same error even
when I copy example code directly out of MSDN and try to run it. Basically,
the ADOX library
lets me do everthing I want to do with creating a new database, but I can't
touch anything that
has to do with Users or Groups.
I have tried adding references to the Jet and Replication Objects library.
I don't know what else
to try.
' --------------------------------------------------------------------------
-----------------------
Sub CreateDatabase()
Dim catNew As ADOX.Catalog
Dim tblFlowers As ADOX.Table
Dim keyFlowerPK As ADOX.Key
Dim usrLoop As ADOX.User
Dim grpLoop As ADOX.Group
'If the created database already exists on the C:\ drive, delete it. Error
handling is turned on
'briefly in case the file does not yet exist.
On Error Resume Next
Kill "C:\New Database.mdb"
On Error GoTo 0
'Create the new database.
Set catNew = New Catalog
Call catNew.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\New
Database.mdb")
'Create tblFlowers, along with its three fields.
Set tblFlowers = New Table
tblFlowers.Name = "tblFlowers"
Call tblFlowers.Columns.Append("FlowerID_PK", adInteger)
Call tblFlowers.Columns.Append("FlowerName", adVarWChar, 60)
Call tblFlowers.Columns.Append("FlowerColor", adInteger)
'Set the first field in tblFlowers to be the primary key.
Set keyFlowerPK = New Key
keyFlowerPK.Name = "PrimaryKey"
keyFlowerPK.Type = adKeyPrimary
Call keyFlowerPK.Columns.Append("FlowerID_PK")
Call tblFlowers.Keys.Append(keyFlowerPK)
'Append the table into the new database.
Call catNew.Tables.Append(tblFlowers)
'At this point, everything is fine with the new database. It can be opened
in Access, and its
'single table is set up correctly. But for some reason, I cannot get the
provider to deal with
'the Users collection...
'Attempt to access the Users collection of the new database; this line
causes an error.
Call catNew.Users("Admin").SetPermissions("tblFlowers", adPermObjTable,
adAccessGrant, adRightRead)
'It's not the SetPermissions method that is causing the error. Anything
involving the Users
'collection fails, including the following rather simple request. It also
seems to be impoossible
'to use the Groups collection.
For Each usrLoop In catNew.Users
Debug.Print usrLoop.Name
Next usrLoop
For Each grpLoop In catNew.Groups
Debug.Print grpLoop.Name
Next grpLoop
End Sub
' --------------------------------------------------------------------------
-----------------------
If anyone has any pointers, I would be most grateful.
Thans again.
