VBA
Travailler avec ADO
Recherche…
Remarques
Les exemples présentés dans cette rubrique utilisent une liaison anticipée pour plus de clarté et nécessitent une référence à la bibliothèque Microsoft ActiveX Data Object xx. Ils peuvent être convertis en liaison tardive en remplaçant les références fortement typées par Object
et en remplaçant la création d'objets à l'aide de New
par CreateObject
cas échéant.
Connexion à une source de données
La première étape pour accéder à une source de données via ADO consiste à créer un objet ADO Connection
. Cela se fait généralement en utilisant une chaîne de connexion pour spécifier les paramètres de la source de données, bien qu'il soit également possible d'ouvrir une connexion DSN en transmettant le DSN, l'ID utilisateur et le mot de passe à la méthode .Open
.
Notez qu'un DSN n'est pas obligé de se connecter à une source de données via ADO - toute source de données disposant d'un fournisseur ODBC peut être connectée à l'aide de la chaîne de connexion appropriée. Bien que des chaînes de connexion spécifiques à différents fournisseurs soient hors de la portée de cette rubrique, ConnectionStrings.com est une excellente référence pour trouver la chaîne appropriée à votre fournisseur.
Const SomeDSN As String = "DSN=SomeDSN;Uid=UserName;Pwd=MyPassword;"
Public Sub Example()
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
'... Do work.
database.Close 'Make sure to close all database connections.
End If
End Sub
Public Function OpenDatabaseConnection(ConnString As String) As ADODB.Connection
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = New ADODB.Connection
With database
.ConnectionString = ConnString
.ConnectionTimeout = 10 'Value is given in seconds.
.Open
End With
OpenDatabaseConnection = database
Exit Function
Handler:
Debug.Print "Database connection failed. Check your connection string."
End Function
Notez que le mot de passe de la base de données n'est inclus dans la chaîne de connexion de l'exemple ci-dessus que pour des raisons de clarté. Les meilleures pratiques exigent de ne pas stocker les mots de passe de base de données dans le code. Cela peut être accompli en prenant le mot de passe via l'entrée utilisateur ou en utilisant l'authentification Windows.
Récupérer des enregistrements avec une requête
Les requêtes peuvent être exécutées de deux manières, les deux Recordset
objet ADO Recordset
qui est une collection de lignes renvoyées. Notez que les deux exemples ci-dessous utilisent la fonction OpenDatabaseConnection
partir de l'exemple Créer une connexion à une source de données pour des raisons de brièveté. Rappelez-vous que la syntaxe du SQL transmis à la source de données est spécifique au fournisseur.
La première méthode consiste à transmettre l'instruction SQL directement à l'objet Connection et constitue la méthode la plus simple pour exécuter des requêtes simples:
Public Sub DisplayDistinctItems()
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
Dim records As ADODB.Recordset
Set records = database.Execute("SELECT DISTINCT Item FROM Table")
'Loop through the returned Recordset.
Do While Not records.EOF 'EOF is false when there are more records.
'Individual fields are indexed either by name or 0 based ordinal.
'Note that this is using the default .Fields member of the Recordset.
Debug.Print records("Item")
'Move to the next record.
records.MoveNext
Loop
End If
CleanExit:
If Not records Is Nothing Then records.Close
If Not database Is Nothing And database.State = adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume CleanExit
End Sub
La deuxième méthode consiste à créer un objet de Command
ADO pour la requête que vous souhaitez exécuter. Cela nécessite un peu plus de code, mais est nécessaire pour pouvoir utiliser des requêtes paramétrées:
Public Sub DisplayDistinctItems()
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
Dim query As ADODB.Command
Set query = New ADODB.Command
'Build the command to pass to the data source.
With query
.ActiveConnection = database
.CommandText = "SELECT DISTINCT Item FROM Table"
.CommandType = adCmdText
End With
Dim records As ADODB.Recordset
'Execute the command to retrieve the recordset.
Set records = query.Execute()
Do While Not records.EOF
Debug.Print records("Item")
records.MoveNext
Loop
End If
CleanExit:
If Not records Is Nothing Then records.Close
If Not database Is Nothing And database.State = adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume CleanExit
End Sub
Notez que les commandes envoyées à la source de données sont vulnérables à l'injection SQL , intentionnelle ou non. En général, les requêtes ne doivent pas être créées en concaténant une entrée utilisateur quelconque. Au lieu de cela, ils doivent être paramétrés (voir Création de commandes paramétrées ).
Exécution de fonctions non scalaires
Les connexions ADO peuvent être utilisées pour effectuer à peu près toutes les fonctions de base de données prises en charge par le fournisseur via SQL. Dans ce cas, il n'est pas toujours nécessaire d'utiliser le jeu d' Recordset
renvoyé par la fonction Execute
, bien qu'il puisse être utile pour obtenir des affectations de clés après des instructions INSERT avec @@ Identity ou des commandes SQL similaires. Notez que l'exemple ci-dessous utilise la fonction OpenDatabaseConnection
partir de l'exemple Créer une connexion à une source de données pour des raisons de brièveté.
Public Sub UpdateTheFoos()
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
Dim update As ADODB.Command
Set update = New ADODB.Command
'Build the command to pass to the data source.
With update
.ActiveConnection = database
.CommandText = "UPDATE Table SET Foo = 42 WHERE Bar IS NULL"
.CommandType = adCmdText
.Execute 'We don't need the return from the DB, so ignore it.
End With
End If
CleanExit:
If Not database Is Nothing And database.State = adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume CleanExit
End Sub
Notez que les commandes envoyées à la source de données sont vulnérables à l'injection SQL , intentionnelle ou non. En général, les instructions SQL ne doivent pas être créées en concaténant une entrée utilisateur quelconque. Au lieu de cela, ils doivent être paramétrés (voir Création de commandes paramétrées ).
Création de commandes paramétrées
Chaque fois que SQL exécuté via une connexion ADO doit contenir une entrée utilisateur, il est conseillé de le paramétrer afin de minimiser les risques d'injection SQL. Cette méthode est également plus lisible que les longues concaténations et facilite un code plus robuste et maintenable (en utilisant une fonction qui renvoie un tableau de Parameter
).
Dans la syntaxe ODBC standard, les paramètres sont donnés ?
"espaces réservés" dans le texte de la requête, puis les paramètres sont ajoutés à la Command
dans l'ordre dans lequel ils apparaissent dans la requête.
Notez que l'exemple ci-dessous utilise la fonction OpenDatabaseConnection
partir de la création d'une connexion à une source de données pour plus de concision.
Public Sub UpdateTheFoos()
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
Dim update As ADODB.Command
Set update = New ADODB.Command
'Build the command to pass to the data source.
With update
.ActiveConnection = database
.CommandText = "UPDATE Table SET Foo = ? WHERE Bar = ?"
.CommandType = adCmdText
'Create the parameters.
Dim fooValue As ADODB.Parameter
Set fooValue = .CreateParameter("FooValue", adNumeric, adParamInput)
fooValue.Value = 42
Dim condition As ADODB.Parameter
Set condition = .CreateParameter("Condition", adBSTR, adParamInput)
condition.Value = "Bar"
'Add the parameters to the Command
.Parameters.Append fooValue
.Parameters.Append condition
.Execute
End With
End If
CleanExit:
If Not database Is Nothing And database.State = adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume CleanExit
End Sub
Remarque: L'exemple ci-dessus illustre une instruction UPDATE paramétrée, mais toute instruction SQL peut recevoir des paramètres.