FAQ MS-Access
FAQ MS-AccessConsultez toutes les FAQ
Nombre d'auteurs : 140, nombre de questions : 926, dernière mise à jour : 15 juin 2021
Sommaire→VBA→SQL et VBA- Comment exécuter/utiliser du SQL dans VBA ?
- Comment créer un jeu de données (Recordset) ?
- Comment parcourir un jeu d'enregistrements (Recordset) ?
- Pourquoi une erreur d'exécution '13' « Incompatiblité de type » ?
- Comment utiliser les méthodes FindFirst/FindLast/FindNext/FindPrevious ou la méthode Seek ou la méthode Find avec un Recordset ?
- Comment supprimer des enregistrements lorsque l'intégrité référentielle est activée ?
- Je n'arrive pas à supprimer ma table. Pourquoi ?
- Comment effacer toutes les données de la base ?
- Comment exécuter une requête action (ajout, suppression ou mise à jour) ?
- Comment supprimer les messages d'alertes ?
- Pourquoi les messages d'avertissement n'apparaissent-ils pas lorsque j'exécute mes requêtes action en VBA ?
- Comment insérer dans une table une chaîne de caractères contenant des quotes ?
- Comment tester l'existence d'une requête en VBA et la supprimer ?
- Pourquoi ma requête ne fonctionne-t-elle pas selon le type des variables qu'elle utilise ?
- Comment insérer des enregistrements de la table1 dans la table2, puis effacer ces enregistrements de la table1 ?
- Comment faire une requête qui recherche les doublons dans une table ?
- Comment se connecter en ADO à une base de données pour exécuter une requête action ?
- Est-il plus performant de faire appel à une requête enregistrée (OpenQuery) ou bien de l'écrire dans le code (VBA) et de l'exécuter avec DoCmd.RunSql ?
- Est-il possible de créer une requête paramétrée dont la valeur du paramètre proviendrait d'une variable ?
- Comment définir en VBA le SQL d'une requête existante ?
- Comment personnaliser le message d'erreur d'Access lors de doublons ?
- Comment chercher les enregistrements contenant une certaine chaîne (like) ?
- Pour insérer des données, vaut-il mieux passer par un Recordset ou par une requête de type INSERT ?
- Comment supprimer l'apparition des messages d'avertissement pour toute l'application ?
- Comment récupérer le résultat de ma requête en VBA ?
Voici deux liens.
Le premier vous amènera aux Recordsets, qui servent à exploiter les requêtes de sélection (SELECT).
Le second vous expliquera comment exécuter des requêtes d'action (INSERT [insertion], UPDATE [mise à jour], ...).
Lien : Comment créer un jeu de données (Recordset) ?
Lien : Comment exécuter une requête action (ajout, suppression ou mise à jour) ?
DAO
Sub DAOOpenRecordset ()
Dim db As DAO.Database, rst As DAO.Recordset, fld As DAO.Field
Dim sSQL As String
' Ouverture de la base de données
Set db = DBEngine.OpenDatabase (".\Comptoir.mdb")
sSQL = "Select * From CLIENTS Where Région= \'WA\'"
' Ouverture du Recordset
Set rst = db.OpenRecordset (sSQL, dbOpenForwardOnly, dbReadOnly)
' Fermeture du Recordset
rst.Close
End SubNotes
-> Il est important d'écrire "DAO." lors de la déclaration.
-> Pensez également à cocher la référence Microsoft DAO 3.6 Object Library (dans un module faites Outils / références).
ADO
Sub ADOOpenRecordset ()
Dim cnn As New ADODB.connection, rst As New ADODB.Recordset, fld As ADODB.Field
' Ouverture de la connexion
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=.\Comptoir.mdb;"
' Ouverture du Recordset en défilement en avant, et en lecture seule
rst.Open "SELECT * FROM Clients WHERE Région =\'WA\'", cnn, adOpenForwardOnly, adLockReadOnly
' Fermeture du Recordset
rst.Close
End SubLien : Définition et manipulation de données avec DAO par Tofalu
Lien : Comment déclarer une référence dans MS Access ?
Pour exécuter ce code, il faut activer la référence : Microsoft DAO 3.x Object Library.
Ces exemples permettent de parcourir un Recordset
If Not oRecordset.EOF Then
Rst.MoveFirst
While Not rst.Eof
' Code
rst.MoveNext
Wend
else
Msgbox "Le jeu d'enregistrements est vide"
End ifOu
Do Until rst.Eof
' Code
rst.MoveNext
LoopLien : Définition et manipulation de données avec DAO par Tofalu
Lien : Comment déclarer une référence dans MS Access ?
Modifiez la déclaration du Recordset :
Dim Rst as DAO.RecordSet
Nous rajoutons le DAO devant RecordSet afin d'éviter les conflits entre bibliothèques ayant certains objets portant le même nom.
En effet ici, les bibliothèques DAO et ADODB possèdent chacune un objet nommé "RecordSet". Ainsi, en écrivant DAO.RecordSet nous savons à quelle bibliothèque nous faisons appel.
Lorsque la bibliothèque n'est pas précisée, le type choisi par défaut correspond à celui de la première bibliothèque compatible référencée dans le projet VBA (en partant du haut).
Pour exécuter ce code, il faut activer la référence : Microsoft DAO 3.x Object Library.
Lien : Pourquoi une erreur de compilation : « Type défini par l'utilisateur non défini » ?
Lien : Définition et manipulation de données avec DAO par Tofalu
Lien : Comment déclarer une référence dans MS Access ?
Pour un DAO.Recordset
Pour exécuter ce code, il faut activer la référence : Microsoft DAO 3.x Object Library.
- Si le Recordset est de type Table (dbOpenTable) : utilisez la méthode Seek.
Celle-ci requiert l'utilisation d'une table indexée (voir l'aide pour plus d'infos).
Lorsque vous utilisez la méthode OpenRecordset, si vous ne précisez pas l'argument type, VBA créera par défaut un Recordset de type Table si celui-ci est basé sur une table.
- Pour les autres types de Recordset : utilisez les méthodes FindFirst / FindLast / FindNext / FindPrevious.
Pour un ADODB.Recordset
Pour exécuter ce code, il faut activer les références : Microsot ADO Ext 2.X for dll and security et Microsof ActiveX Data Object 2.X Library.
Utilisez la méthode Find.
Lien : Définition et manipulation de données avec DAO par Tofalu
Lien : Comment déclarer une référence dans MS Access ?
On ne peut effacer la clé primaire sans effacer les clés étrangères correspondantes.
L'intégrité référentielle est un mécanisme de vérification qui s'assure à chaque ajout, modification ou suppression d'une clé (étrangère ou primaire) qu'il y aura toujours la clé primaire correspondant à la clé étrangère.
Cependant il est parfaitement possible de supprimer des clés étrangères.
Vous essayez de supprimer une table sans succès en utilisant une requête de type "DROP TABLE" ou en utilisant la commande DeleteObject.
Cela provient certainement d'un Recordset chargé avec les données de la table. Cette dernière est alors considérée comme "verrouillée" et donc impossible à supprimer.
Il suffit de fermer le Recordset pour déverrouiller la table :
MonRecordSet.CloseCette ligne doit bien sûr être placée avant la suppression.
Voici comment effacer le contenu d'une table :
dim SQL as string
SQL = "Delete * From TABLE"
Docmd.RunSQL SQLIl suffit de réitérer l'action pour chaque table.
On peut aussi imaginer récupérer le nom de toutes les tables dans un tableau, et pour chaque table exécuter la suppression.
DAO
Pour exécuter ce code, il faut activer la référence : Microsoft DAO 3.x Object Library.
Sub DAOExecuteBulkOpQuery()
Dim db As DAO.Database
Set db = DBEngine.OpenDatabase (".\Comptoir.mdb")
' Exécution de la requête
db.Execute "Update CLIENTS Set PAYS = \'États-Unis\' Where PAYS = \'USA\'"
Debug.Print "Records Affected = " & db.RecordsAffected
db.Close
End Sub
ADO
Pour exécuter ce code, il faut activer les références : Microsot ADO Ext 2.X for dll and security et Microsof ActiveX Data Object 2.X Library.
Sub ADOExecuteBulkOpQuery()
Dim cnn As New ADODB.connection, iAffected As Integer
Dim sSQL As String
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=.\Comptoir.mdb;"
sSQL = "Update CLIENTS Set PAYS = \'États-Unis\' Where PAYS = \'USA\'"
' Exécution de la requête
cnn.Execute sSQL, iAffected, adExecuteNoRecords
Debug.Print "Records Affected = " & iAffected
cnn.Close
End SubLien : Définition et manipulation de données avec DAO par Tofalu
Lien : Comment déclarer une référence dans MS Access ?
En VBA, placez ce code avant la requête d'insertion/de modification :
DoCmd.SetWarnings False
Macro : Avertissements=Non.
Pensez à rétablir les messages après la requête :
DoCmd.SetWarnings True
Macro : Avertissements=Oui.
Lien : Comment supprimer l'apparition des messages d'avertissement pour toute l'application ?
Tout d'abord, vérifiez que l'option d'avertissement n'est pas désélectionnée dans Access :
Menu Outils, Options, Modifier/Rechercher, Confirmer => Requête Action = Oui.
Ensuite, si vous utilisez la méthode Docmd.SetWarnings, assurez-vous que le dernier appel avant l'exécution de votre requête active les messages.
Enfin vous devez utiliser la méthode Docmd.RunSql car la méthode Execute d'un objet Database ne déclenche jamais les messages d'avertissement Access.
Docmd.SetWarnings True
Docmd.RunSql ""Update MATABLE Set MATABLE.[MONCHAMP] = ""DVP.COM"";"Il faut faire ceci :
Dim val As String
val = "l\'exemple"
CurrentDb.Execute "Insert Into TEST Values(""" & val & """)"Voici une solution :
Pour exécuter ce code, il faut activer la référence : Microsoft DAO 3.x Object Library.
Sub supp()
' Pour fonctionner ce code nécessite la référence
' - Microsoft DAO X Object Library
Dim oDb As DAO.Database
Dim oQdf As DAO.QueryDef
Dim strReqName As String 'Nom de la requête à supprimer
' Accède à la base de données courante
Set oDb = CurrentDb
strReqName = "R1"
' Parcourt toutes les requêtes jusqu'à la suppression
For Each oQdf In oDb.QueryDefs
With oQdf
If .Name = strReqName Then
' Une requête porte le nom recherché, donc nous la supprimons
oDb.QueryDefs.Delete strReqName
' Quitte la boucle
Exit For
End If
End With
Next oQdf
End SubLe même code avec une gestion d'erreur :
Sub supp()
' Pour fonctionner ce code nécessite la référence
' - Microsoft DAO X Object Library
' Gestion d'erreur
On Error GoTo err
Dim oDb As DAO.Database
Dim oQdf As DAO.QueryDef
Dim strReqName As String 'Nom de la requête à supprimer
' Accède à la base de données courante
Set oDb = CurrentDb
strReqName = "R2"
' Tente la suppression
oDb.QueryDefs.Delete strReqName
' Affiche un message
MsgBox "La requête " & strReqName & " a été supprimée"
fin:
' Libère l'objet oDb
Set oDb = Nothing
Exit Sub
err:
' Affiche un message suivant l'erreur
Select Case err.Number
Case 3265: MsgBox "La requête n'existe pas"
Case Else: MsgBox "Erreur critique inconnue"
End Select
' Termine le programme
Resume fin
End SubLien : Comment savoir si une table existe ?
Lien : Comment supprimer une table si elle existe ?
Lien : Définition et manipulation de données avec DAO par Tofalu
Lien : Comment déclarer une référence dans MS Access ?
Il faut utiliser des marqueurs différents selon le type de valeur :
Numérique (aucun délimiteur)
SQL & "And roulage!numéro = " & Me.cmbRechNumeroTexte ( Chr(34) crée des ")
SQL = SQL & "And roulage!repère = "& chr(34) & Me.cmbRechRepere & chr(34)Date (Délimiteur # et format mm/dd/yyyy)
SQL & "And roulage!LADATE= #" & format(Me.txtRechDateDebut,"mm/dd/yyyy") & "#"Testé sous Access 2000 :
Sub Sauvegarder()
' Création des deux Recordsets
Dim myRS1 As New ADODB.Recordset
Dim myRS2 As New ADODB.Recordset
myRS1.Open "Table1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
myRS2.Open "Table2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
' Parcours du RS pour ajouter à table1 tout en supprimant de table2
Do Until myRS2.EOF
With myRS1
.AddNew Array("Champ1", "Champ2", "Champ3"), _
Array(myRS2("Alpha"), myRS2("Beta"), myRS2("Gamma"))
.Update
End With
myRS2.Delete
myRS2.MoveNext
Loop
' Fermeture des Recordsets
myRS1.Close
myRS2.Close
Set myRS1 = Nothing
Set myRS2 = Nothing
End SubSelect TABLE.CHAMP1, Sum(TABLE.CHAMP2) AS CHAMP2 Into NOUVELLETABLE
From TABLE
Group By TABLE.CHAMP1,
Order By TABLE.CHAMP1,Où Champ1 est le champ comportant les doublons à supprimer.
Dim cn as ADODB.Connection
Dim cmd as ADODB.Command
Dim insQuery as String
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
cn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\CS527\P1\bd1_archive.mdb;Persist Security Info=False;" & _
"Jet OLEDB:System database=c:\LePath\LeFichier.mdw"
insQuery = "INSERT INTO purchasearchive VALUES (" & Chr(34) _
& PUID & Chr(34) & "," & Chr(34) _
& cardNum & Chr(34) & "," & Chr(34) _
& cardType & Chr(34) & "," & Chr(34) _
& productID & Chr(34) & ",#" _
& date & "#," _
& number & ");"
cn.Open
cmd.ActiveConnection=cn
cmd.CommandText=insQuery
cmd.ExecutePour se connecter à une base de données sécurisée au niveau utilisateur : dans la chaîne de connexion, ajoute ceci :
Password=LeMotDePasse;User ID=LeUseren mettant le bon mot de passe et le bon User.
Si la requête utilise des critères basés sur des champs indexés, il est alors préférable de passer par une requête et OpenQuery.
Docmd.Runsql ne fait qu'exécuter une instruction SQL, tandis que OpenQuery exécute une requête enregistrée (bénéficiant de la technologie Rushmore).
Cette technologie est notamment basée sur l'usage des index pour la recherche d'un enregistrement.
Ceux-ci sont stockés dans une structure cachée.
Si les critères sont posés sur des champs indexés, lorsque la requête s'exécute, elle peut être optimisée car la recherche se fait sur la structure des index et non directement sur la table.
Nous avons une requête du type :
Parameters TON_PARAM Text;
Insert Into TABLE1 Select CHAMP1 From TABLE2 Where CHAMP2 = [TON_PARAM];Voici un exemple de code :
Set qdf= CurrentDb.QueryDefs("TAREQUETE")
With qdf
.Parameters("TON_PARAM") = TaVariable
.Execute
End WithPour exécuter ce code, il faut activer la référence : Microsoft DAO 3.x Object Library.
Dim qdf As DAO.Querydef
Set qdf = Currentdb.Querydefs!NomDeMaRequête
qdf.SQL = "Select * From MaTABLE"
Set qdf = NothingLien : Définition et manipulation de données avec DAO par Tofalu
Lien : Comment déclarer une référence dans MS Access ?
Par exemple ici, on tente d'enregistrer dans une table un nom qui existe déjà. Or le champ Nom n'accepte pas les doublons.
Sub Form_Error(DataErr As Integer, Response As Integer)
' Si l'erreur est due à un doublon, la fonction affiche un message permettant d'annuler une commande.
Const ERR_DOUBLON = 3022 ' Erreur de doublon
' DataErr reçoit le code erreur de la part du moteur de la BDD ou de l'interface
Select Case DataErr
Case ERR_DOUBLON
' Votre message...
MsgBox "Ce nom existe déjà.", vbExclamation, "Attention"
' La zone à surveiller
[Client].SetFocus
' Demande à Access de continuer l'exécution sans afficher le message standard
Response = acDataErrContinue
End Select
End Subrequete = "Select * From carnet Where Prénom Like \'*" & prenom & "*\'"Dans ce cas, prénom est une variable ou un contrôle contenant la chaîne que nous cherchons.
L'utilisation d'un Recordset rend le traitement plus rapide.
Ce code vous permettra de constater vous-même la différence.
Pour exécuter ce code, il faut activer la référence : Microsoft DAO 3.x Object Library.
Function BenchmarkIt()
Dim t0 As Single, t1 As Single
Dim rst As DAO.Recordset
Dim i As Long
t0 = Timer
For i = 1 To 5000
CurrentDb.Execute "INSERT INTO [tblINSERT] VALUES (" & Format(i, "000000") & ");"
Next i
t1 = Timer
Debug.Print "Exécution par INSERT : " & Format(t1 - t0, "0.000") & " s"
t0 = Timer
Set rst = CurrentDb.OpenRecordset("tblRecordset")
For i = 1 To 5000
rst.AddNew
rst!dum = Format(i, "000000")
rst.Update
Next i
rst.Close
set rst = Nothing
t1 = Timer
Debug.Print "Exécution par RECORDSET : " & Format(t1 - t0, "0.000") & " s"
End Function
Ce code donne :
benchmarkit
Exécution par INSERT : 5,328 s
Exécution par RECORDSET : 0,141 s
Il est possible que la différence soit due au fait que le Recordset travaille avec la mémoire avant d'écrire sur le disque, alors que n requêtes provoquent n accès disque.
Lien : Définition et manipulation de données avec DAO par Tofalu
Lien : Comment déclarer une référence dans MS Access ?
Outils/Options, Modifier/Rechercher
Dans le cadre Confirmer décochez ce que bon vous semble.
Vous trouverez ci-dessous un lien vous indiquant où se trouve la réponse à votre question. Intéressez-vous plus particulièrement au Recordset.