Comment connecter Excel à base de données Access en utilisant VBA
La base de données Access est un système de gestion de base de données relationnelle qui enregistre efficacement une grande quantité de données de manière organisée. Où Excel est un outil puissant pour réduire les données en informations significatives. Cependant, Excel ne peut pas stocker trop de données. Mais lorsque nous utilisons Excel et Access ensemble, la puissance de ces outils augmente de façon exponentielle. Alors, apprenons à connecter la base de données Access en tant que source de données à Excel via VBA.
Connexion de la base de données Access en tant que source de données Excel
1: Ajouter une référence à l’objet de données AcitveX
Nous utiliserons ADO pour nous connecter pour accéder à la base de données. Nous devons donc d’abord ajouter la référence à l’objet ADO.
Ajoutez un module à votre projet VBA et cliquez sur les outils. Cliquez ici sur les références.
Recherchez maintenant la bibliothèque d’objets de données Microsoft ActiveX. Vérifiez la dernière version dont vous disposez. J’ai 6.1. Cliquez sur le bouton OK et c’est fait. Nous sommes maintenant prêts à créer un lien vers la base de données Access.
2. Ecrire un code VBA pour établir une connexion à la base de données Access
Pour connecter Excel à une base de données Access, vous devez disposer d’une base de données Access. Le nom de ma base de données est « Test Database.accdb ». Il est enregistré à l’emplacement « C: \ Users \ Manish Singh \ Desktop ». Ces deux variables sont importantes. Vous devrez les modifier en fonction de vos besoins. Le code de repos peut être conservé tel quel.
Copiez le code ci-dessous pour créer votre module Excel VBA et apporter des modifications selon vos besoins. J’ai expliqué chaque ligne du code ci-dessous:
Sub ADO_Connection()
'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb"
'This is the connection provider. Remember this for your interview.
PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
conn.Open connString
'the query I want to run on the database.
query = "SELECT * from customerT;"
'running the query on the open connection. It will get all the data in the rec object.
rec.Open query, conn
'clearing the content of the cells
Cells.ClearContents
'getting data from the recordset if any and printing it in column A of excel sheet.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
'closing the connections
rec.Close
conn.Close
End Sub
Copiez le code ci-dessus ou téléchargez le fichier ci-dessous et apportez des modifications au fichier en fonction de vos besoins.
`link: /wp-content-uploads-2020-02-VBA-Database-Learning.xls [__ Télécharger le fichier: VBA Database Learning]
Lorsque vous exécutez ce code VBA, Excel établira une connexion à la base de données. Ensuite, il exécutera la requête conçue. Il effacera tout ancien contenu de la feuille et remplira la colonne A avec les valeurs du champ 1 (deuxième champ) de la base de données.
Comment fonctionne cette connexion à la base de données VBA Access?
Dim conn As New Connection, rec As New Recordset
Dans la ligne ci-dessus, nous ne déclarons pas seulement les variables de connexion et de jeu d’enregistrements, mais l’initialisons directement à l’aide du mot-clé New.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Ces deux lignes sont des concurrents. Le DBPATH changera avec votre base de données uniquement. PRVD connecte le fournisseur OLE DB.
conn.Open connString
Cette ligne ouvre la connexion à la base de données. Open est la fonction de l’objet de connexion qui prend plusieurs arguments. Le premier argument nécessaire est ConnectingString. Cette chaîne contient le fournisseur OLE DB (ici PRVD) et la source de données (ici DBPATH). Il peut également prendre admin et password comme arguments facultatifs pour les bases de données protégées.
La syntaxe de Connection.Open est:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
Comme je n’ai pas d’identifiant ni de mot de passe dans ma base de données, j’utilise uniquement ConnectionString. Le format de ConnectionString est « Provider = provider_you want to use; _ Data Source = _fully qualifié name of database« . Nous avons créé et enregistré cette variable chaîne inconnString.
query = "SELECT * from customerT;"
C’est la requête que je souhaite exécuter sur la base de données. Vous pouvez avoir toutes les requêtes que vous souhaitez.
rec.Open query, conn
Cette instruction exécute la requête définie dans la connexion définie. Ici, nous utilisons la méthode Open de l’objet recordset. Toute la sortie est enregistrée dans le jeu d’enregistrements objectrec. Vous pouvez récupérer des valeurs de manipulation ou de suppression de l’objet de jeu d’enregistrements.
Cells.ClearContents
Cette ligne efface le contenu de la feuille. En d’autres termes, supprime tout des cellules de la feuille.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
L’ensemble de lignes ci-dessus vérifie si le jeu d’enregistrements est vide ou non. Si le jeu d’enregistrements n’est pas vide (cela signifie que la requête a renvoyé des enregistrements), la boucle commence et commence à imprimer chaque valeur du champ 1 (deuxième champ, prénom dans ce cas) dans // cells-ranges-rows-and-columns-in vba / 3-best-ways-to-find-last-non-blank-row-and-column-using-vba.html [dernière cellule inutilisée de la colonne] `.
(Ceci est utilisé juste pour expliquer. Vous n’avez peut-être pas ces lignes. Si vous voulez simplement ouvrir une connexion à la base de données, le code VBA au-dessus de ces lignes suffit.)
Nous avons utilisé rec.EOF pour exécuter la boucle jusqu’à la fin du jeu d’enregistrements. Le rec.MoveNext est utilisé pour passer au jeu d’enregistrements suivant. rec.Fields (1) est utilisé pour obtenir des valeurs du champ 1 (qui est le deuxième car son indexation de champ commence à 0. Dans ma base de données, le deuxième champ est le prénom du client).
rec.Close conn.Close
Enfin, lorsque tout le travail que nous voulions de la rec et de la connexion est terminé, nous les fermons.
Vous pouvez avoir ces lignes dans un sous-programme distinct si vous souhaitez ouvrir et fermer séparément des connexions spécifiques.
Alors oui les gars, voici comment établir une connexion à la base de données ACCESS en utilisant ADO. Il existe également d’autres méthodes, mais c’est le moyen le plus simple de se connecter à une source de données d’accès via VBA. Je l’ai expliqué le plus en détail possible. Faites-moi savoir si cela vous a été utile dans la section commentaires ci-dessous.
Articles liés:
`link: / import-and-export-in-vba-use-a-closed-workbook-as-a-database-dao-using-vba-in-microsoft-excel [Utiliser un classeur fermé comme base de données (DAO) utilisant VBA dans Microsoft Excel] `| Pour utiliser un classeur fermé en tant que base de données avec une connexion DAO, utilisez cet extrait de code VBA dans Excel.
`link: / import-and-export-in-vba-use-a-closed-workbook-as-a-database-ado-using-vba-in-microsoft-excel [Utiliser un classeur fermé comme base de données (ADO) utilisant VBA dans Microsoft Excel] `| Pour utiliser un classeur fermé comme base de données avec une connexion ADO, utilisez cet extrait de code VBA dans Excel.
link: / applications-word-outlook-in-vba-getting-started-with-excel-vba-userforms [Premiers pas avec Excel VBA UserForms] | * Pour insérer des données dans la base de données, nous utilisons des formulaires. Les UserForms d’Excel sont utiles pour obtenir des informations de l’utilisateur. Voici comment vous devez commencer avec les formulaires utilisateur VBA.
===
`link: / user-forms-input-boxes-in-vba-change-the-valueecontent-of-multiple-userform-controls-using-vba-in-microsoft-excel [Change la valeur / le contenu de plusieurs contrôles UserForm using VBA in Excel] `| * Pour modifier le contenu des contrôles de formulaire utilisateur, utilisez ce simple extrait de code VBA.
`lien: / user-forms-input-boxes-in-vba-empêche-un-userform-de-fermer-lorsque-l’utilisateur-clique-sur-le-bouton-x-en utilisant-vba-in-microsoft-excel [ Empêcher un formulaire utilisateur de se fermer lorsque l’utilisateur clique sur le bouton x à l’aide de VBA dans Excel] `| Pour empêcher le formulaire utilisateur de se fermer lorsque l’utilisateur clique sur le bouton x du formulaire, nous utilisons l’événement UserForm_QueryClose.
Articles populaires:
lien: / clavier-formule-raccourcis-50-excel-raccourcis-pour-augmenter-votre-productivité [50 raccourcis Excel pour augmenter votre productivité] | Accélérez votre tâche. Ces 50 raccourcis vous permettront de travailler encore plus rapidement sur Excel.
lien: / formules-et-fonctions-introduction-de-vlookup-function [La fonction RECHERCHEV dans Excel] | C’est l’une des fonctions les plus utilisées et les plus populaires d’Excel qui est utilisée pour rechercher des valeurs à partir de différentes plages et feuilles.
lien: / tips-countif-in-microsoft-excel [COUNTIF dans Excel 2016] | Comptez les valeurs avec des conditions en utilisant cette fonction étonnante. Vous n’avez pas besoin de filtrer vos données pour compter une valeur spécifique.
La fonction Countif est indispensable pour préparer votre tableau de bord.
lien: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel] | C’est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.