Simplifier les requêtes sql sous ado / adodb
5/5 (3 avis)
Snippet vu 15 863 fois - Téléchargée 45 fois
Contenu du snippet
Il s'agit d'un ensemble de 7 petites fonctions qui vous permettrons de travailler très simplement avec ADODB (c'est à dire ADO).
Ces fonctions permettent aussi de monter vos instructions SQL par fusion plutôt que par concaténation.
Moi je n'utilise plus que ça...
Avant toute chose, vous devez rendre la connexion g_Cnx ouverte.
(sous Access : Set g_Cnx = CurrentProject.Connection)
Exemples :
Ouvrir un recordset en mode lecture seule :
Set MonRS = m_Sql_OuvreRsL("SELECT * FROM MaTable WHERE (Id=%1%) AND (Ville='%2%')",VarId,VarVille)
Ouvrir un recordset en mode modif : (c'est le nom de la fonction qui change)
Set MonRS = m_Sql_OuvreRsM("SELECT * FROM MaTable WHERE (Id=%1%) AND (Ville='%2%')",VarId,VarVille)
Récupérer une valeur simple :
MaVar = m_Sql_Éval("SELECT SUM(Montant) FROM MatTable WHERE (Pays='%1%')";strPays)
Éxecuter une instruction SQL : (retourne le nombre d'enreg affectés)
Nbr = m_Sql_Exécute("UPDATE MaTable SET MonChamp='%2%' WHERE (Id=%1%)",VarId,VarTxt)
Ajouter un enreg dans une table :
m_Sql_AjouteEnreg("MaTable","Date,Nom,Montant",Now(),"Toto",150000)
Ces fonctions permettent aussi de monter vos instructions SQL par fusion plutôt que par concaténation.
Moi je n'utilise plus que ça...
Avant toute chose, vous devez rendre la connexion g_Cnx ouverte.
(sous Access : Set g_Cnx = CurrentProject.Connection)
Exemples :
Ouvrir un recordset en mode lecture seule :
Set MonRS = m_Sql_OuvreRsL("SELECT * FROM MaTable WHERE (Id=%1%) AND (Ville='%2%')",VarId,VarVille)
Ouvrir un recordset en mode modif : (c'est le nom de la fonction qui change)
Set MonRS = m_Sql_OuvreRsM("SELECT * FROM MaTable WHERE (Id=%1%) AND (Ville='%2%')",VarId,VarVille)
Récupérer une valeur simple :
MaVar = m_Sql_Éval("SELECT SUM(Montant) FROM MatTable WHERE (Pays='%1%')";strPays)
Éxecuter une instruction SQL : (retourne le nombre d'enreg affectés)
Nbr = m_Sql_Exécute("UPDATE MaTable SET MonChamp='%2%' WHERE (Id=%1%)",VarId,VarTxt)
Ajouter un enreg dans une table :
m_Sql_AjouteEnreg("MaTable","Date,Nom,Montant",Now(),"Toto",150000)
Source / Exemple :
Option Explicit
'-------------------------------
'Auteur : Skrol 29
'Email : skrol29@freesurf.fr
'Web : www.skrol29.com
'Version : 1.01
'-------------------------------
'Voici un ensemble de fonctions simples et pratiques pour travailler
' sur base de données avec ADODB sous Visual Basic ou Access.
'Description des fonctions :
'm_Sql_OuvreConnexion() : ouvre un nouvelle connexion et retourne cette connexion
'm_Sql_Éval() : retourne la valeur du premier champ de la première ligne de l'instruction SQL
'm_Sql_Exécute() : exécute l'instruction SQL et retourne le nombre de lignes affectées
'm_Sql_AjouteEnreg() : ajoute un enregistrement à une table
'm_Sql_OuvreRsL() : retourne un nouveau RecordSet en mode lecture seule sur une instruction SQL
'm_Sql_OuvreRsM : retourne un nouveau RecordSet en mode lecture/écriture sur une instruction SQL
'm_Sql_Montage() : fusionne une expression SQL avec des valeurs
'La variable publique 'g_Cnx' :
'La variable publique g_Cnx représente la connexion à la base de données.
'Toutes les fonctions s'appuient sur cette connexion et supposent qu'elle est ouverte.
'Il faudra donc, à un moment donné dans votre code, ouvrir la connexion, par exemple à l'aide de la fonction m_Sql_OuvreConnexion().
'Sous Access 2000 et 2002 :
'L'application Access fourni déjà une variable publique qui représente la connexion
' ouverte sur la base de données en cours. La variable 'g_Cnx' devient donc
' inutile et doit être remplacée dans ce code par l'expression 'CurrentProject.Connection'.
'Utilisation de la fusion dans les fonctions :
'La plupart des fonctions ci-dessous permettent un codage rapide et rigoureux
' grâce à leur possibilité de fusionner l'instruction SQL, évitant ainsi aux développeurs
' de monter l'instruction SQL par concaténation de chaîne.
'Comment ça marche ? C'est tout simple.
'Au lieu de coder :
' m_Sql_Exécute "DELETE Matable WHERE (Nature=" & xNature & ") AND (Ville='" & xVille "')"
'Vous pouvez coder :
' m_Sql_Exécute "DELETE Matable WHERE (Nature=%1%) AND (Ville='%2%')",xNature,xVille
'
'C'est carrément plus claire non ?
'Attention à ne pas oublier les délimiteurs dans vos instructions SQL.
'Il vous faudra aussi malheureusement reformater vos dates avant de les passer en paramètre
' mais ça c'est toujours un problème avec le langage SQL.
Public g_Cnx As New ADODB.Connection
Public Function m_Sql_Exécute(SqlStr As String, ParamArray PrmLst() As Variant) As Long
'*** Cette fonction fusionne l'instruction SqlStr et les valeurs PrmLst() comme décrite plus haut.
'Execute le SQL et retourne le nombre d'enregistrement affecté.
Dim i As Long
Dim x As String
Dim Nbr As Long
'Fusion entre la chaîne et les paramètres
x = SqlStr
For i = 0 To UBound(PrmLst)
x = Replace(x, "%" & (i + 1) & "%", "" & PrmLst(i))
Next i
g_Cnx.Execute x, Nbr
m_Sql_Exécute = Nbr
End Function
Public Function m_Sql_Éval(SqlStr As String, ParamArray PrmLst() As Variant) As Variant
'*** Cette fonction fusionne l'instruction SqlStr et les valeurs PrmLst() comme décrite plus haut.
'Retourne la valeur du premier champ retourné par l'instrcution SQL renseignée.
'Il peut s'agir une phrase SELECT ou d'une procédure stockée.
'exemple : m_Sql_Éval("SELECT COUNT(*) FROM Matable WHERE (Age>%1%) AND (Ville='%2%')",30,"TOULOUSE")
Dim i As Long
Dim x As String
Dim Rs As New ADODB.Recordset
Dim Cmd As New ADODB.Command
'Fusion entre la chaîne et les paramètres
x = SqlStr
For i = 0 To UBound(PrmLst)
x = Replace(x, "%" & (i + 1) & "%", "" & PrmLst(i))
Next i
Rs.Open x, g_Cnx, adOpenStatic, adLockReadOnly
p_TrouveDernierRs Rs
If Rs.EOF Then
m_Sql_Éval = Null
Else
m_Sql_Éval = Rs.Fields(0).Value
End If
Rs.Close
End Function
Public Sub m_Sql_AjouteEnreg(Table As String, ParamArray FieldsAndValues() As Variant)
'Ajoute un nouvel enreg dans la table avec les données spécifée.
'Deux syntaxes possibles :
'Syntaxe 1 : m_Sql_AjouteEnreg "MaTable","champs1",val1,"champ2",val2,"champ3",val3
'Syntaxe 2 : m_Sql_AjouteEnreg "MaTable","champs1,champ2,champ3",val1,val2,val3
Dim i As Long
Dim Rs As New ADODB.Recordset
Dim Syntaxe As Integer
'On regard quelle syntaxe est utilisée
Syntaxe = 1
If UBound(FieldsAndValues) >= 0 Then
If InStr("" & FieldsAndValues(0), ",") > 0 Then
Syntaxe = 2
End If
End If
With Rs
'ajoute l'enreg
If Syntaxe = 1 Then
.Open "SELECT TOP 0 * FROM " & Table, g_Cnx, adOpenKeyset, adLockOptimistic
i = 0
.AddNew
Do Until i > UBound(FieldsAndValues)
.Fields(FieldsAndValues(i)).Value = FieldsAndValues(i + 1)
i = i + 2
Loop
.Update
.Close
Else
.Open "SELECT TOP 0 " & FieldsAndValues(0) & " FROM " & Table, g_Cnx, adOpenKeyset, adLockOptimistic
.AddNew
For i = 1 To UBound(FieldsAndValues)
.Fields(i - 1).Value = FieldsAndValues(i)
Next i
.Update
.Close
End If
End With
End Sub
Public Function m_Sql_OuvreRsL(SqlStr As String, ParamArray PrmLst() As Variant) As ADODB.Recordset
'*** Cette fonction fusionne l'instruction SqlStr et les valeurs PrmLst() comme décrite plus haut.
'Retourne un nouveau Recordset ouvert en mode lecture seule.
Dim i As Long
Dim x As String
Dim Rs As New ADODB.Recordset
'Fusion entre la chaîne et les paramètres
x = SqlStr
For i = 0 To UBound(PrmLst)
x = Replace(x, "%" & (i + 1) & "%", "" & PrmLst(i))
Next i
Rs.Open x, g_Cnx, adOpenKeyset, adLockReadOnly
p_TrouveDernierRs Rs
Set m_Sql_OuvreRsL = Rs
End Function
Public Function m_Sql_OuvreRsM(SqlStr As String, ParamArray PrmLst() As Variant) As ADODB.Recordset
'*** Cette fonction fusionne l'instruction SqlStr et les valeurs PrmLst() comme décrite plus haut.
'Retourne un nouveau Recordset ouvert en mode lecture/modif.
Dim i As Long
Dim x As String
Dim Rs As New ADODB.Recordset
'Fusion entre la chaîne et les paramètres
x = SqlStr
For i = 0 To UBound(PrmLst)
x = Replace(x, "%" & (i + 1) & "%", "" & PrmLst(i))
Next i
Rs.Open x, g_Cnx, adOpenKeyset, adLockOptimistic
p_TrouveDernierRs Rs
Set m_Sql_OuvreRsM = Rs
End Function
Public Function m_Sql_OuvreConnexion(Srv As String, Db As String, TrustedConn As Boolean, Optional Uid As String, Optional Pwd As String) As ADODB.Connection
'Ouvre un nouvelle connexion et retourne la connexion ouverte.
'Testé uniquement avec SQL-Server, merci de me dire si ça fonctionne aussi sous Oracle.
'NB : la paramètre TrustedConn doit être à True pour indiquer une connexion approuvée par le Login NT.
Dim Cnx As New ADODB.Connection
Dim x As String
x = ""
x = x & "PROVIDER=SQLOLEDB.1;"
x = x & "PERSIST SECURITY INFO=FALSE;"
x = x & "APPLICATION NAME=" & App.EXEName & ";"
x = x & "DATA SOURCE=" & Srv & ";"
x = x & "INITIAL CATALOG=" & Db & ";"
If TrustedConn = True Then
x = x & "INTEGRATED SECURITY=SSPI"
Cnx.Open x
Else
Cnx.Open x, Uid, Pwd
End If
Set m_Sql_OuvreConnexion = Cnx
End Function
Private Sub p_TrouveDernierRs(Rs As ADODB.Recordset)
'Il arrive parfois que des procédures sockées retourne plusieurs jeux d'enregistrements.
'Quand cela arrive, il faut récupérer le premier Recorset utilisable.
'NB : il très important de ne pas utiliser l'instruction With dans cette procédure car
' la varibale Rs est ré-assignée.
Do Until Rs.State <> 0
Set Rs = Rs.NextRecordset
Loop
End Sub
Public Function m_Sql_Montage(SqlStr As String, ParamArray PrmLst() As Variant) As String
'*** Cette fonction fusionne l'instruction SqlStr et les valeurs PrmLst() comme décrite plus haut.
'Cette fonction permet simplement de monter une chaîne texte SQL en fusionnant le modème avec les paramètres
Dim i As Long
Dim x As String
'Merging SqlStr and Parateters
x = SqlStr
For i = 0 To UBound(PrmLst)
x = Replace(x, "%" & (i + 1) & "%", "" & PrmLst(i))
Next i
m_Sql_Montage = x
End Function
A voir également
- Adodb select
- Sql simplifier - Meilleures réponses
- Ado sql - Meilleures réponses
- VB6\ADODB\Requête SQL\1 seul critère de sélection ✓ - Forum - Visual Basic 6
- Adodc1.recordsource = select * from ✓ - Forum - Visual Basic 6
- Adodb access - Conseils pratiques - Visual Basic / VB.NET
- Sql ajouter une colonne dans un select - Forum - SQL
- Adodb vb - Conseils pratiques - Visual Basic / VB.NET
26 nov. 2004 à 18:20
24 oct. 2003 à 12:18
Mais ton ensemble de fonction est lui aussi très lourd a mettre en place, sauf quand on fait un simple petit copier/coller.
La solution parfois la plus simple est d'utiliser les requêtes sur requêtes, il suffit d'ouvrir plusieur recordset et de faire appel d'un recordset dans l'autre !
24 déc. 2002 à 10:06
J'ai trouvé les fonctions SQL très utiles à titre d'exemple (je débute en VBA sous Access).
La fonction de fusion m'a fait bondir de joie : fini les concaténations à rallonge et une bonne simplification dans la création automatique des requêtes.
Si je n'ai pas mis 10/10, c'est à cause du titre qui est mal choisi. Il aurai du être du genre "Simplifier les requêtes SQL". En fait, je suis tombé sur la rubrique par hazard.
Cordialement
Cap de Boro
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.