jdelano

MS Access VBA Challenge

Oct 14th, 2025
1,451
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim ws As DAO.Workspace
  5. Dim rsWrite As DAO.Recordset
  6. Dim stringArray() As String
  7. Dim randomStringCount As Long
  8. Dim sectionTimes(1 To 4) As Date
  9.  
  10. Private Sub Form_Load()
  11.    
  12.     CreateStringArray
  13.     SetupSortedTable
  14.    
  15.     ClearForm
  16.  
  17. End Sub
  18.  
  19. Private Sub btnCreateStrings_Click()
  20.  
  21.     If rsWrite Is Nothing Then
  22.         SetupSortedTable ' if rerunning without closing
  23.    End If
  24.    
  25.     ClearForm
  26.     GenerateRandomStrings
  27. End Sub
  28.  
  29. Private Sub btnSortStrings_Click()
  30.    
  31.     ClearForm
  32.    
  33.     CreateStringArray
  34.    
  35.     If rsWrite Is Nothing Then SetupSortedTable ' if rerunning without closing
  36.    
  37.     Dim startRun As Date
  38.     Dim endRun As Date
  39.    
  40.     startRun = Now
  41.     txtStartTime.SetFocus: txtStartTime.Text = Format(CDate(startRun), "mm/dd/yy hh:MM:ss")
  42.        
  43.     sectionTimes(1) = Now
  44.     lblBusy.Caption = "Geting Strings": DoEvents
  45.     GetStrings
  46.    
  47.     sectionTimes(2) = Now
  48.     lblBusy.Caption = "Sorting Strings": DoEvents
  49.     Quicksort stringArray, LBound(stringArray), UBound(stringArray)
  50.    
  51.     sectionTimes(3) = Now
  52.     lblBusy.Caption = "Removing Duplicates": DoEvents
  53.     RemoveDuplicates
  54.    
  55.     sectionTimes(4) = Now
  56.     lblBusy.Caption = "Writing Sorted": DoEvents
  57.     WriteTheStrings
  58.    
  59.     endRun = Now
  60.     lblBusy.Caption = "Done"
  61.        
  62.     txtEndTime.SetFocus: txtEndTime.Text = Format(CDate(endRun), "mm/dd/yy hh:MM:ss")
  63.     txtDuration.SetFocus: txtDuration.Text = DateDiff("s", startRun, endRun)
  64.     txtCount.SetFocus: Me.txtCount.Text = UBound(stringArray) - 1
  65.    
  66.     Debug.Print "Get Strings start " & Format(sectionTimes(1), "h:mm:ss")
  67.     Debug.Print "Sort Strings start " & Format(sectionTimes(2), "h:mm:ss")
  68.     Debug.Print "Remove Duplicates start " & Format(sectionTimes(3), "h:mm:ss")
  69.     Debug.Print "Write Strings start " & Format(sectionTimes(4), "h:mm:ss")
  70.    
  71. End Sub
  72.  
  73. Private Sub CreateStringArray()
  74.  
  75.     ' create string array for sorting
  76.    On Error Resume Next
  77.         ' if there is already a tblRandom table get the count of records in it
  78.        Dim rsCount As DAO.Recordset
  79.         Set rsCount = CurrentDb.OpenRecordset("select count(TextString) from tblRandom", dbOpenDynaset)
  80.         randomStringCount = rsCount(0)
  81.         lblCreatedStringsCount.Caption = "There are " & randomStringCount & " random strings"
  82.         rsCount.Close
  83.         Set rsCount = Nothing
  84.        
  85.         ReDim stringArray((randomStringCount - 1))
  86.     On Error GoTo 0
  87.  
  88. End Sub
  89.  
  90. Private Sub ClearForm()
  91.  
  92.     txtStartTime.SetFocus: txtStartTime.Text = ""
  93.     txtEndTime.SetFocus: txtEndTime.Text = ""
  94.     txtDuration.SetFocus: txtDuration.Text = ""
  95.     txtCount.SetFocus: Me.txtCount.Text = ""
  96.  
  97. End Sub
  98.  
  99. Private Sub SetupSortedTable()
  100.  
  101.     ' set up the table
  102.    Set ws = DBEngine.Workspaces(0)
  103.  
  104.     On Error Resume Next
  105.     ' create the tblPrimes table
  106.    CurrentDb.Execute "drop table tblSorted"
  107.     CurrentDb.Execute "Create Table tblSorted (ID AUTOINCREMENT PRIMARY KEY, SortedString TEXT(150));"
  108.  
  109.     On Error GoTo 0
  110.     ' create the recordset
  111.    Set rsWrite = CurrentDb.OpenRecordset("tblSorted", dbOpenTable)   ' open the table to be written to
  112.    
  113.     DoEvents
  114. End Sub
  115.  
  116. Private Sub GetStrings()
  117.  
  118.     ' get the strings into an array
  119.    Dim rs As DAO.Recordset
  120.     Dim arrayIndex As Long
  121.    
  122.     Set rs = CurrentDb.OpenRecordset("Select * from tblRandom", dbOpenForwardOnly)
  123.  
  124.     arrayIndex = 0
  125.     Do While Not rs.EOF
  126.         stringArray(arrayIndex) = rs(1)
  127.         rs.MoveNext
  128.         arrayIndex = arrayIndex + 1
  129.     Loop
  130.    
  131.     rs.Close
  132.     Set rs = Nothing
  133.    
  134. End Sub
  135.  
  136. Private Sub RemoveDuplicates()
  137.  
  138.     Dim arrayIndex As Long
  139.     Dim removedCount As Long
  140.    
  141.     removedCount = 0
  142.     For arrayIndex = UBound(stringArray) - 1 To 1 Step -1
  143.         If stringArray(arrayIndex) = stringArray(arrayIndex - 1) Then
  144.             ' this is a duplicate of the one above it, make this blank
  145.            stringArray(arrayIndex) = ""
  146.             removedCount = removedCount + 1
  147.         End If
  148.    
  149.     Next arrayIndex
  150.    
  151.     If removedCount > 0 Then
  152.         ' duplicates were found and blanked out - remove the blanks
  153.        Dim tempArray() As String
  154.         Dim tempArrayIndex As Long
  155.        
  156.         ' use a temp array to hold the new array with no duplicates
  157.        ReDim tempArray((UBound(stringArray) - removedCount))
  158.        
  159.         For arrayIndex = 0 To UBound(stringArray) - 1
  160.             If Len(stringArray(arrayIndex)) > 0 Then
  161.                 tempArray(tempArrayIndex) = stringArray(arrayIndex)
  162.                 tempArrayIndex = tempArrayIndex + 1
  163.             End If
  164.        
  165.         Next arrayIndex
  166.        
  167.         ' assign it back to the main array variable while condencing the array
  168.        ReDim stringArray((UBound(stringArray) - removedCount))
  169.         stringArray = tempArray
  170.        
  171.     End If
  172.    
  173. End Sub
  174.  
  175. Sub Quicksort(vArray As Variant, arrLbound As Long, arrUbound As Long)
  176.  
  177.     ' Sorts a one-dimensional VBA array from smallest to largest
  178.    ' using a very fast quicksort algorithm variant.
  179.    ' from https://wellsr.com/vba/2018/excel/vba-quicksort-macro-to-sort-arrays-fast/
  180.    
  181.     Dim pivotVal As Variant
  182.     Dim vSwap As Variant
  183.     Dim tmpLow As Long
  184.     Dim tmpHi As Long
  185.    
  186.     tmpLow = arrLbound
  187.     tmpHi = arrUbound
  188.     pivotVal = vArray((arrLbound + arrUbound) \ 2)
  189.      
  190.     While (tmpLow <= tmpHi)
  191.        While (vArray(tmpLow) < pivotVal And tmpLow < arrUbound)
  192.           tmpLow = tmpLow + 1
  193.        Wend
  194.      
  195.        While (pivotVal < vArray(tmpHi) And tmpHi > arrLbound)
  196.           tmpHi = tmpHi - 1
  197.        Wend
  198.      
  199.        If (tmpLow <= tmpHi) Then
  200.           vSwap = vArray(tmpLow)
  201.           vArray(tmpLow) = vArray(tmpHi)
  202.           vArray(tmpHi) = vSwap
  203.           tmpLow = tmpLow + 1
  204.           tmpHi = tmpHi - 1
  205.        End If
  206.     Wend
  207.      
  208.     If (arrLbound < tmpHi) Then Quicksort vArray, arrLbound, tmpHi
  209.     If (tmpLow < arrUbound) Then Quicksort vArray, tmpLow, arrUbound
  210.      
  211. End Sub
  212.  
  213. Private Sub WriteTheStrings()
  214.  
  215.     Dim arrayIndex As Long
  216.    
  217.     ws.BeginTrans
  218.    
  219.     ' loop the sorted array and write it to a table
  220.    For arrayIndex = 0 To UBound(stringArray) - 1
  221.        
  222.         rsWrite.AddNew
  223.         rsWrite("SortedString") = stringArray(arrayIndex)
  224.         rsWrite.Update
  225.        
  226.     Next arrayIndex
  227.  
  228.     ws.CommitTrans
  229.    
  230.     rsWrite.Close
  231.     Set rsWrite = Nothing
  232.    
  233.     ws.Close
  234.     Set ws = Nothing
  235.  
  236. End Sub
  237.  
  238. Public Sub GenerateRandomStrings()
  239.  
  240.     ' provided string generation code
  241.    Dim db As DAO.Database
  242.     Dim tdf As DAO.TableDef
  243.     Dim rst As DAO.Recordset
  244.     Dim varListSize As Variant
  245.     Dim nListSize As Long
  246.     Dim nRecord As Long
  247.     Dim nChar As Integer
  248.     Dim sChar As String
  249.     Dim sText As String
  250.     Dim nRand As Integer
  251.     Set db = CurrentDb
  252.    
  253.     ' Delete tblRandom if it exists
  254.    On Error Resume Next
  255.     db.TableDefs.Delete "tblRandom"
  256.     On Error GoTo 0
  257.     DoEvents
  258.    
  259.     ' Re-create tblRandom and add the required fields
  260.    Set tdf = db.CreateTableDef("tblRandom")
  261.     DoEvents
  262.     With tdf
  263.         .Fields.Append .CreateField("ID", dbLong)
  264.         .Fields("ID").Attributes = dbAutoIncrField
  265.         .Fields.Append .CreateField("TextString", dbText, 255)
  266.     End With
  267.     db.TableDefs.Append tdf
  268.    
  269.     Set rst = db.OpenRecordset("tblRandom", dbOpenDynaset)
  270.    
  271.     ' get the desired list size
  272.    varListSize = InputBox("Number of Elements", "Enter Number", 1000000) ' default 1,000,000 but can be changed
  273.    If varListSize = "" Or varListSize <= 0 Or Not IsNumeric(varListSize) Then
  274.         MsgBox "Exiting", vbOKOnly + vbInformation, "Invalid Selection"
  275.         Exit Sub
  276.     End If
  277.    
  278.     nListSize = Fix(varListSize)
  279.     ' generate the random list
  280.    Call Rnd(-1)     ' required along with Randomize
  281.    Randomize 12345  ' set seed based on nRecord to ensure that the list of generated strings is always the same
  282.    
  283.     Me.lblCreatedStringsCount.Caption = "Creating " & nListSize & " strings": DoEvents
  284.    
  285.     ws.BeginTrans
  286.    
  287.     ' Outer loop
  288.    For nRecord = 1 To nListSize
  289.         sText = ""
  290.        
  291.         ' generate 8 random values between 0 and 26
  292.        For nChar = 1 To 8
  293.        
  294.             nRand = Int(27 * Rnd())  ' Random number between 0 and 26
  295.            
  296.             If nChar = 1 And nRand = 0 Then
  297.                 nRand = (nRecord Mod 26) + 1  ' don't allow the first character to be a space
  298.            End If
  299.            
  300.             ' Convert to character
  301.            If nRand = 0 Then
  302.                 sChar = " "  ' if nRand = 0 then a space is generated
  303.                Exit For     ' stop once a space is encountered
  304.            Else
  305.                 sChar = Chr(96 + nRand)  ' Chr(97) = "a", Chr(98) = "b", etc.
  306.            End If
  307.            
  308.             ' Build the string
  309.            sText = sText & sChar
  310.         Next nChar
  311.        
  312.         ' Write to the table
  313.        rst.AddNew
  314.         rst!TextString = sText
  315.         rst.Update
  316.        
  317.     Next nRecord
  318.    
  319.     ws.CommitTrans
  320.    
  321.     rst.Close
  322.     Set rst = Nothing
  323.     Set tdf = Nothing
  324.     Set db = Nothing
  325.    
  326.     Me.lblCreatedStringsCount.Caption = "Table of " & Format(nListSize, "#,##0") & " random strings created"
  327.    
  328.     randomStringCount = nListSize ' update the array size required
  329. End Sub
  330.  
  331.  
  332.  
Tags: Quicksort
Advertisement
Add Comment
Please, Sign In to add comment