Improve query/vba to group by linked list

See more:

MS-Access

VBA

Hi,

I receive some tables where there are elements grouped by linked lists and I have trouble to deal with it.

The function works find as it but I'm often asked where is its macro when launched since task scheduler or have some memory issues.

I use the following code to find out the idGroup (translated to English) and I'm wondering if there would by ways to improvise it especially its speed because it takes up to an hour for 30 000 rows and about 2500 groups... (It's why I had used VBA to see progress...)

'Simple example
'idGroup,id2,id1
'6338546,14322882,13608969
'6338546,13608969,13255363
'6338546,6338546,14322882
'6338546,11837926,11316332
'6338546,12297571,11837926
'6338546,13255363,12811071
'6338546,12811071,12297571
'6338546,7610194,7343817
'6338546,7935943,7610194
'6338546,8531387,7935943
'6338546,6944491,6611041
'6338546,7343817,6944491
'6338546,9968746,9632204
'6338546,10381694,9968746
'6338546,6611041,0
'6338546,8920224,8531387
'6338546,9632204,8920224
'6338546,11316332,10941093
'6338546,10941093,10381694


Public Function GetidGroup()
    'first id1 is always 0
    sql = "SELECT idGroup, id2, id1 FROM TABLE_WITH_LINKED_LIST WHERE id1='0' ORDER BY id2 DESC"
    Dim rs As Recordset
    Dim uidLikedList As String, id2 As String, id1 As String
   
    Set rs = CurrentDb.OpenRecordset(sql)
    Dim total As Long
    Dim idGroup As String
    Dim incrément As Long, progress As Double
       
    total = rs.RecordCount
    incrément = 1
   
    While Not rs.EOF
        progress = Math.Round(100 * incrément / total, 2)
       
        'Print in order to avoir freezing
        Debug.Print progress
       
        If rs.Fields("idGroup") = "" Then
            id2 = rs.Fields("id2")
                   
            idGroup = precedentUid(id2)
           
            rs.Edit
            rs.Fields("idGroup") = idGroup
            rs.Update
        End If
       
        incrément = incrément + 1
        rs.MoveNext
    Wend
   
    rs.Close
    Set rs = Nothing
    GetidGroup = total
End Function

'Recursive function
'Deepest so far is about 62 calls
Public Function precedentUid(id2 As String) As String
    sql = "SELECT idGroup, id2 FROM TABLE_WITH_LINKED_LIST WHERE id1 = '" & id2 & "'"
    Dim rs As Recordset
    Dim precedentid2 As String
    Dim idGroup As String
    Dim ret As String
   
    Set rs = CurrentDb.OpenRecordset(sql)
    If rs.EOF Then
        rs.Close
        Set rs = Nothing
        precedentUid = id2
    Else
        'Some records have several references
        '56 impacted records  :
        'TODO : Give the min id2 to the group
        ret = "-1"
        While Not rs.EOF           
            If rs.Fields("idGroup") = "" Then
                precedentid2 = rs.Fields("id2")
                idGroup = precedentUid(precedentid2)
               
                If ret = "-1" Or CLng(ret) > CLng(idGroup) Then
                    ret = idGroup
                End If
               
                'Debug.Print id2 & " " & precedentid2 & " " & idGroup
               
                rs.Edit
                    rs.Fields("idGroup") = idGroup
                rs.Update
            End If
            rs.MoveNext
        Wend
        rs.Close
        Set rs = Nothing
        precedentUid = ret
    End If
End Function

Thanks in advance for any hint.

Guillaume.