Access baza podataka je relacijski sustav upravljanja bazom podataka koji učinkovito sprema veliku količinu podataka na organiziran način. Gdje je Excel moćan alat za skupljanje podataka u smislene informacije. Međutim, Excel ne može pohraniti previše podataka. No kad zajedno koristimo Excel i Access, moć ovih alata eksponencijalno se povećava. Dakle, naučimo kako povezati Access bazu podataka kao izvor podataka s Excelom putem VBA.
Povezivanje baze podataka Access kao izvora podataka Excel
1: Dodajte referencu na AcitveX podatkovni objekt
Za pristup bazi podataka koristit ćemo ADO. Dakle, prvo moramo dodati referencu na ADO objekt.
Dodajte modul svom VBA projektu i kliknite na alate. Ovdje kliknite na reference.
Sada potražite biblioteku podatkovnih objekata Microsoft ActiveX. Provjerite najnoviju verziju koju imate. Ja imam 6.1. Pritisnite gumb OK i sve je gotovo. Sada smo spremni za stvaranje veze na Access Database.
2. Napišite VBA kôd za uspostavu veze s Access bazom podataka
Za povezivanje programa Excel s bazom podataka programa Access morate imati bazu podataka programa Access. Moja baza podataka je "Test Database.accdb ". Sprema se na "C: \ Users \ Manish Singh \ Desktop" mjesto. Ove dvije varijable su važne. Morat ćete ih promijeniti prema svojim potrebama. Kôd za odmor može se zadržati takav kakav jest.
Kopirajte donji kôd da biste napravili Excel VBA modul i unijeli izmjene prema vašim zahtjevima. Objasnio sam svaki redak koda u nastavku:
Pod ADO_Connection () 'Stvaranje objekata Connection and Recordset Dim conn kao nova veza, zapiši kao novi skup zapisa Dim DBPATH, PRVD, connString, upit kao niz 'Deklariranje potpuno kvalificiranog imena baze podataka. Promijenite ga prema lokaciji i imenu baze podataka. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" 'Ovo je davatelj veze. Zapamtite ovo za svoj intervju. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Ovo je niz veze koji će vam trebati prilikom otvaranja veze. connString = "Pružatelj =" & PRVD & "Izvor podataka =" & DBPATH 'otvaranje veze conn.Otvori connString 'upit koji želim pokrenuti u bazi podataka. query = "SELECT * from customerT;" 'pokretanje upita na otvorenoj vezi. Dobit će sve podatke u rec objekt. rec.Open upit, spoj 'čišćenje sadržaja stanica Ćelije.Čisti sadržaj 'dobivanje podataka iz skupa zapisa ako ih ima i ispis u stupcu A Excelove tablice. Ako (rec.RecordCount 0) Tada radite dok nije rec.EOF Raspon ("A" & Ćelije (Rows.Count, 1) .End (xlUp) .Row) .Offset (1, 0) .Value2 = _ rec.Fields (1) .Vrijednost rec.MoveNext Loop End If 'zatvaranje veza rec.Close conn.Close End Sub
Kopirajte gornji kôd ili preuzmite donju datoteku i unesite izmjene u datoteku tako da odgovaraju vašim zahtjevima.
Preuzmite datoteku: VBA Database LearningKada pokrenete ovaj VBA kôd, Excel će uspostaviti vezu s bazom podataka. Nakon toga će pokrenuti osmišljeni upit. Izbrisat će sve stare sadržaje na listu i ispunit će stupac A vrijednostima polja 1 (drugo polje) baze podataka.
Kako funkcionira ova VBA Access Database Connection veza?
Zatamni kao novu vezu, zapiši kao novi skup zapisa
U gornjem retku ne samo da deklariramo varijable Connection i recordset, već ih inicijaliziramo izravno pomoću ključne riječi New.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Ove dvije linije su natjecatelji. DBPATH će se promijeniti samo s vašom bazom podataka. PRVD povezuje pružatelja usluga OLE DB.
conn.Otvori connString
Ova linija otvara vezu s bazom podataka. Otvorena je funkcija objekta veze koja uzima nekoliko argumenata. Prvi i neophodni argument je ConnectingString. Ovaj niz sadrži davatelja OLE DB (ovdje PRVD) i izvor podataka (ovdje DBPATH). Također može uzeti administratora i lozinku kao izborne argumente za zaštićene baze podataka.
Sintaksa Connection.Open je:
connection.open ([ConnectionString kao niz], [Korisnički ID kao niz], [Lozinka kao niz], [Opcije kao dugačak = -1])
Budući da u svojoj bazi podataka nemam nikakav ID i lozinku, koristim samo ConnectionString. Format ConnectionString je "Provider =pružatelj usluga_koje želite koristiti; Izvor podataka =potpuno kvalificirano ime baze podataka". Napravili smo i spremili ovaj niz uconnString promjenjivo.
query = "SELECT * from customerT;"
Ovo je upit koji želim pokrenuti u bazi podataka. Možete imati bilo koji upit koji želite.
rec.Open upit, spoj
Ova naredba izvodi definirani upit u definiranoj vezi. Ovdje koristimo metodu Open objekta zapisa zapisa. Sav izlaz sprema se u objekt zapisa zapisarec. Možete dohvatiti manipulirane ili izbrisane vrijednosti iz objekta skupa zapisa.
Ćelije.Čisti sadržaj
Ovaj redak briše sadržaj lista. Drugim riječima, briše se sve iz ćelija lista.
Ako (rec.RecordCount 0) Tada radite dok nije rec.EOF Raspon ("A" & Ćelije (Rows.Count, 1) .End (xlUp) .Row) .Offset (1, 0) .Value2 = _ rec.Fields (1) .Vrijednost rec.MoveNext Loop End If
Gornji skup linija provjerava je li skup zapisa prazan ili nije. Ako skup zapisa nije prazan (to znači da je upit vratio neke zapise), petlja počinje i počinje ispisivati svaku vrijednost polja 1 (drugo polje, ime u ovom slučaju) u posljednjoj neiskorištenoj ćeliji u stupcu.
(Ovo se koristi samo za objašnjenje. Možda nemate ove retke. Ako samo želite otvoriti vezu s bazom podataka, tada je dovoljan VBA kôd iznad ovih redaka.)
Koristili smo rec.EOF za pokretanje petlje do kraja skupa zapisa. Rec.MoveNext koristi se za prelazak na sljedeći skup zapisa. rec.Fields (1) se koristi za dobivanje vrijednosti iz polja 1 (koje je drugo jer njegovo indeksiranje polja počinje od 0. U mojoj bazi podataka drugo polje je ime klijenta).
rec.Zatvori zatvori.Zatvori
Konačno, kad se završi sav posao koji smo htjeli od rec i conna, zatvaramo ih.
Ove linije možete imati u zasebnoj potprogrami ako želite zasebno otvarati i zatvarati određene veze.
Pa da, dečki, ovako ćete uspostaviti vezu s bazom podataka ACCESS pomoću ADO -a. Postoje i druge metode, ali ovo je najjednostavniji način povezivanja s izvorom podataka za pristup putem VBA. Objasnio sam to što detaljnije mogu. Javite mi je li vam ovo pomoglo u donjem odjeljku komentara.
Povezani članci:
Koristite zatvorenu radnu knjigu kao bazu podataka (DAO) koristeći VBA u programu Microsoft Excel | Za korištenje zatvorene radne knjige kao baze podataka s DAO vezom upotrijebite ovaj isječak VBA u Excelu.
Koristite zatvorenu radnu knjigu kao bazu podataka (ADO) koristeći VBA u programu Microsoft Excel | Za korištenje zatvorene radne knjige kao baze podataka s ADO vezom upotrijebite ovaj isječak VBA u Excelu.
Početak korištenja Excel VBA korisničkih obrazaca | Za umetanje podataka u bazu podataka koristimo obrasce. Korisnički obrasci programa Excel korisni su za dobivanje informacija od korisnika. Evo kako biste trebali početi s VBA korisničkim oblicima.
Promijenite vrijednost/sadržaj nekoliko kontrola UserForm pomoću VBA u Excelu | Za promjenu sadržaja kontrola korisničkog oblika upotrijebite ovaj jednostavan isječak VBA.
Spriječite zatvaranje korisničkog oblika kada korisnik klikne gumb x pomoću VBA u Excelu | Da bismo spriječili zatvaranje korisničkog oblika kada korisnik klikne gumb x obrasca, koristimo događaj UserForm_QueryClose.
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Funkcija VLOOKUP u Excelu | Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
COUNTIF u Excelu 2016 | Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste računali određenu vrijednost. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu | Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.