Access 2010 query to create random password - creates same random password for all records

Question

I have found some very useful vba code to generate a random password that is going into one field in a table. The issue is that Access is generating a random password only once per query and hence using the same password for every record created. Is there a way to force Access to run the function code once for each record instead of once per query?

Here is the SQL from the query:

SELECT DISTINCT BASE_USER_TABLE.ID, BASE_USER_TABLE.LAST_NAME, BBASE_USER_TABLE.FIRST_NAME, [BASE_USER_TABLE]![FIRST_NAME] & " " & [BASE_USER_TABLE]![LAST_NAME] AS FULL_NAME, UCase([BASE_USER_TABLE]![FIRST_NAME] & "." & [BASE_USER_TABLE]![LAST_NAME]) AS USERNAME, Random([«Rlength»]) AS [PASSWORD], INTO USER_ACCT_TABLE

FROM BASE_USER_TABLE;

Here is the code I found for the password generation (with credit to the person that generated it)

Public Function Random(Rlength As Integer) As String

' This function creates a string of random characters, both numbers

' and alpha, with a length of RLength.  It uses Timer to seed the Rnd

' function.

' Random() Version 1.0.0

' Copyright © 2009 Extra Mile Data, www.extramiledata.com.

' For questions or issues, please contact *** Email address is removed for privacy ***.

' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_Random

Dim strTemp As String

Dim intLoop As Integer

Dim strCharBase As String

Dim intPos As Integer

Dim intLen As Integer

' Build the base.

strCharBase = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" _

& "abcdefghijklmnopqrstuvwxyz" _

& "0123456789"

' Get it's length.

intLen = Len(strCharBase)

' Initialize the results.

strTemp = String(Rlength, "A")

' Reset the random seed.

Rnd -1

' Initialize the seed using Timer.

Randomize (Timer)

' Loop until you hit the end of strTemp.  Replace each character

' with a character selected at random from strCharBase.

For intLoop = 1 To Len(strTemp)

' Use the Rnd function to pick a position number in strCharBase.

' If the result exceeds the length of strCharBase, subtract one.

intPos = CInt(Rnd() * intLen + 1)

If intPos > intLen Then intPos = intPos - 1

' Now assign the character at that position in the base to the

' next strTemp position.

Mid$(strTemp, intLoop, 1) = Mid$(strCharBase, intPos, 1)

Next

' Return the results.

Random = strTemp

Exit_Random:

On Error Resume Next

Exit Function

Err_Random:

MsgBox Err.Number & " " & Err.Description, vbCritical, "Random"

Random = ""

Resume Exit_Random

End Function

On another very vba noob note, I have also been looking for a way to set the RLength to a static 12 characters, rather than having to specify is whenever I run the query. I have tried to do that, but I am clearly using the wrong syntax/commands, because the function completely breaks when I do.

Thanks for any info or hints,

The Neophyte

Answer 1

Access will "save time" by calling a function only once and stashing the result, unless you pass the function a field from the query.

You can kill two birds with one stone by using

Random(12 + 0*[ID])

in the query - this will pass a constant 12 for the length, and still trick Access into thinking you're also passing the ID so it will call the function anew for each row.

Just save the query with this expression in it, you should not need to reenter anything.

---

John W. Vinson/MVP

Answer 2

Hey guys,

I thought I had this, then I started using it on a grander scale and I realized that what Tom had pointed out about Access outsmarting me and realizing that ID*0 was always 0. When I do this for a few dozen records at a time at a time Access uses the same randomly generated password for a group of records, between 5 and 10, before it jumps to the next random password which it then reuses for another 5 or 10. The really odd thing is that if I view the query in datasheet view, and click on an individual record it will update to something random and truly unique.

I am at a loss, I even tried rewriting the function per Tom's suggestion and the result is still the same.

Any other ideas?

Thanks,

The_Neophyte

Answer 3

Just a very brief mention of the use of Rnd() for generating random sequences. I'm not sure if this is relevant to your situation but you should take a look at:http://www.15seconds.com/issue/051110.htm

This is a fairly lengthy article about using VBA Rnd() for generating random sequences for secure use. The summary of the article though may prove of interest:

What This Means to Developers

This is a summary of the weaknesses and problems with the VB PRNG.

• The Rnd() sequence cycles every 16.77 million items. If you need to create very long random number sequences, like those used in disk-wiping utilities, this period is much too short. If you use enough write operations, you might overcome this deficiency. For some cryptography operations, this short period is a 'stopper'.
• The Rnd() sequence can be cracked fairly easily, since it implements a simple linear congruent formula.
• The same starting value is used if no Randomize statement precedes the first Rnd() invocation.
• The seed is not reset if Rnd -1 (or any negative number) does not precede the Randomize statement. In other words, the stated purpose and function of the Randomize statement doesn't always happen.
• Since the Timer() values repeat every day, the Randomize statement is susceptible to the reuse of seed values.
• However, the most severe weakness in the VB PRNG is that there are only about 64K possible (post-mosh) unique seeds! This means that there are only 64K unique random sequence starting points!!

Answer 4

Thank you (and Tom) for the excellent quick support on this. Your answer(s) were awesome and worked exactly as I needed. The only hitch I ran into was that the [ID] field contains non-numeric characters, so initially the solution offered generated an error for the password. It took me a second to realize 0 times a letter is not gonna work for Access. ;-)

Anyway, there is another unique field that is all numeric in my source table, so I figured it out, and it works perfectly!

Thank you both again.

• The Neophyte

Answer 5

John already gave you the answer. In case you're interested in a bit of background info: we call those functions "deterministic" which always return the same value for the same input. Sin and Abs are examples of that. So is your function in its original version.

John made it non-deterministic. These functions do not return the same value for the same input. Now() is an example, so is Random(12+0*[ID]). Of course this means you're lucky that the query parser at least in this version of Access is not smart enough to realize that 0*x is 0, which would indicate deterministic is good enough. I might rewrite your Random function as:

Public Function Random(byval Rlength As Integer, byval dummy as long) As String

Then call it using "select Random(Rlength, ID) from someTable".

---

-Tom. Microsoft Access MVP