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)

 

[sheetspread]

VBA: you can pass the string to an array with split, sort it, then build a SQL string with another loop and run

http://www.mrexcel.com/forum/excel-...al-basic-applications-sort-array-numbers.html (bubble sort algorithm uses general vba despite being written for Excel as far as I can see)

VBA Code to loop an SQL statement

 

[Luthius]

Thanks for the links
Well, the problem now is how to use the recordset to insert the values in my table.

Is it possíble give a special hint on how to do it?

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

 

[sheetspread]

My first idea was to try DoCmd.RunSQL (as implied above) but maybe .addnew should be considered as well (?)

vba - How to insert ADO Recordset into MS Access Table - Stack Overflow

 

[Joe4]

The request seems little odd, a little counter intuitive to good database design. You usually don't store "calculations" on a table level. It removes the dynamic nature of a database, and can undermine data integrity. For example, what if you stored the "rankings", then changed a value or added values later. Then your rankings may no longer be correct.

Check out this thread form Allen Browne on how to do these type of rankings dynamically: Microsoft Access tips:

If you absolutely have to/want to store them on a table level, one way would be to import them into a temporary tables, sort them in a query, and then use VBA code to loop through the recordset, writing out each record in order.

 

[sheetspread]

I have a routine that generates a sequence of numbers

You usually don't store "calculations" on a table level

A non-VBA method suggested on another site is to sort in Excel, link to Access, and append (or update) in design view

 

[Luthius]

I'm developing application with numbers as data because I need to store the result of partitions..

Partition Function P -- from Wolfram MathWorld

I am almost there. I would like your assistance on help me to finish it

Sub insertRecorsInMyTable()Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSequence As String
Dim varString As Variant
Dim varTemp As Variant
Dim maxLenght As Integer


strSequence = "25,24,18,16,12,11,09,08,07,06,05,04,03,02,01"
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)


For j = 1 To maxLenght
'rs.AddNew
'rs![number1] = varTemp(j)
Next
'rs.Update
rs.Close
db.Close
End Sub

 

[sheetspread]

This entered your array into the lone Numbers field of tblResults:

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 rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblResults", dbOpenDynaset)
For i = LBound(tempArray) To UBound(tempArray)
rs.AddNew
rs!Numbers = tempArray(i)
rs.Update
Next i
End Sub

 

[Luthius]

Thanks for your feedback, but the characteristic of my table is different. I mean, as explained before I have 15 fields to receive the 15 numbers of the sequence. Unfortunately I cannot change it.

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

 

[sheetspread]

I'm developing application with numbers as data because I need to store the result of partitions..

Click here:

a built-in function that I'd be willing to bet 99.9% of VBA programmers never knew existed... the Partition function

This may not apply to your purpose, but I thought of it as soon as I read your #7 post