0

My Problem

  • I don't know any VBA
  • Need to generate a string of 12 chars (ALPHA + numbers , 0-9 , A-Z) IN VBA...

  • Sample output :

    "ADF3V3224K1WQ"

    (12 characters long , Higher caps Alphabetical letters , and Numeric values aswell).

Story

I'm having a hard time figuring out how to create a string generator (didn't say it has to be random, but it 100% has to be unique, meaning it has to check for duplications or store existing values), I have no experience with VBA. I already found a similar post here, but it is different then mine, as it does not specifically ask for the string to be 100% unique. (I don't need a very low chance, I need 100% across the board).

Syntax-wise I'm pretty confused, I managed to find where the code behind events are, ie Button presses, so I know where I need to write it, but I have no idea how to check for duplications. (even relying on a time seed that can ensure it will be 100% unique is great).

Link to the similar post: MS Access Visual Basic - generate random string in text field

halfer
  • 19,824
  • 17
  • 99
  • 186
Yuval
  • 180
  • 13
  • 1
    **101% Unique**. Start by asking this: where are the already used, previously generated strings stored? How? We likely dont need to store all of them, but at least we need to hold a counter somewhere. – A.S.H Jul 05 '17 at 19:51
  • Thing is , My knowledge with office access (and office in general is close to none) a wild guess (and I do mean wild) is probably in the actual table. – Yuval Jul 05 '17 at 19:54
  • If we can find out what table these values are stored in. Then this problem becomes much easier – JahKnows Jul 05 '17 at 20:03
  • It's in hebrew i'm afraid , but I do know the table. the field is the primary key. it can be reffered to as operatorID (equivalent to hebrew) , and the table tblOperator – Yuval Jul 05 '17 at 20:21
  • Please read [Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers?](//meta.stackoverflow.com/q/326569) - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. – halfer Jul 07 '17 at 07:36

3 Answers3

4

There are multiple solutions to this problem, but I like this one:

Public Function newguidx() As String
    With CreateObject("Scriptlet.TypeLib")
        newguidx = Left(.Guid, 38)
    End With  
End Function

It creates a GUID. These aren't unique per se, but you can read on the Wikipedia page that they might as well be. You can use Replace to remove the {}- signs if you want to.

Also, since the format is standardized, it will be clear to most programmers what it is.

Obviously, you can trim characters out to get your desired length, and it will increase your chance of finding a duplicate.

Edit:

Unfortunately, access to Scriptlet.TypeLib is currently blocked by default because of security reasons. See MS Access VBA Error: Run time error '70' Permission Denied for information on that, and How can I generate GUIDs in Excel? for a nice overview of multiple methods to generate a GUID.

My personal preference:

Public Function CreateGUID() As String
    Do While Len(CreateGUID) < 32
        If Len(CreateGUID) = 16 Then
            '17th character holds version information
            CreateGUID = CreateGUID & Hex$(8 + CInt(Rnd * 3))
        End If
        CreateGUID = CreateGUID & Hex$(CInt(Rnd * 15))
    Loop
    CreateGUID = "{" & Mid(CreateGUID, 1, 8) & "-" & Mid(CreateGUID, 9, 4) & "-" & Mid(CreateGUID, 13, 4) & "-" & Mid(CreateGUID, 17, 4) & "-" & Mid(CreateGUID, 21, 12) & "}"
End Function
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • No, but it is common to use this for identification of unique cases, and like Wikipedia states: "Thus, for there to be a one in a billion chance of duplication, 103 trillion version 4 UUIDs must be generated". I don't know how large your database is, but I think that should do. – Erik A Jul 06 '17 at 10:18
  • What if the length of the string would be 12 (I need it to be no longer than twelve). is there any way to tell it to run again in case a duplication error has been found? say , catch duplication errors or (do while) duplication_error <> 0 (I dont know vba syntax). – Yuval Jul 06 '17 at 10:25
  • Well, that limits the uniqueness, increasing the chance of collisions. If I have a chance, I will look into a more appropriate answer. Note that true uniqueness is not possible unless it's either time-based (limiting you to max 1 ID per second or external library calls to get microseconds) or you will need to keep a log of the (seed of the) previously generated answers. – Erik A Jul 06 '17 at 10:32
2

Use the date-time string:

  • Formula: =TEXT(NOW(),"yymmddhhmmss") -> 170705161201

  • VBA: Debug.Print Format(Now, "yymmddhhmmss")

paul bica
  • 10,557
  • 4
  • 23
  • 42
  • Paul , I like your solution , very creative , certainly haven't thought about it , but it doesn't have any A-Z characters in it. – Yuval Jul 05 '17 at 20:26
  • Why care? You can generate a unique number every second for one hundred years. – Gustav Jul 06 '17 at 07:54
  • Do you know of a way I can use it for auto generation of a field ? So that I wont need to click a button or activate an event but it will be automaticly activated when opening a new record? – Yuval Jul 06 '17 at 10:14
1

Well, I guess if it really needs to be alphanumeric and unique, this is the way to go:

This code generates a random string of characters based on the current time (like the answer by @paul-bica), only it encodes them alphanumerically and uses the timer to get fractions of a second to reach 12 characters). This way you should be able to generate multiple random strings per second.

Just call UniqueTimeBasedString() to get the string. (Note that if you use this function in a query, Access will cache the result and it won't be unique at all).

Public Function UniqueTimeBasedString() As String
    Dim alphanumericCharacters As String
    alphanumericCharacters = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Dim timeString As String
    timeString = Format(Now, "yymmddhhmmss") & Getmtimestring(6)
    Dim c As Integer
    Dim intTimepart As Integer
    c = 1
    Do While c < Len(timeString)
        intTimepart = CInt(Mid(timeString, c, 3))
        c = c + 3
        UniqueTimeBasedString = UniqueTimeBasedString & Mid(alphanumericCharacters, Int(intTimepart / 61) + 1, 1) & Mid(alphanumericCharacters, intTimepart Mod 61 + 1, 1)
    Loop
End Function

Public Function Getmtimestring(length As Integer) As String
    Dim mtime As Double
    mtime = Timer()
    Dim mtimeLng As Long
    mtimeLng = Int((mtime - Int(mtime)) * (10 ^ length))
    Getmtimestring = CStr(mtimeLng)
    Do While Len(Getmtimestring) < length
        Getmtimestring = "0" & Getmtimestring
    Loop
End Function

Notes:

  1. Timer() isn't really accurate, and this can theoretically cause problems
  2. This code is incompatible with OS X, but that won't be a problem if you stay limited to Access (Getmtimestring will just return zeroes)
  3. You really should have a talk with your boss. Asking you to do something you aren't really qualified for with a tight deadline no options to use a slightly other solution isn't healthy workplace behaviour.
  4. The string contains encoded time, and can be decoded. Don't report it if you want the time of creation of the string to be secret.
Erik A
  • 31,639
  • 12
  • 42
  • 67