Requêtes: Simplifier les requêtes d'action, dans le code	Author(s)

---Soumis par Dev Ashish---

Simplifier les requêtes d'action, dans le code.

    Avez-vous déjà implémenté uin processus en mode batch d'importation, de mise à jour suivi d'exportation sous Access, utilisant Execute ou RunSQL? Si oui, vous savez probablement la difficulté associé à la maintenance de ces codes SQL. Pour être honnête, je me suis tiré les cheveux à maintes occasions avec ce problème.

    C'est-à-dire jusqu'à ce que je réalise que je peux utiliser une table Access et un logique toute simple qui non seulement réduit le nombre de lignes de code SQL inclus dans le code VBA, mais, de plus, rend les requêtes auto documentées. On peut continuer à définir les requêtes avec Access QBE et ne pas à avoir à inclure tout ces énoncés SQL dans le code.

Je me défini une table, tblSQL, comme

Le champ de description représente le contenu actuel de chaque champ. L'idée derrière le processus est de sauvegarder les énoncés SQL en séquence, de par le numéro de séquence  SQLSequence à l'intérieur de chaque processus CalledFromProc (réutilisable, si requis, à divers points de votre programme). Un exemple de valeurs dans cette table peut être comme suit:

Créer un formulaire lié à cette table et en y contenant tous les champs. Vous pouvez ne pas inclure  SQLID si vous le désirez. Le formulaire vous permet de sauvegarder, modifier ou de voir les énoncés SQL.

Les étapes requises pour exécuter ces requêtes en séquence, les unes après les autres, sont toutes simples.

En principe, vous devez:

• Identifier les énoncés SQL requis pour exécuter la procédure "batch" requise.
• Ordonner les énoncé SQL selon leur séquence, pour la procédure identifiée.
• Remplacer tous les guillemets par  Chr(34)  dans l'énoncé SQL.
• Exécuter la requête d'action.

Avec le traitement d'erreur, et une fonction pour remplacer les guillemets, nous avons tout ce qu'il nous faut pour procéder. Un exemple simple d'utilisation suit:

'*************** Code Start ***************
Sub sSomeSubRoutine()
Dim lodb As Database, strSQL As String
Dim loSQLRS As Recordset, lngCurrent As Long
Dim varTmp As Variant
Const cERR_GRACEFUL_EXIT = vbObjectError + 20

    On Local Error GoTo Err_handler
    varTmp = SysCmd(acSysCmdSetStatus, "Starting Batch process...")
    Set lodb = CurrentDb
    Set loSQLRS = lodb.OpenRecordset("Select * from tblSQL where" _
                    & " CalledFromProc='sStartOutSiderProcess'", dbOpenSnapshot)
    lngCurrent = 1
    With loSQLRS
        .FindFirst "SQLSequence=" & lngCurrent
        If .NoMatch Then Err.Raise cERR_GRACEFUL_EXIT
        Do While Not .NoMatch
            strSQL = adhHandleQuotes(!SQLString)
            lodb.Execute strSQL, dbFailOnError
            lngCurrent = lngCurrent + 1
            .FindFirst "SQLSequence=" & lngCurrent
        Loop
    End With
    
Exit_Here:
    varTmp = SysCmd(acSysCmdClearStatus)
    Set loSQLRS = Nothing
    Set lodb = Nothing
    Exit Sub
Err_handler:
    Dim strXX As String
    Select Case Err.Number
        Case 3065:      'SQL is a Select statement
            strXX = "You can only run Action queries by the Execute method."
            strXX = strXX & vbCrLf & "Please change the SQL to an action statement."
            strXX = strXX & vbCrLf & vbCrLf & loSQLRS!SQLString
            MsgBox strXX, vbCritical + vbOKOnly, "Error in SQL Statement"
        Case 3075:      'Syntax error in SQL statement
            strXX = "The following SQL statement has syntax error."
            strXX = strXX & vbCrLf & "Please verify the SQL string and try again."
            strXX = strXX & vbCrLf & vbCrLf & loSQLRS!SQLString
            MsgBox strXX, vbCritical + vbOKOnly, "Error in SQL Statement"
        Case cERR_GRACEFUL_EXIT:
        Case Else:
            strXX = "Proc: sSomeSubRoutine"
            strXX = strXX & vbCrLf & "Error #: " & Err.Number
            strXX = strXX & vbCrLf & "Description: " & Err.Description
            If Not (loSQLRS Is Nothing) And Not (lodb Is Nothing) Then
                strXX = strXX & vbCrLf & "The last action query " & vbCrLf & vbCrLf & _
                        loSQLRS!SQLString & vbCrLf & vbCrLf & "affected " & _
                        lodb.RecordsAffected & " records"
            End If
            MsgBox strXX, vbExclamation + vbOKOnly, "Runtime Error"
    End Select
    Resume Exit_Here
