Generate set of Passwords in Access

Anonymous
2011-06-21T06:23:05+00:00

I ha ve an access database that contains the following columns :

UserID |First name  | Last Name | Username

I would like to add a password column as well and generate random passwords for the number of records found in my database , example if I have 50 records then I need to generate 50 password , 1 for every record

Any idea ?

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2011-06-23T16:58:46+00:00

What are the better ways ?

 

As suggested upthread, include both a Password field and a date/time field PasswordExpires. When you create a new user give them a password that's really easy - "password" for example! - and set the PasswordExpires field to =Date().

Then in your logon process check the expiration date. If it's less than or equal to Date() make them assign a new password of their own choice. You can enforce "strong" passwords by (say) ensuring that their entry is of mixed case or contains at least one number:

Dim blnPwdOk As Boolean

If StrComp([txtPassword], LCase([txtPassword]), 0) = 0 Then

  MsgBox "Password must contain at least one uppercase letter", vbOKOnly

  blnPwdOk = False

Elseif StrComp([txtPassword], UCase([txtPassword]), 0) = 0 Then

  MsgBox "Password must contain at least one lowercase letter", vbOKOnly

  BlnPwdOk = False

< use Instr to check for digits and/or special characters >

You may also want to set the PasswordExpires field to some date (say three months in the future) after you update the password.

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2011-06-23T14:29:38+00:00

Use an update query to populate all the records.

UPDATE YourTable

SET Password = fGenPassword(10,[UserName])

WHERE Password is Null

To do this is query design mode

Open up a query on your table

Set its type to UPDATE query

Select the Password field

Set the criteria to Is Null

Set the Update To: fGenPassword(10,[UserName])

If you want to generate different lengths of passwords change the expression to somethng like the following.

fGenPassword(Nz(Len([First Name]),10),[UserName])

Was this answer helpful?

0 comments

14 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-06-22T06:08:27+00:00

    Code is not running , whenever I click the button to execute it , nothing happen

    can you please provide me with more details ?

    Was this answer helpful?

    0 comments
  2. ScottGem 68,855 Reputation points Volunteer Moderator
    2011-06-21T19:34:27+00:00

    I ha ve an access database that contains the following columns :

     

    UserID |First name  | Last Name | Username

     

    I would like to add a password column as well and generate random passwords for the number of records found in my database , example if I have 50 records then I need to generate 50 password , 1 for every record

    Any idea ?

    I have to question this. Are you trying to generate passwords for each user? If so, WHY? Users hate having to remember an assigned password, and will generally write it down and not hide it, negating your security.

    On my databases, I set the default password to something uniform like password. I then have another field in my User table named PWReset. When the password is set to the default, PWReset is set to Yes. So when a user logs in for the first time, they are prompted to choose their own password. I detail this in my blog on Login Security using VBA.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-06-21T12:42:39+00:00

    You can use a VBA function to generate the password.   And call it from your query.

    If you want to change the range of characters returned, change 125 to the ascii value of the highest character and 33 to the value of the lowest character you want in the password.

    Public Function fGenPassword(iLen As Integer, vAnyField as Variant)

    'vAnyField is required to force the function to be called for each row in the query

    Dim iLoop As Integer

    Dim iRandom As Integer

    Dim strReturn As String

       Randomize

       For iLoop = 1 To iLen

          strReturn = strReturn & Chr(Int((125 - 33 + 1) * Rnd + 33))

       Next iLoop

       fGenPassword = strReturn

    End Function

    Was this answer helpful?

    0 comments