Access -databasen er et relasjonsbasert databasesystem som effektivt lagrer en stor mengde data på en organisert måte. Der Excel er et kraftig verktøy for å knuse data til meningsfull informasjon. Imidlertid kan Excel ikke lagre for mye data. Men når vi bruker Excel og Access sammen, øker kraften til disse verktøyene eksponentielt. Så la oss lære hvordan du kobler Access -databasen som en datakilde til Excel via VBA.
Koble til Access Database som datakilde Excel
1: Legg til referanse til AcitveX Data Object
Vi bruker ADO for å koble til tilgang til databasen. Så først må vi legge til referansen til ADO -objektet.
Legg til en modul i ditt VBA -prosjekt og klikk på verktøyene. Klikk her på referansene.
Se nå etter Microsoft ActiveX Data Object Library. Sjekk den siste versjonen du har. Jeg har 6.1. Klikk OK -knappen og det er gjort. Nå er vi klare til å lage en lenke til Access Database.
2. Skriv en VBA -kode for å opprette en forbindelse til tilgangsdatabasen
For å koble Excel til en Access -database må du ha en Access -database. Databasen min heter "Test Database.accdb ". Den er lagret kl "C: \ Users \ Manish Singh \ Desktop" plassering. Disse to variablene er viktige. Du må endre dem i henhold til dine behov. Restkoden kan beholdes som den er.
Kopier koden nedenfor for å lage Excel VBA -modulen og gjøre endringer i henhold til dine krav. Jeg har forklart hver linje i koden nedenfor:
Sub ADO_Connection () 'Opprette objekter for tilkobling og postsett Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String 'Deklarere fullt kvalifisert navn på databasen. Endre den med databasens plassering og navn. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" 'Dette er tilkoblingsleverandøren. Husk dette for intervjuet ditt. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Dette er tilkoblingsstrengen du trenger når du åpner tilkoblingen. connString = "Provider =" & PRVD & "Data Source =" & DBPATH 'åpner tilkoblingen conn.Open connString 'spørringen jeg vil kjøre på databasen. query = "SELECT * fra customerT;" 'kjører spørringen på den åpne tilkoblingen. Det vil få alle dataene i rec gjenstand. rec.Open forespørsel, konn 'rydder innholdet i cellene Cells.ClearContents 'hente data fra rekordsettet hvis det er noen og skrive dem ut i kolonne A i excel -ark. 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 'stenge forbindelsene rec.Close conn.Close End Sub
Kopier koden ovenfor eller last ned filen nedenfor og gjør endringer i filen som passer dine behov.
Last ned fil: VBA Database LearningNår du kjører denne VBA -koden, oppretter Excel en forbindelse til databasen. Etterpå kjører den designet spørringen. Det sletter alt gammelt innhold på arket og fyller kolonnen A med verdiene for felt 1 (andre felt) i databasen.
Hvordan fungerer denne VBA Access Database Connection?
Dim conn As New Connection, rec As New Recordset
På linjen ovenfor erklærer vi ikke bare tilkoblings- og postsettvariablene, men initialiserer det direkte ved hjelp av det nye søkeordet.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Disse to linjene er deltakere. DBPATH endres bare med databasen din. PRVD kobler til OLE DB -leverandøren.
conn.Open connString
Denne linjen åpner tilkoblingen til databasen. Åpen er funksjonen til tilkoblingsobjektet som tar flere argumenter. Det første og nødvendige argumentet er ConnectingString. Denne strengen inneholder OLE DB -leverandøren (her PRVD) og datakilden (her DBPATH). Det kan også ta admin og passord som valgfrie argumenter for beskyttede databaser.
Syntaksen til Connection.Open er:
tilkobling. åpne ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long = -1])
Siden jeg ikke har noen ID og passord på databasen min, bruker jeg bare ConnectionString. ConnectionString -formatet er "Provider =provider_you want to use; Datakilde =fullt kvalifisert navn på databasen". Vi lagde og lagret denne strengenconnString variabel.
query = "SELECT * fra customerT;"
Dette er spørringen jeg vil kjøre på databasen. Du kan ha spørsmål du ønsker.
rec.Open forespørsel, konn
Denne setningen kjører den definerte spørringen i den definerte tilkoblingen. Her bruker vi Open -metoden for rekordsettobjekt. All utdata lagres i rekordsettobjektetrec. Du kan hente manipulere eller slette verdier fra rekordsettobjektet.
Cells.ClearContents
Denne linjen fjerner innholdet i arket. Med andre ord, sletter alt fra cellene i arket.
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 sett med linjer sjekker om platesettet er tomt eller ikke. Hvis rekordsettet ikke er tomt (det betyr at spørringen returnerte noen poster) begynner sløyfen og begynner å skrive ut hver verdi i felt 1 (andre felt, fornavn i dette tilfellet) i den siste ubrukte cellen i kolonnen.
(Dette brukes bare for å forklare. Du har kanskje ikke disse linjene. Hvis du bare vil åpne en tilkobling til databasen, er VBA -koden over disse linjene nok.)
Vi har brukt rec.EOF for å kjøre sløyfen til slutten av rekordsettet. Rec.MoveNext brukes til å gå opp til neste rekordsett. rec.Fields (1) brukes til å hente verdier fra felt 1 (som er andre siden feltindekseringen starter fra 0. I min database er det andre feltet kundens fornavn).
rec.Close conn.Close
Til slutt, når alt arbeidet vi ønsket fra rec og conn er ferdig, lukker vi dem.
Du kan ha disse linjene i separat underrutine hvis du liker å åpne og lukke bestemte tilkoblinger separat.
Så ja gutta, dette er hvordan du oppretter en forbindelse til ACCESS -databasen ved hjelp av ADO. Det er også andre metoder, men dette er den enkleste måten å koble til en datakilde for tilgang via VBA. Jeg har forklart det så detaljert som jeg kan. Gi meg beskjed om dette var nyttig i kommentarfeltet nedenfor.
Relaterte artikler:
Bruk en lukket arbeidsbok som en database (DAO) ved hjelp av VBA i Microsoft Excel | Hvis du vil bruke en lukket arbeidsbok som en database med DAO -tilkobling, bruker du denne VBA -kodebiten i Excel.
Bruk en lukket arbeidsbok som en database (ADO) ved hjelp av VBA i Microsoft Excel | Hvis du vil bruke en lukket arbeidsbok som en database med ADO -tilkobling, bruker du denne VBA -kodebiten i Excel.
Komme i gang med Excel VBA UserForms | For å sette inn data i databasen bruker vi skjemaer. Excel UserForms er nyttige for å få informasjon fra brukeren. Slik bør du starte med VBA -brukerformer.
Endre verdien/innholdet i flere UserForm-kontroller ved hjelp av VBA i Excel | Bruk denne enkle VBA -kodebiten for å endre innholdet i brukerformkontrollene.
Forhindre at et brukerskjema lukkes når brukeren klikker på x-knappen ved å bruke VBA i Excel | For å forhindre at brukerskjemaet lukkes når brukeren klikker på x -knappen i skjemaet, bruker vi UserForm_QueryClose -hendelsen.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
VLOOKUP -funksjonen i Excel | Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
COUNTIF i Excel 2016 | Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikk verdi. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.