End Sub


Public Function adhHandleQuotes(strValue As String) As String
    ' Remplacer tous les " dans la chaîne, par Chr$(32)
    ' de sorte que Jet puisse alors traiter proprement
    ' la chaîne pour fins de recherche.
    '
    ' From Access 97 Developer's Handbook
    ' by Litwin, Getz, and Gilbert (Sybex)
    ' Copyright 1997.  All rights reserved.
    '
    ' Solution suggérée par Jurgen Welz, un lecteur attentif

    ' In:
    '   strValue:   Value to fix up.
    ' Out:
    '   Résultat: le texte, avec les guillements proprement formattés
    ' Requiert:
    '   adhReplace (ou autre fonction qui remplace une chaîne
    '       par une autre)
    '
    ' Exemple:
    '    adhHandleQuotes("John "Big-Boy" O'Neil") retourne
    '     "John " & Chr$(34) & "Big-Boy" & Chr$(34) & " O'Neil"

   Const QUOTE As String = """"

   adhHandleQuotes = QUOTE & adhReplace(strValue, QUOTE, _
    QUOTE & " & Chr$(34) & " & QUOTE) & QUOTE
 End Function

 Function adhReplace(ByVal varValue As Variant, _
  ByVal strFind As String, ByVal strReplace As String) As Variant

     ' Remplace toutes les instances de strFind par strReplace dans varValue.

     ' From Access 97 Developer's Handbook
     ' by Litwin, Getz, and Gilbert (Sybex)
     ' Copyright 1997.  All rights reserved.

     ' In:
     '    varValue: chaîne initiale à modifier
     '    strFind: sous-chaîne à substituer
     '    strReplace: sous-chaîne de remplacement
     '
     ' Out:
     '    Résultat: varValue, où toutes les apparitions de strFind
     '     furent remplacées par strReplace.

     Dim intLenFind As Integer
     Dim intLenReplace As Integer
     Dim intPos As Integer

     If IsNull(varValue) Then
         adhReplace = Null
     Else
         intLenFind = Len(strFind)
         intLenReplace = Len(strReplace)

         intPos = 1
         Do
             intPos = InStr(intPos, varValue, strFind)
             If intPos > 0 Then
                 varValue = Left(varValue, intPos - 1) & _
                  strReplace & Mid(varValue, intPos + intLenFind)
                 intPos = intPos + intLenReplace
             End If
         Loop Until intPos = 0
     End If
     adhReplace = varValue
 End Function
'****************** Code End *****************

Les avantages de cette approche:

• L'énoncé SQL est fin prêt à être copié, sans avoir à ajouter ou à enlever des caractères de continuation.
• Pour changer l'ordre d'exécution? Tout simple, seulement à changer SQLSequence, évitant de couper-coller!
• Le champ  SQLDescription rend votre code plus aisé à comprendre. Vous pouvez même définir un état, avec les champs selon l'ordre des séquence, et qui voilà tout simplement la documentation appropriée !
• Est-il besoin de mentionner le nombre de lignes de code que vous venez d'éliminer,. remplacé par un appel à une simple procédure?
• Vous désirez tester un différent ordonnancement des énoncés? Simplement renuméroter les séquences. Ainsi, pour "tester" ce qui se passe si 11 et 12 ne sont pas exécutés, temporairement les renuméroter, comme 20 et 21, puis arrêter l'exécution après 19, par exemple.