Insert records into table separated by "," caracter

[Luthius]

I have a routine that generates a sequence of numbers like this

"25,24,18,16,12,11,09,08,07,06,05,04,03,02,01"


I would like to insert these numbers separated in my table ordered from lower numbers to higher numbers.


Table Name:tblResults
Fields Name:
number1 (LOWEST NUMBER OF THE SEQUENCE)
number2
...
number15 (HIGHEST NUMBER OF THE SEQUENCE)

 

[Luthius]

Very good I didnt know about this function, but I still need to collect each value of my sequence and save on my 15 fields of my table.
Is there some idea?

 

[sheetspread]

I have 15 fields to receive the 15 numbers of the sequence. Unfortunately I cannot change it.

Ok, I had actually modified John Vinson's code here: how do I write the contents of an array to an access table | PC Review for just 1, his were hard coded for multi fields, but the one below it loops dynamically (I think). Try it and see, I'll test it too.

 

[Luthius]

Well, I am almost there.
The loop below needs to be fixed. The Idea i had was to use the array Vartemp that contains the ordered numbers after the bubbleSort and add through the loop of fields.
But it is not working....

For i = 0 To UBound(varTemp)    
        For j = 1 To rs.Fields.Count - 1
           rs.Fields(j).Value = varTemp(i)
    Next j
Next i

 

[sheetspread]

This?

Option Compare Database


Sub BubbleSort()
Dim tempArray As Variant
Dim Temp As Variant
Dim i As Integer
Dim NoExchanges As Integer
tempArray = Array(25, 24, 18, 16, 12, 11, 9, 8, 7, 6, 5, 4, 3, 2, 1)


    ' Loop until no more "exchanges" are made.
    Do
        NoExchanges = True
        
        ' Loop through each element in the array.
        For i = 0 To UBound(tempArray) - 1
        
            ' Substitution when element is greater than the element following int
            If tempArray(i) > tempArray(i + 1) Then
                NoExchanges = False
                Temp = tempArray(i)
                tempArray(i) = tempArray(i + 1)
                tempArray(i + 1) = Temp
            End If
        
        Next i
    
    Loop While Not (NoExchanges)
    
Dim j As Long
Dim f As Long
Dim rsR As DAO.Recordset
Set rsR = CurrentDb.OpenRecordset("tblResults")

With rsR

.AddNew
For f = 0 To UBound(tempArray) 'loop through fields
rsR.Fields(f).Value = tempArray(f)
Next f
.Update


.Close
End With
End Sub

 

[Luthius]

Thank you very much for your support. With your ideas and hints I got here.

Public Function insertRecordsInMyTable(strSequence As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varString As Variant
Dim vartemp As Variant
Dim maxLenght As Integer


varString = Split(strSequence, ",")
maxLenght = UBound(varString)
ReDim vartemp(0 To maxLenght)


For i = 0 To maxLenght
    vartemp(i) = CLng(varString(i))
Next i


vartemp = BubbleSort(vartemp)
maxLenght = UBound(vartemp)
 
Set db = CurrentDb
Set rs = db.OpenRecordset("tblResults", dbOpenTable, dbAppendOnly)
i = 0


Do Until i > UBound(vartemp)
rs.AddNew


    For j = 2 To rs.Fields.Count - 1
        rs.Fields(j).Value = vartemp(i)
        i = i + 1
    Next j
    Loop
rs.Update
rs.Close
db.Close
End Function


'Adapted from http://support.microsoft.com/kb/133135
'Note: a zero-based array is assumed (jazz this up by testing for zero or one base arrays and proceeding accordingly)
Public Function BubbleSort(ByVal tempArray As Variant) As Variant
Dim Temp As Variant
Dim i As Integer
Dim NoExchanges As Integer


    ' Loop until no more "exchanges" are made.
    Do
        NoExchanges = True
        
        ' Loop through each element in the array.
        For i = 0 To UBound(tempArray) - 1
        
            ' Substitution when element is greater than the element following int
            If tempArray(i) > tempArray(i + 1) Then
                NoExchanges = False
                Temp = tempArray(i)
                tempArray(i) = tempArray(i + 1)
                tempArray(i + 1) = Temp
            End If
        
        Next i
    
    Loop While Not (NoExchanges)
    
    BubbleSort = tempArray


End Function

I realized that the bubbleSort function loops too much. If we could get to a faster way to do it would be good.
Thanks again for all support.

 

[sheetspread]

QuickSort might be better (from msdn):

Sub QuickSort(arr, Lo As Long, Hi As Long)
  Dim varPivot As Variant
  Dim varTmp As Variant
  Dim tmpLow As Long
  Dim tmpHi As Long
  tmpLow = Lo
  tmpHi = Hi
  varPivot = arr((Lo + Hi) \ 2)
  Do While tmpLow <= tmpHi
    Do While arr(tmpLow) < varPivot And tmpLow < Hi
      tmpLow = tmpLow + 1
    Loop
    Do While varPivot < arr(tmpHi) And tmpHi > Lo
      tmpHi = tmpHi - 1
    Loop
    If tmpLow <= tmpHi Then
      varTmp = arr(tmpLow)
      arr(tmpLow) = arr(tmpHi)
      arr(tmpHi) = varTmp
      tmpLow = tmpLow + 1
      tmpHi = tmpHi - 1
    End If
  Loop
  If Lo < tmpHi Then QuickSort arr, Lo, tmpHi
  If tmpLow < Hi Then QuickSort arr, tmpLow, Hi
End Sub

 

[james_lankford]

let access do the sorting for you

let's say your final table is called tbl_2 and has a single field called num
now let's make a table called tbl_1 that also has a single field called num -- this table will be our intermediate table used to store and sort the numbers

Option Compare Database
Option Explicit
'**************************************************
Sub doit()


    '*****************************
    ' tbl_1 is jsut a temp table used to sort our numbers
    ' tbl_2 is the fianl table, the real table that holds our data
    '
    ' so we insert into tbl_1
    ' then we select those values and insert them into tbl_2
    '*****************************
    
    '*****************************
    '***** testing only **********
    'CurrentDb().Execute "delete * from tbl_1"
    'CurrentDb().Execute "delete * from tbl_2"
    '*****************************
        
    Dim sql As String
    
    Dim s As String
    s = "25,24,18,16,12,11,09,08,07,06,05,04,03,02,01"
    
    Dim arr As Variant
    arr = Split(s, ",")
    
    Dim index As Integer
    Dim lst As Integer
    
    lst = UBound(arr)
    For index = 0 To lst
        sql = ""
        sql = "insert into tbl_1 (num) values ( " & arr(index) & " ) ; " & vbCrLf
        CurrentDb().Execute sql
    Next
    
    sql = "insert into tbl_2 (num) select num from tbl_1 order by tbl_1.num "
    
    CurrentDb().Execute sql
    
    ' clean out tbl_1 so we don't insert the old values into tbl_2 next time
    CurrentDb().Execute "delete * from tbl_1"
    
End Sub
'**************************************************