asked on

MS Access VBA How To Use String Generator To Produce 5000+ Strings For a Access Table Import

I have a file with over 5000 records I need added to a table of mine in access. Well one of my fields is call SKU. The SKU field is required and is Unique. I use a Function called UniqueString for when I insert new records one at a time. But I need to use this function to generate 5000+ Strings at a single time so I can copy and paste the row into my excel file before importing into Access?

How can this be accomplished? Thanks for the help and here is UniqueString:

Option Compare Database
Option Explicit

Function UniqueString(ByVal parmLen) As String
    Const cAlphabet = "ABCDEFGHJKMNPQRSTUVWXYZ0123456789"
    Const AlphabetLen = 33
    Dim lngLoop As Long
    Dim lngOffset As Long
    Dim lngPosn As Long
    Dim GUID As String
    Dim GUID_Trailer As String
    
    Do
        GUID = GUID & Replace(Mid(CreateObject("scriptlet.typelib").GUID, 2, 36), "-", vbNullString)
    Loop Until Len(GUID) >= parmLen
    
    UniqueString = String(parmLen, "*")     'initialize output string
    Select Case Len(GUID)
        Case parmLen
            'convert entire GUID to string
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = "&h" & Mid(GUID, lngLoop, 2)
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
            Next
            
        Case Is > parmLen
            'use remaining byte values as increment, mod 256
            GUID_Trailer = Mid(GUID, (parmLen * 2) + 1)
            Do
                GUID_Trailer = GUID_Trailer & GUID_Trailer
            Loop Until Len(GUID_Trailer) >= ((parmLen * 2) + 6)
            
            GUID = Left(GUID, (parmLen * 2))
            
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = CLng("&h" & Mid(GUID, lngLoop, 2)) + CLng("&h" & Mid(GUID_Trailer, lngPosn, 6))
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
            Next
        
    End Select
End Function

Open in new window

Bill Prew

Here is an approach. Open the attached Excel Workbook, adjust the constants in the GenStrings() routine and then run it. It will place the values generated down the A column. Then copy from there as needed...

Sub GenStrings()
    Const GenCount = 50
    Const GenLength = 10
    
    Dim i As Long
    
    Columns("A:A").ClearContents
    
    For i = 1 To GenCount
        Worksheets(1).Cells(i, 1).Value = UniqueString(GenLength)
    Next
    
End Sub

Open in new window

EE29039458.xlsm

»bp

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

Pat is right.
That said, your function fails for me; the last half of the requested string is stars only:

? UniqueString(32)
80Z2X38JBHHBWUCQ****************

and for some length, for example 16 and 48, it goes into an endless loop, so you will have to kill Access.

Consider a simpler method:

Public Function StrRandom(ByVal lngLen As Long) As String

' Create fixed length string of random characters.
' Will generate about 512K per second.
'
' 2002-02-02. Cactus Data ApS, CPH

    Dim StrRnd        As String
    Dim lngN          As Long
    Dim strChar       As String
      
    Randomize
    lngLen = Abs(lngLen)
    ' Create string of zeroes, lngLen long.
    StrRnd = String(lngLen, "0")
    ' Perform hi-speed filling of string with random character string.
    While lngN < lngLen
        strChar = Chr(65 + Rnd * (90 - 65))
        Select Case strChar
            Case "I", "L", "O"
                ' Ignore.
            Case Else
                Mid(StrRnd, lngN + 1) = strChar
                ' Calculate entry position for next substring.
                lngN = lngN + 1
        End Select
    Wend
  
    StrRandom = StrRnd
  
End Function

Open in new window

/gustav

Dustin Stanley

ASKER

Thanks Bill and Pat for the idea! I will see what I can do. I was extremely swamped today at work. Hopefully tomorrow I can figure this out.

Gustav

That said, your function fails for me; the last half of the requested string is stars only:

Good eye. I have never used this for anything over 11 length. I ran a test like you said and sure enough it crashed and *************...

Thanks for the input I never would have seen that until later if I used it for a longer length.

Dustin Stanley

ASKER

Also Pat

Add the SKU column and use the function in the From cell.

Do you mean to Literally in Excel in the SKU cell for each item type UniqueString(11)????

Then when I import it it will automatically know to run the function? Thanks.

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dustin Stanley

ASKER

Thank you. This will get me in the correct area. Thanks!