Accessova zbirka podatkov je relacijski sistem za upravljanje baz podatkov, ki učinkovito organizirano shrani veliko količino podatkov. Kjer je Excel močno orodje za združevanje podatkov v pomembne informacije. Vendar Excel ne more shraniti preveč podatkov. Ko pa uporabljamo Excel in Access skupaj, se moč teh orodij eksponentno poveča. Zato se naučimo, kako povezati bazo podatkov Access kot vir podatkov z Excelom prek VBA.
Povezovanje zbirke podatkov Access kot podatkovnega vira Excel
1: Dodajte sklic na podatkovni predmet AcitveX
Za dostop do baze podatkov bomo uporabljali ADO. Zato moramo najprej dodati sklic na objekt ADO.
V svoj projekt VBA dodajte modul in kliknite na orodja. Tukaj kliknite na reference.
Zdaj poiščite knjižnico podatkovnih objektov Microsoft ActiveX. Preverite najnovejšo različico, ki jo imate. Imam 6.1. Kliknite gumb V redu in končano. Zdaj smo pripravljeni ustvariti povezavo do zbirke podatkov Access.
2. Napišite kodo VBA, da vzpostavite povezavo z zbirko podatkov Access
Če želite Excel povezati z zbirko podatkov programa Access, morate imeti zbirko podatkov programa Access. Ime moje baze podatkov je "Test Database.accdb ". Shranjeno je na "C: \ Users \ Manish Singh \ Desktop" lokacijo. Ti dve spremenljivki sta pomembni. Spremeniti jih boste morali glede na vaše potrebe. Kodo za počitek lahko ohranite takšno, kot je.
Kopirajte spodnjo kodo, da naredite svoj Excel VBA modul in naredite spremembe glede na vaše zahteve. Pojasnil sem vsako vrstico kode spodaj:
Pod ADO_Connection () 'Ustvarjanje objektov Connection and Recordset Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, poizvedi kot niz „Razglasitev polno kvalificiranega imena zbirke podatkov. Spremenite ga z lokacijo in imenom baze podatkov. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" 'To je ponudnik povezav. Zapomnite si to za svoj intervju. PRVD = "Microsoft.ace.OLEDB.12.0;" 'To je niz povezave, ki ga boste potrebovali pri odpiranju povezave. connString = "Ponudnik =" & PRVD & "Vir podatkov =" & DBPATH 'odpiranje povezave conn.Open connString "poizvedbo, ki jo želim zagnati v bazi podatkov. query = "SELECT * from customerT;" 'izvajanje poizvedbe na odprti povezavi. Prejel bo vse podatke v rec predmet. rec.odprta poizvedba, povez "čiščenje vsebine celic Celice.ClearContents „pridobivanje podatkov iz zapisa zapisov, če obstajajo, in njihovo tiskanje v stolpec A lista Excel. Če (rec.RecordCount 0) Potem naredi medtem ko ni (1) .Vrednost rec.MoveNext Loop End If 'zapiranje povezav rec.Zaprite povez.Zapri konec Sub
Kopirajte zgornjo kodo ali prenesite spodnjo datoteko in datoteko spremenite, da bo ustrezala vašim zahtevam.
Prenesite datoteko: VBA Database LearningKo zaženete to kodo VBA, bo Excel vzpostavil povezavo z bazo podatkov. Nato bo zagnalo oblikovano poizvedbo. Počistila bo vso staro vsebino na listu in napolnila stolpec A z vrednostmi polja 1 (drugo polje) baze podatkov.
Kako deluje ta VBA Access Database Connection?
Dim conn As New Connection, rec As New Recordset
V zgornji vrstici ne samo razglasimo spremenljivk Connection in recordset, ampak jo inicializiramo neposredno s ključno besedo New.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Ti dve vrstici sta tekmovalca. DBPATH se bo spremenil samo z vašo bazo podatkov. PRVD povezuje ponudnika OLE DB.
conn.Open connString
Ta vrstica odpre povezavo z bazo podatkov. Odpri je funkcija povezovalnega objekta, ki sprejme več argumentov. Prvi in nujni argument je ConnectingString. Ta niz vsebuje ponudnika OLE DB (tukaj PRVD) in vir podatkov (tukaj DBPATH). Prav tako lahko vzame skrbnika in geslo kot izbirna argumenta za zaščitene baze podatkov.
Sintaksa Connection.Open je:
connection.open ([ConnectionString kot niz], [ID uporabnika kot niz], [Geslo kot niz], [Možnosti kot dolgo = -1])
Ker v svoji bazi podatkov nimam nobenega ID -ja in gesla, uporabljam samo ConnectionString. Oblika ConnectionString je "Provider =ponudnik_, ki ga želite uporabiti; Vir podatkov =polno ime baze podatkov". Ta niz smo ustvarili in shranili vconnString spremenljivka.
query = "SELECT * from customerT;"
To je poizvedba, ki jo želim zagnati v bazi podatkov. Imate lahko kakršna koli vprašanja, ki jih želite.
rec.odprta poizvedba, povez
Ta stavek izvaja definirano poizvedbo v definirani povezavi. Tukaj uporabljamo odprto metodo predmeta zapisa zapisa. Vsi izhodi so shranjeni v objektu zapisa zapisarec. Vrednosti iz predmeta nabora zapisov lahko prikličete ali izbrišete.
Celice.ClearContents
Ta vrstica počisti vsebino lista. Z drugimi besedami, izbriše vse iz celic lista.
Če (rec.RecordCount 0) Potem naredi medtem ko ni (1) .Vrednost rec.MoveNext Loop End If
Zgornji niz vrstic preveri, ali je niz zapisov prazen ali ne. Če nabor zapisov ni prazen (to pomeni, da je poizvedba vrnila nekaj zapisov), se zanka začne in natisne vsako vrednost polja 1 (drugo polje, v tem primeru ime) v zadnji neuporabljeni celici v stolpcu.
(To se uporabi samo za pojasnitev. Morda nimate teh vrstic. Če želite samo odpreti povezavo z bazo podatkov, je dovolj koda VBA nad temi vrsticami.)
Za izvajanje zanke do konca nabora zapisov smo uporabili rec.EOF. Rec.MoveNext se uporablja za prehod na naslednji niz zapisov. rec.Fields (1) se uporablja za pridobivanje vrednosti iz polja 1 (ki je drugo, saj se njegovo indeksiranje pol začne od 0. V moji zbirki podatkov je drugo polje ime stranke).
rec.Zapri zapri.Zapri
Ko končamo vse delo, ki smo ga želeli od rec in conn, jih zapremo.
Morda imate te vrstice v ločeni podprogramu, če želite ločeno odpirati in zapirati določene povezave.
Torej, fantje, tako vzpostavite povezavo z bazo podatkov ACCESS z uporabo ADO. Obstajajo tudi druge metode, vendar je to najlažji način za povezavo z virom podatkov za dostop prek VBA. Razložil sem ga čim podrobneje. Sporočite mi, če vam je to pomagalo v spodnjem razdelku s komentarji.
Povezani članki:
Uporabite zaprt delovni zvezek kot bazo podatkov (DAO) z uporabo VBA v programu Microsoft Excel | Če želite uporabiti zaprt delovni zvezek kot zbirko podatkov s povezavo DAO, uporabite ta delček VBA v Excelu.
Uporabite zaprt delovni zvezek kot bazo podatkov (ADO) z uporabo VBA v Microsoft Excelu | Če želite uporabiti zaprt delovni zvezek kot zbirko podatkov s povezavo ADO, uporabite ta delček VBA v Excelu.
Uvod v uporabniške obrazce programa Excel VBA | Za vstavljanje podatkov v bazo podatkov uporabljamo obrazce. Uporabniški obrazci programa Excel so uporabni za pridobivanje informacij od uporabnika. Tukaj je, kako morate začeti z uporabniškimi oblikami VBA.
Spremenite vrednost/vsebino več kontrolnikov UserForm z uporabo VBA v Excelu | Če želite spremeniti vsebino kontrolnikov oblike uporabnika, uporabite ta preprost delček VBA.
Preprečite, da se uporabniška oblika zapre, ko uporabnik klikne gumb x z uporabo VBA v Excelu | Da preprečimo, da se uporabniški obrazec zapre, ko uporabnik klikne gumb x obrazca, uporabimo dogodek UserForm_QueryClose.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Funkcija VLOOKUP v Excelu | To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
COUNTIF v Excelu 2016 | Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določene vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.