Creating User Accounts 1

[CharlieT302]

Hello,

I have a database that is written in a 2003 format in order to take advantage of user accounts. I need to set up a user form to allow for easier creation of user accounts and to assign them to groups. With this in mind, I have two questions:

First,
The initial code that specifies the username, password, and PID works fine.
CurrentProject.Connection.Execute "CREATE USER Rsmith pword 12345;"

However, I would like to be able to replace those values with a field reference on an entry form, so that the administrator can dynamically choose a user account, group, etc. I have tried several variations and cannot get the code to read the form.

Any variation of the code below has not worked.

Dim usrNew, grpNew, passnew, PidNew

usrNew = Me.NewUser
grpNew = Me.NewGroup
passnew = "password"
PidNew = usrNew & "23"

CurrentProject.Connection.Execute "CREATE USER [usrNew] pword [PidNew];"
CurrentProject.Connection.Execute "ADD USER usrNew TO grpnew;"

Seoondly, I would like to view, from the database file, the contents of the MSysGroupList and MSysUserList tables; which are in the workgroup file (.mdw). The intent is to display the user and group accounts in a list box so the administrator can more easily assign users to group account simply by selecting the desired account from the list. How can I view these lists from the database file?

 

[Andrzejek]

For your first question, try this approach:

Dim usrNew As String
Dim grpNew As String
Dim passnew As String
Dim PidNew As String

Dim str As String

usrNew = Me.NewUser
grpNew = Me.NewGroup
passnew = "password"
PidNew = usrNew & "23"

With CurrentProject.Connection
    str = "CREATE USER " & usrNew & " pword " & PidNew & ";"
    Debug.Print str
    .Execute str
    str = "ADD USER " & usrNew & " TO " & passnew & ";" 
    Debug.Print str
    .Execute str
End With

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[CharlieT302]

Andrzejek,

Thanks a lot. For the most part that worked great. However, when I attempt to create the password in a similar fashion, it fails. See below.

Dim usrNew As String
Dim grpNew As String
Dim passnew As String
Dim PidNew As String

Dim str As String

usrNew = Me.NewUser
grpNew = Me.NewGroup
passnew = Me.NewPass
PidNew = usrNew & "23"

With CurrentProject.Connection
    str = "CREATE USER " & usrNew & passnew & PidNew & ";"
    Debug.Print str
    .Execute str
    
    
    str = "ADD USER " & usrNew & " TO " & grpNew & ";"
    Debug.Print str
    .Execute str
End With

 

[Andrzejek]

First line is what I gave you,
Second what you have now:
[tt]
str = "CREATE USER " & usrNew & " pword " & PidNew & ";"
str = "CREATE USER " & usrNew & passnew & PidNew & ";"
[/tt]
Do you see the difference?
That's why you have the [tt]Debug.Print str[/tt] lines to see and evaluate what is going to be executed.

Unless by "it fails" you mean something else....?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[CharlieT302]

Andrzejek,

I do see the difference and your code worked fine. However, the code you gave me is assigning the password as: pword.

What I am saying is that I would like the user to be able to specify a password on a form field rather than having the password hard coded.

Notice that I have:
passnew = Me.NewPass

This is a field I set up on my form to capture a user-defined password. I am just trying to have the code read the password form field as it does with the other fields.

 

[Andrzejek]

From your OP, I've assumed the BLACK portion of the expression is hard-coded, and the [red]RED[/red] portions are what you want to reaplce:
[tt]
"CREATE USER [red]Rsmith[/red] pword [red]12345[/red];"[/tt]
That's why I gave you:[tt]
"CREATE USER [red]" & usrNew & "[/red] pword [red]" & PidNew & "[/red];"[/tt]

If that is NOT the case, please state which portions are hard-coded and which are not.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[CharlieT302]

I would prefer to have nothing hard-coded. I would like everything to come from a form field.

 

[Andrzejek]

