Loop in an update SQL Server query sent by MS Access vba

Question

I am using MS Access vba as the interface and SQL Server as database server.

I want to update a set of rows in a table CANDIDAT_TESTS. What I am now doing is update one by one and I loop with a for loop; it works, but it takes too much time.

I would like to reduce the number of query by looping in one query.

My code:

This is the entire code

´´´ varSession = "2020" Set cnx = getConnectionBD(varSession)

rsSCE.Open "SELECT DISTINCT LIBELLE_DEPARTEMENT, LIBELLE_SOUS_CENTRE, NUM_SOUS_CENTRE " _
    & " FROM VIEW_CANDIDATS_SCE " _
    & " WHERE NUM_REGION = " & Me.LIST_REGION & " AND NUM_EXAMEN = " & Me.LIST_EXAMEN _
    & " ORDER BY LIBELLE_DEPARTEMENT, LIBELLE_SOUS_CENTRE", cnx, adOpenStatic, adLockOptimistic
varTabSCE = rsSCE.GetRows
'On initialise les jurys parce que les jurys sont par région
jury = 0

'On parcourt les sous-centres par départements
For j = 1 To rsSCE.RecordCount
    rsSerie.Open "SELECT DISTINCT NUM_SERIE, ABREVIATION_SERIE " _
        & " FROM VIEW_CANDIDATS_SCE " _
        & " WHERE NUM_SOUS_CENTRE = " & varTabSCE(2, j - 1) & " AND NUM_EXAMEN = " & Me.LIST_EXAMEN _
        & " ORDER BY ABREVIATION_SERIE", cnx, adOpenStatic, adLockOptimistic
    varTabSerie = rsSerie.GetRows

    'On parcourt les séries du sce j
    For k = 1 To rsSerie.RecordCount
        'on initialise le curseur à 0 étant donné que les numéros d'ordre sont par série
        cursor = 0
        rsCandidats.Open "SELECT ID FROM CANDIDAT_TESTS " _
            & " WHERE NUM_SOUS_CENTRE = " & varTabSCE(2, j - 1) & " AND NUM_SERIE = " & varTabSerie(0, k - 1) _
            & " ORDER BY NOMS, DATE_DE_NAISSANCE", cnx, adOpenStatic, adLockOptimistic
        varTabCandidats = rsCandidats.GetRows

        nbCanddidats = rsCandidats.RecordCount
        nbJurys = RoundUp(nbCanddidats / 250)

        'On parcourt les jurys de la série k du sce j
        numJury = nbJurys
        For l = 1 To nbJurys
            jury = jury + 1

            If numJury > 1 Then
                'On parcourt les candidats du jury l pour leur attribuer le numéro d'ordre et le jury
                For m = 250 * (l - 1) + 1 To 250 * (l - 1) + 250
                    cursor = cursor + 1
                    rsUpdate.Open "UPDATE CANDIDAT_TESTS " _
                        & " SET NUMERO_ORDRE = " & cursor & ", JURY = " & jury _
                        & " WHERE ID = " & varTabCandidats(0, m - 1), cnx, adOpenStatic, adLockOptimistic

                    Set rsUpdate = Nothing
                Next m
            ElseIf numJury = 1 Then
                'On parcourt les candidats du dernier jury pour leur attribuer le numéro d'ordre et le jury
                For m = 250 * (l - 1) + 1 To nbCanddidats
                    cursor = cursor + 1
                    rsUpdate.Open "UPDATE CANDIDAT_TESTS " _
                        & " SET NUMERO_ORDRE = " & cursor & ", JURY = " & jury _
                        & " WHERE ID = " & varTabCandidats(0, m - 1), cnx, adOpenStatic, adLockOptimistic

                    Set rsUpdate = Nothing
                Next m
            End If
            numJury = numJury - 1

        Next l

        Set rsCandidats = Nothing
        Set rsSerie = Nothing
    Next k

    Set rsSCE = Nothing
Next j ´´´

Is there any way I could transform the for m loop to a single query? Because I am looping this block of code again.

Thanks to y'all

Lee Mac · Accepted Answer · 2020-05-29 18:07:43Z

1

Rather than repeatedly executing a SQL statement for every iteration of the loop, it is likely to be more efficient to iterate over a single recordset and update the value of each record encountered, for example, using DAO:

With CurrentDb.OpenRecordset _
    ( _
        "select numero_ordre from candidat_tests " & _
        "where id >= " & varTabCandidats(0, 250 * (l - 1)) & " and " & _
        "id <= " & varTabCandidats(0, 250 * (l - 1) + 249) & " " &  _
        "order by id"
    )
    If Not .EOF Then
        .MoveFirst
        cursor = 1
        Do Until .EOF
            .Edit
            !numero_ordre = cursor
            .Update
            .MoveNext
            cursor = cursor + 1
        Loop
    End If
    .Close
End With

Without knowledge of the varTabCandidats function, the above is obviously untested.

edited May 29, 2020 at 18:07

answered May 29, 2020 at 17:55

Lee Mac

16.1k6 gold badges38 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Victoire Nkolo Over a year ago

varTabCandidats Is an array containing a list of selected IDs from the table CANDIDAT_TESTS

Victoire Nkolo Over a year ago

Could do it but I don’t know how to make the Where clause loop the set of data varTabCandidats.

Victoire Nkolo · Accepted Answer · 2020-06-02 15:11:32Z

0

This is what i did:

dim popjury as integer, jurynum as integer

popjury=0
jurynum=1
Do Until rsCandidats.EOF
    cursor = cursor  + 1 
    popjury=popjury+1
    if popjury > 250 then
          popjury=1
          jurynum=jurynum+1
    end if
    rsCandidats.Edit
    rsCandidats!NUMERO_ORDRE = cursor
    rsCandidats!JURY = jurynum
    rsCandidats.Update
    rsCandidats.MoveNext
Loop

It works ! Thanks to y'all.

answered Jun 2, 2020 at 15:11

Victoire Nkolo

12 bronze badges

Collectives™ on Stack Overflow

Loop in an update SQL Server query sent by MS Access vba

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related