De Access-database is een relationeel databasebeheersysteem dat effectief een grote hoeveelheid gegevens op een georganiseerde manier opslaat. Waar Excel een krachtig hulpmiddel is om gegevens om te zetten in zinvolle informatie. Excel kan echter niet te veel gegevens opslaan. Maar wanneer we Excel en Access samen gebruiken, neemt de kracht van deze tools exponentieel toe. Laten we dus leren hoe we de Access-database als gegevensbron via VBA kunnen verbinden met Excel.
Access-database verbinden als gegevensbron Excel
1: Referentie toevoegen aan AcitveX-gegevensobject
We zullen ADO gebruiken om verbinding te maken om toegang te krijgen tot de database. Dus eerst moeten we de verwijzing naar het ADO-object toevoegen.
Voeg een module toe aan je VBA-project en klik op de tools. Klik hier op de referenties.
Zoek nu naar Microsoft ActiveX Data Object Library. Controleer de laatste versie die je hebt. Ik heb 6.1. Klik op de knop OK en het is klaar. Nu zijn we klaar om een koppeling naar de Access Database te maken.
2. Schrijf een VBA-code om een verbinding met de Access-database tot stand te brengen
Om Excel te koppelen aan een Access-database, hebt u een Access-database nodig. De naam van mijn database is "Testdatabase.accdb". Het wordt opgeslagen op "C:\Users\Manish Singh\Desktop" plaats. Deze twee variabelen zijn belangrijk. U zult ze moeten wijzigen volgens uw behoeften. Rest code kan worden gehouden zoals het is.
Kopieer de onderstaande code om uw Excel VBA-module te maken en breng wijzigingen aan volgens uw vereisten. Ik heb elke regel van de onderstaande code uitgelegd:
Sub-ADO_Verbinding() 'Verbindingsobjecten en Recordset maken' Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String 'Het declareren van de volledig gekwalificeerde naam van de database. Wijzig het met de locatie en naam van uw database. DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" 'Dit is de provider van de verbinding. Onthoud dit voor je sollicitatiegesprek. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Dit is de verbindingsreeks die u nodig heeft bij het openen van de verbinding. connString = "Provider=" & PRVD & "Gegevensbron=" & DBPATH 'de verbinding openen' conn.Open connString 'de query die ik op de database wil uitvoeren. query = "SELECTEER * van klantT;" ' het uitvoeren van de query op de open verbinding. Het krijgt alle gegevens in de rec object. rec.Open query, conn 'de inhoud van de cellen wissen' Cellen.ClearContents 'gegevens uit de recordset halen en deze afdrukken in kolom A van het Excel-blad. Als (rec.RecordCount 0) Dan Doen Terwijl Niet rec.EOF Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _ rec.Fields (1).Value rec.MoveNext Loop End If 'verbindingen sluiten' rec.Sluit conn.Sluit End Sub
Kopieer de bovenstaande code of download het onderstaande bestand en breng wijzigingen aan in het bestand om aan uw vereisten te voldoen.
Bestand downloaden: VBA-database lerenWanneer u deze VBA-code uitvoert, maakt Excel een verbinding met de database. Daarna zal het de ontworpen query uitvoeren. Het zal alle oude inhoud op het blad wissen en de kolom A vullen met de waarden van Veld 1 (tweede veld) van de database.
Hoe werkt deze VBA Access-databaseverbinding?
Dim conn As New Connection, rec As New Recordset
In de bovenstaande regel declareren we niet alleen de variabelen Connection en recordset, maar initialiseren we deze rechtstreeks met het trefwoord New.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Deze twee lijnen zijn deelnemers. De DBPATH verandert alleen met uw database. PRVD verbindt OLE DB-provider.
conn.Open connString
Deze regel opent de verbinding met de database. Open is de functie van het verbindingsobject waarvoor meerdere argumenten nodig zijn. Het eerste en noodzakelijke argument is ConnectingString. Deze string bevat de OLE DB-provider (hier PRVD) en de gegevensbron (hier DBPATH). Het kan ook admin en wachtwoord gebruiken als optionele argumenten voor beveiligde databases.
De syntaxis van Connection.Open is:
verbinding.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
Aangezien ik geen ID en wachtwoord in mijn database heb, gebruik ik alleen ConnectionString. Het formaat van de ConnectionString is "Provider=provider_die u wilt gebruiken; Gegevensbron=volledig gekwalificeerde naam van database". We hebben deze string gemaakt en opgeslagen inconnString variabel.
query = "SELECTEER * van klantT;"
Dit is de query die ik op de database wil uitvoeren. U kunt alle vragen hebben die u wilt.
rec.Open query, conn
Deze instructie voert de gedefinieerde query uit in de gedefinieerde verbinding. Hier gebruiken we de Open methode van recordset object. Alle uitvoer wordt opgeslagen in het recordset-objectrec. U kunt waarden manipuleren of verwijderen uit het recordset-object.
Cellen.ClearContents
Deze regel wist de inhoud van het blad. Met andere woorden, verwijdert alles uit de cellen van het blad.
Als (rec.RecordCount 0) Dan Doen Terwijl Niet rec.EOF Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _ rec.Fields (1).Value rec.MoveNext Loop End If
De bovenstaande reeks regels controleert of de recordset leeg is of niet. Als de recordset niet leeg is (dit betekent dat de query een aantal records heeft geretourneerd), begint de lus en wordt elke waarde van veld 1 (tweede veld, in dit geval voornaam) in de laatste ongebruikte cel in de kolom afgedrukt.
(Dit wordt gebruikt om uit te leggen. Mogelijk hebt u deze regels niet. Als u alleen een verbinding met de database wilt openen, is de VBA-code boven deze regels voldoende.)
We hebben rec.EOF gebruikt om de lus tot het einde van de recordset te laten lopen. De rec.MoveNext wordt gebruikt om naar de volgende recordset te gaan. rec.Fields(1) wordt gebruikt om waarden uit veld 1 te halen (dit is de tweede omdat de veldindexering begint bij 0. In mijn database is het tweede veld de voornaam van de klant).
rec.Sluit conn.Close
Eindelijk, wanneer al het werk dat we wilden van de rec en conn gedaan is, sluiten we ze.
U kunt deze lijnen in een aparte subroutine hebben als u specifieke verbindingen afzonderlijk wilt openen en sluiten.
Dus ja jongens, zo maak je een verbinding met de ACCESS-database met behulp van ADO. Er zijn ook andere methoden, maar dit is de gemakkelijkste manier om via VBA verbinding te maken met een gegevensbron. Ik heb het zo gedetailleerd mogelijk uitgelegd. Laat me weten of dit nuttig was in de comments hieronder.
Gerelateerde artikelen:
Gebruik een gesloten werkmap als database (DAO) met VBA in Microsoft Excel | Gebruik dit VBA-fragment in Excel om een gesloten werkmap als database met DAO-verbinding te gebruiken.
Gebruik een gesloten werkmap als database (ADO) met VBA in Microsoft Excel | Gebruik dit VBA-fragment in Excel om een gesloten werkmap als database met ADO-verbinding te gebruiken.
Aan de slag met Excel VBA UserForms | Om gegevens in de database in te voegen, gebruiken we formulieren. De Excel UserForms zijn handig om informatie van de gebruiker te krijgen. Hier leest u hoe u moet beginnen met VBA-gebruikersformulieren.
Verander de waarde/inhoud van verschillende UserForm-controls met VBA in Excel | Gebruik dit eenvoudige VBA-fragment om de inhoud van de bedieningselementen van het gebruikersformulier te wijzigen.
Voorkom dat een gebruikersformulier wordt gesloten wanneer de gebruiker op de x-knop klikt door VBA in Excel te gebruiken | Om te voorkomen dat het gebruikersformulier wordt gesloten wanneer de gebruiker op de x-knop van het formulier klikt, gebruiken we de gebeurtenis UserForm_QueryClose.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
De VERT.ZOEKEN-functie in Excel | Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
AANTAL.ALS in Excel 2016 | Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om een specifieke waarde te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.