---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 honê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.
I defined a table, tblSQL, as
Field Name | Data Type | Description |
SQLID | Autonumber | Clé primaire pour la table. |
SQLSequence | Long Integer | Détermine la séquence d'exécution des énoncés SQL. |
CalledFromProc | Text | Nom de la procédure utilisant cette requête. |
SQLString | Memo | Énoncé SQL à utiliser. |
SQLDescription | Memo | Description de ce que fait cet énoncé SQL. |
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:
SQL ID | SQL Seq- uence |
Called From Proc |
SQL String |
SQL Description |
3 | 1 | Batch Process1 |
UPDATE DISTINCTROW Table1 INNER JOIN Table2 ON Table1.SomeID =
Table2.SomeID SET Table2.SomeField = [Table1].[AnotherField] WHERE (((Table2.ThirdField)="Microsoft Access")); |
Gets the value for SomeID from Table2 for ThirdField="Microsoft Access" |
4 | 2 | Batch Process1 |
UPDATE DISTINCTROW Table2 SET Table2.LastName = fRemoveChar([LastName],"_"), Table2.FirstName = fRemoveChar([FirstName],"_"); | Removes Underscores from First and Last names for all records and replaces it with an underscore. |
5 | 3 | Batch Process1 |
UPDATE DISTINCTROW Table2 INNER JOIN MasterTable ON Table2.SomeID = MasterTable.ThisID SET Table2.ThisID = MasterTable.TheID WHERE (((Table2.SomeID) Is Null)); | Get the ThisID for everybody from MasterTable table |
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 voirs 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:
Avec le traitement d'erreur, et une fonction pour remplacer les guillements, 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: