A User object represents
a user account that has specific access permissions while a Group
object represents a group of user accounts that have common access permissions.
Creating users and groups allows you to easily control and maintain users' access
to the database and objects within the database.
The following code example shows how to create a
new user.
DAO
Sub DAOCreateUser()
Dim wks As DAO.Workspace
' Open a workspace
DBEngine.SystemDB = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"
Set wks = DBEngine.CreateWorkspace("", "Admin", "password")
' Create the user and append it to the Users collection
wks.Users.Append wks.CreateUser("MyUser", "xNewUser", "password")
End Sub
ADOX
Sub ADOCreateUser()
Dim cat As New ADOX.Catalog
' Open the catalog, specifying the system database to use
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW;" & _
"User Id=Admin;Password=password;"
' Create the new user and append it to the users collection
cat.Users.Append "MyUser", "password"
End Sub
Unlike with DAO, with ADOX you do not have to
create a User object before adding the user to the
database with the Append method. With ADOX you can
create a new user simply by passing the name and password to the Append method of the Users
collection.
Note Before using this
technique to create User objects, see the comments about
setting PID values in the section "Adding a User to a Group."
Setting PID values
When creating a user or group in DAO, you must
provide a value for the PID parameter or property. This parameter is not required
when creating a new user or group in ADOX because the Microsoft Jet Provider
automatically generates PID values. This PID value is not retrievable using
ADOX or DAO.
This presents problems if the workgroup
information file, such as system.mdw, becomes corrupted or destroyed. In order
to recreate users and groups and restore permissions to your databases and
their objects, you must know the PID values. Microsoft Jet combines the user or
group name and the PID value to create the identifier, called the SID, that it
uses to control the permissions of databases and objects. By recreating a user
or group with its original name and PID value, Microsoft Jet will create a SID
value identical to the original value, so all permissions set originally using
that SID will work again. Since you cannot set the PID value using ADOX, the
Microsoft Jet Provider effectively creates a new SID that does not have any
database or object permissions set for it.
There are two methods for working around this
problem if you choose not to explicitly set the PID values. One is to make
frequent backups of the workgroup information file, and restore the file from
the backup when necessary. This is usually the most efficient way to avoid the
problem. The second method is to recreate all of the permissions in the
database to work with the new users and groups that you will create. This is
usually difficult to accomplish efficiently. Both methods do not require you to
keep track of the PID values for users and groups.
Another way to avoid this problem is to use the
new SQL Data Definition Language (DDL) security commands available for
Microsoft Jet 4.0. The following code example demonstrates how to create a user
and specify a PID value using ADO and DDL.
ADO
Sub ADOCreateUser2()
Dim cmd As New ADODB.Command
' Create the Command
cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;Jet OLEDB:System database=" & _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW;" & _
"User Id=Admin;Password=password;"
' Execute the DDL security command
cmd.CommandText = "CREATE USER MyUser MyPW MyPID"
cmd.Execute
End Sub
EmoticonEmoticon