Access -databasen er et relationsdatabasestyringssystem, der effektivt gemmer en stor mængde data på en organiseret måde. Hvor Excel er et kraftfuldt værktøj til at knuse data til meningsfuld information. Excel kan dog ikke gemme for mange data. Men når vi bruger Excel og Access sammen, øges effekten af disse værktøjer eksponentielt. Så lad os lære at forbinde Access -databasen som en datakilde til Excel via VBA.
Tilslutning af Access Database som datakilde Excel
1: Tilføj reference til AcitveX Data Object
Vi bruger ADO til at oprette forbindelse til databasen. Så først skal vi tilføje referencen til ADO -objektet.
Tilføj et modul til dit VBA -projekt, og klik på værktøjerne. Klik her på referencerne.
Se nu efter Microsoft ActiveX Data Object Library. Tjek den seneste version, du har. Jeg har 6.1. Klik på knappen OK, og det er gjort. Nu er vi klar til at oprette et link til Access Database.
2. Skriv en VBA -kode for at oprette forbindelse til Access Database
For at forbinde Excel til en Access -database skal du have en Access -database. Min database hedder "Test Database.accdb ". Det gemmes kl "C: \ Brugere \ Manish Singh \ Desktop" Beliggenhed. Disse to variabler er vigtige. Du bliver nødt til at ændre dem efter dine behov. Restkode kan bevares, som den er.
Kopiér koden herunder for at lave dit Excel VBA -modul og foretage ændringer efter dine krav. Jeg har forklaret hver linje i koden herunder:
Sub ADO_Connection () 'Oprettelse af objekter i forbindelse og recordset Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String 'Erklæring om fuldt kvalificeret databasens navn. Skift det med din databases placering og navn. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" 'Dette er forbindelsesudbyderen. Husk dette til dit interview. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Dette er den forbindelsesstreng, du skal bruge, når du åbner forbindelsen. connString = "Provider =" & PRVD & "Data Source =" & DBPATH 'åbner forbindelsen conn.Open connString 'den forespørgsel, jeg vil køre på databasen. forespørgsel = "SELECT * fra customerT;" 'kører forespørgslen på den åbne forbindelse. Det vil få alle data i rec objekt. rec.Open forespørgsel, forbind 'rydder indholdet af cellerne Celler. Ryd Indhold 'at hente data fra eventuelt rekordsæt og udskrive dem i kolonne A i excelark. 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 'lukke forbindelserne rec.Close conn.Close End Sub
Kopiér ovenstående kode, eller download filen herunder, og foretag ændringer i filen, så den passer til dine krav.
Download fil: VBA Database LearningNår du kører denne VBA -kode, opretter Excel en forbindelse til databasen. Bagefter kører den designet forespørgsel. Det sletter alt gammelt indhold på arket og fylder kolonnen A med værdierne for felt 1 (andet felt) i databasen.
Hvordan fungerer denne VBA Access Database Connection?
Dim conn As New Connection, rec As New Recordset
I ovenstående linje erklærer vi ikke bare forbindelsen og recordset -variablerne, men initialiserer den direkte ved hjælp af det nye søgeord.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Disse to linjer er deltagere. DBPATH ændres kun med din database. PRVD forbinder OLE DB -udbyder.
conn.Open connString
Denne linje åbner forbindelsen til databasen. Åben er forbindelsesobjektets funktion, der tager flere argumenter. Det første og nødvendige argument er ConnectingString. Denne streng indeholder OLE DB -udbyderen (her PRVD) og datakilden (her DBPATH). Det kan også tage admin og adgangskode som valgfrie argumenter for beskyttede databaser.
Syntaksen for Connection.Open er:
forbindelse. åben ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long = -1])
Da jeg ikke har noget id og adgangskode på min database, bruger jeg kun ConnectionString. ConnectionStrings format er "Provider =provider_you ønsker at bruge; Datakilde =fuldt kvalificeret database navn". Vi lavede og gemte denne streng ikonnstreng variabel.
forespørgsel = "SELECT * fra customerT;"
Dette er den forespørgsel, jeg vil køre på databasen. Du kan have enhver forespørgsel, du ønsker.
rec.Open forespørgsel, forbind
Denne sætning kører den definerede forespørgsel i den definerede forbindelse. Her bruger vi Open -metoden til recordset -objekt. Alt output gemmes i recordset -objektetrec. Du kan hente manipulere eller slette værdier fra recordset -objektet.
Celler. Ryd Indhold
Denne linje rydder indholdet af arket. Med andre ord, sletter alt fra arkets celler.
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
Ovenstående sæt linjer kontrollerer, om rekordsættet er tomt eller ej. Hvis rekordsættet ikke er tomt (det betyder, at forespørgsel returnerede nogle poster), begynder løkken og begynder at udskrive hver værdi i felt 1 (andet felt, fornavn i dette tilfælde) i den sidste ubrugte celle i kolonnen.
(Dette bruges bare til at forklare. Du har muligvis ikke disse linjer. Hvis du bare vil åbne en forbindelse til databasen, er VBA -koden over disse linjer nok.)
Vi har brugt rec.EOF til at køre loop indtil slutningen af rekordsættet. Rec.MoveNext bruges til at gå op til det næste rekordsæt. rec.Fields (1) bruges til at hente værdier fra felt 1 (hvilket er det andet, da dets feltindeksering starter fra 0. I min database er det andet felt kundens fornavn).
rec.Close forbind. tæt
Endelig, når alt det arbejde, vi ønskede fra rec og conn, er udført, lukker vi dem.
Du kan have disse linjer i separat underrutine, hvis du gerne vil åbne og lukke bestemte forbindelser separat.
Så ja fyre, sådan opretter du en forbindelse til ACCESS -databasen ved hjælp af ADO. Der er også andre metoder, men dette er den nemmeste måde at oprette forbindelse til en datakilde for adgang via VBA. Jeg har forklaret det så detaljeret som jeg kan. Lad mig vide, om dette var nyttigt i kommentarfeltet herunder.
Relaterede artikler:
Brug en lukket projektmappe som en database (DAO) ved hjælp af VBA i Microsoft Excel | Hvis du vil bruge en lukket projektmappe som en database med DAO -forbindelse, skal du bruge dette VBA -kodestykke i Excel.
Brug en lukket projektmappe som en database (ADO) ved hjælp af VBA i Microsoft Excel | Hvis du vil bruge en lukket projektmappe som en database med ADO -forbindelse, skal du bruge dette VBA -kodestykke i Excel.
Kom godt i gang med Excel VBA UserForms | For at indsætte data i databasen bruger vi formularer. Excel UserForms er nyttige til at få oplysninger fra brugeren. Sådan skal du starte med VBA -brugerformer.
Rediger værdien/indholdet af flere UserForm-kontroller ved hjælp af VBA i Excel | Brug dette enkle VBA -uddrag til at ændre indholdet i brugerformkontrolelementerne.
Undgå, at en brugerformular lukker, når brugeren klikker på x-knappen ved hjælp af VBA i Excel | For at forhindre brugerformularen i at lukke, når brugeren klikker på x -knappen i formularen, bruger vi UserForm_QueryClose -hændelse.
Populære artikler:
50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
VLOOKUP -funktionen i Excel | Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
COUNTIF i Excel 2016 | Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle specifik værdi. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.