I understand, but [tt]CREATE USER[/tt] is a part of the statement and will not come from a form field. It is hard-coded (unless I am missing something here....?)
So again, which portions are hard-coded and which are not?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[CharlieT302]

I just got it to work. See below. Thank you very much for your help!

Now, if anyone knows a way of reading the MsysGroupList table in the .mdw file, please let me know. The code below works fine but I need the ability to add multiple groups at once rather than one at a time.

My hope was to read and list the available user and groups accounts. In this way, the administrator could select a user and assign them to any of the available groups.

Dim usrNew As String
Dim grpNew As String
Dim passnew As String
Dim PidNew As String

Dim str As String

usrNew = Me.NewUser
grpNew = Me.NewGroup
passnew = Me.NewPass
PidNew = usrNew & "23"

With CurrentProject.Connection
    str = "CREATE USER " & usrNew & " " & passnew & " " & PidNew & ";"
'    usrNew.Password = passnew
    Debug.Print str
    .Execute str
    
    str = "ADD USER " & usrNew & " TO " & grpNew & ";"
    Debug.Print str
    .Execute str
End With

 

[Andrzejek]

Just a side note:
Now that you have grasped the idea of how to do it, you may make it a lot shorter :

[green]
'Dim usrNew As String
'Dim grpNew As String
'Dim passnew As String
'Dim PidNew As String

'usrNew = Me.NewUser
'grpNew = Me.NewGroup
'passnew = Me.NewPass
'PidNew = usrNew & "23"[/green]

Dim str As String

With CurrentProject.Connection
    str = "CREATE USER " & Me.NewUser & " " & Me.NewPass & " " & Me.NewUser & "23;"
    .Execute str
    
    str = "ADD USER " & Me.NewUser & " TO " & Me.NewGroup & ";"
    .Execute str
End With

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[CharlieT302]

Yes. Absolutely. Actually, I started out that way but when I couldn't get the syntax correct, I started experimenting. You really helped out. Now If I can just figure out how to link to the tables in the workgroup file, I will be all set. That's todays project!

 

[Andrzejek]

That's why I usually start with the hard-coded statement, and then start replacing bits and pieces with variables, and use Debug.Print statement to see what I have created before I execute it.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[CharlieT302]

Hello All,

Another wrinkle came up. I was able to install code that:
[ul]
[li]Creates and Removes User Accounts[/li]
[li]Display a list of all User Accounts[/li]
[li]Display a list of all Group Accounts[/li]
[li]Display a list of Group Accounts for the selected user[/li]
[/ul]
My issue now is that when I create a user account, I would like the following to be updated:
[ul]
[li]The list of All User Accounts (to reflect the new account)[/li]
[li]The list of group accounts associated with the newly created user.[/li]
[/ul]

The User List and Associated Groups list will only update if I close the entire database (not just the form) and the reopen it. My thought is that the Workspace or dbEngine must need to be requeried, but I am unsure how to do so.

Below is the code I use to populate the list of groups associated with the "selected" (existing) user. However, this works only when the form is opened for the first time; not with a user just created. I thought I could transfer this to the code that creates the user account, but it didn't work. Probably because it is simply creating a list from the data already in memory.

Set WrkSpc = DBEngine.Workspaces(0)
Dim strAccountName As String
Dim grpAccountName As String
Dim usr As User
Dim grp As Group

User_Grp_Accounts: ' lists all accounts assigned to selected user.
Me.User_Groups.RowSource = ""
For Each grp In WrkSpc.Users(Me.User_List).Groups
If IsNull(Me.User_List) Then
GoTo exit_sub
Else
If grp.Name <> "Admins" And grp.Name <> "Users" Then
grpAccountName = grpAccountName & ", " & grp.Name
Me.Group_List.RowSourceType = "Value List"
User_Groups.RowSource = grpAccountName
End If
End If
Next

How can I requery this list?

 

[kjv1611]

OP posted the new question in new thread:
thread705-1781705

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57