INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Creating User Accounts

Creating User Accounts

(OP)
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.

CODE --> VBA

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?

RE: Creating User Accounts

For your first question, try this approach:

CODE

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.

RE: Creating User Accounts

(OP)
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.

CODE --> VBA

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 

RE: Creating User Accounts

First line is what I gave you,
Second what you have now:

str = "CREATE USER " & usrNew & " pword " & PidNew & ";"
str = "CREATE USER " & usrNew & passnew & PidNew & ";"

Do you see the difference?
That's why you have the Debug.Print str 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.

RE: Creating User Accounts

(OP)
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.

RE: Creating User Accounts

From your OP, I've assumed the BLACK portion of the expression is hard-coded, and the RED portions are what you want to reaplce:

"CREATE USER Rsmith pword 12345;"

That's why I gave you:
"CREATE USER " & usrNew & " pword " & PidNew & ";"


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.

RE: Creating User Accounts

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

RE: Creating User Accounts

I understand, but CREATE USER 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.

RE: Creating User Accounts

(OP)
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.

CODE --> VBA

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 

RE: Creating User Accounts

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

CODE


'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"

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.

RE: Creating User Accounts

(OP)
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!

RE: Creating User Accounts

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.

RE: Creating User Accounts

(OP)
Hello All,

Another wrinkle came up. I was able to install code that:
  • Creates and Removes User Accounts
  • Display a list of all User Accounts
  • Display a list of all Group Accounts
  • Display a list of Group Accounts for the selected user
My issue now is that when I create a user account, I would like the following to be updated:
  • The list of All User Accounts (to reflect the new account)
  • The list of group accounts associated with the newly created user.
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.

CODE --> VBA

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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close