Generate a random password with VBA

Most real-world databases have to be protected with a password and when a user forgets their password someone as to generate a temporary random password until that user can get back in and reset their password to something memorable. You could of course just give them something easy like "secret" as their temporary password but, human nature being what it is, they'd probably be so happy to have an easy-to-remember password that they wouldn't want to change it.

This routine solves that problem by generating a random string as a password.

Public Function GeneratePwd(intLength As Integer,_
                            booMixed As Boolean,_
                            booDigits As Boolean)

'-- Start with a password of spaces
strPassword = Space(intLength)

'-- Put in about a third of the characters as lower
'-- case if we need a mixed password
If booMixed Then
  intLower = Int(intLength / 3)
   For intN = 1 To intLower
     intPos = 1 + Int((intLength) * Rnd())
     strChar = Chr(97 + Int(26 * Rnd()))
     Mid(strPassword, intPos, 1) = strChar
   Next intN
End If

'-- Convert about a third of the spaces to digits if
'-- we need digits.
If booDigits Then
   intDigits = Int(intLength / 3)
   Do While intDigits > 0
     '-- Note that we don't put a digit at the
     '-- start of the password.
     intPos = 2 + Int((intLength - 1) * Rnd())
     If Mid(strPassword, intPos, 1) = " " Then
       '-- Don't use zero and one because they're
       '-- confusing to some users
       strChar = Chr(50 + Int(8 * Rnd()))
       Mid(strPassword, intPos, 1) = strChar
       intDigits = intDigits - 1
     End If
   Loop
End If

'-- Now fill the remaining spaces with random
'-- uppercase characters
For intN = 1 To intLength
   strChar = Mid(strPassword, intN, 1)
   If strChar = " " Then
     strChar = "O"
     '-- Skip upper case "o" and "I" because they're
     '-- confusing
     Do While strChar = "O" Or strChar = "I"
       strChar = Chr(65 + Int(26 * Rnd()))
     Loop
     Mid(strPassword, intN, 1) = strChar
   End If
Next intN

GeneratePassword = strPassword
End Function

Using the function

The function takes three parameters to set the length of the password, whether it should be mixed case and whether it should include digits. The basic password comes out in uppercase. If you specify mixed case then there will some lower case letters in the password - somewhere between one character and a third of the total. Similarly with digits.

This call for example:

pwd = GeneratePassword(8, False, True)

will generate an eight-letter password of uppercase letters and digits. Don't forget to call Randomize before calling this function. If not, the random number generator will always give you the same password.

Related Items

Custom Toolbars

Your Access database will look more impressive if you add custom toolbars.

Read More

Trust Center in Access 2007

Trust Center in Access 2007

Using the Trust Center in Access 2007

Read More

Access 2002 and Office XP

Notes on the launch of Microsoft Access 2002 and Office XP

Read More

Frequent, automatic backups

How to create backups automatically in FoxPro or Access using the Windows Scripting Host

Read More

Visual Basic equivalent of FoxPro file name function

VBA equivalents of FoxPro functions to manipulate names of files and folders

Read More