Access -tietokanta on relaatiotietokannan hallintajärjestelmä, joka säästää tehokkaasti suuren määrän tietoja organisoidusti. Excel on tehokas työkalu tietojen murskaamiseen merkityksellisiksi tiedoiksi. Excel ei kuitenkaan voi tallentaa liikaa dataa. Mutta kun käytämme Exceliä ja Accessia yhdessä, näiden työkalujen teho kasvaa eksponentiaalisesti. Opetellaan siis, miten Access -tietokanta liitetään tietolähteeksi Exceliin VBA: n kautta.
Access -tietokannan yhdistäminen tietolähteeksi Excel
1: Lisää viite AcitveX -tietoobjektiin
Käytämme ADO: ta muodostaessamme yhteyden tietokantaan. Joten ensin meidän on lisättävä viittaus ADO -objektiin.
Lisää moduuli VBA -projektiisi ja napsauta työkaluja. Klikkaa tästä viittauksia.
Etsi nyt Microsoft ActiveX Data Object Library. Tarkista uusin versio. Mulla on 6.1. Napsauta OK -painiketta ja se on valmis. Nyt olemme valmiit luomaan linkin Access -tietokantaan.
2. Kirjoita VBA -koodi yhteyden muodostamiseksi Access -tietokantaan
Jos haluat yhdistää Excelin Access -tietokantaan, sinulla on oltava Access -tietokanta. Tietokantani nimi on "Testaa tietokanta.accdb ". Se tallennetaan osoitteeseen "C: \ Käyttäjät \ Manish Singh \ Desktop" sijainti. Nämä kaksi muuttujaa ovat tärkeitä. Sinun on muutettava ne tarpeidesi mukaan. Lepokoodi voidaan pitää sellaisenaan.
Kopioi alla oleva koodi Excel VBA -moduulin luomiseen ja muutosten tekemiseen tarpeidesi mukaan. Olen selittänyt alla olevan koodin jokaisen rivin:
Osa ADO_Connection () 'Yhteyden ja tietuejoukon objektien luominen Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String '' Tietokannan täysin pätevän nimen ilmoittaminen. Muuta tietokannan sijainti ja nimi. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" 'Tämä on yhteyden tarjoaja. Muista tämä haastattelussa. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Tämä on yhteysmerkkijono, jota tarvitset yhteyden avaamisen yhteydessä. connString = "Provider =" & PRVD & "Tietolähde =" & DBPATH 'avaa yhteyden conn. open connString 'kyselyn, jonka haluan suorittaa tietokannassa. query = "SELECT * from customerT;" 'suorittaa kyselyn avoimessa yhteydessä. Se saa kaikki tiedot rec esine. avoin kysely, yhdistä 'tyhjentää solujen sisällön Solut, selkeä sisältö 'tietojen hankkiminen mahdollisesta tietuejoukosta ja tulostaminen Excel -taulukon sarakkeeseen A. Jos (rec.RecordCount 0) Tee sitten kun ei Rec.EOF -alue ("A" & Solut (Rows.Count, 1) .End (xlUp) .Row) .Offset (1, 0) .Arvo2 = _ rec.Fields (1). Arvo rec.MoveNext Loop End If 'yhteyksien sulkeminen sulje liitäntä Sulje pää Sub
Kopioi yllä oleva koodi tai lataa alla oleva tiedosto ja tee tiedostoon muutoksia tarpeidesi mukaan.
Lataa tiedosto: VBA Database LearningKun suoritat tämän VBA -koodin, Excel muodostaa yhteyden tietokantaan. Myöhemmin se suorittaa suunnitellun kyselyn. Se tyhjentää arkin vanhan sisällön ja täyttää sarakkeen A tietokannan kentän 1 (toinen kenttä) arvoilla.
Miten tämä VBA Access Database Connection toimii?
Dim conn As New Connection, rec As New Recordset
Yllä olevalla rivillä emme vain ilmoita yhteys- ja tietuejoukon muuttujia, vaan alustat sen suoraan käyttämällä uutta avainsanaa.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Nämä kaksi linjaa ovat kilpailijoita. DBPATH muuttuu vain tietokannasi mukaan. PRVD yhdistää OLE DB -palveluntarjoajan.
conn. open connString
Tämä rivi avaa yhteyden tietokantaan. Open on yhteysobjektin toiminto, joka ottaa useita argumentteja. Ensimmäinen ja välttämätön argumentti on ConnectingString. Tämä merkkijono sisältää OLE DB -palveluntarjoajan (tässä PRVD) ja tietolähteen (tässä DBPATH). Se voi myös ottaa järjestelmänvalvojan ja salasanan valinnaisiksi argumentteiksi suojatuille tietokannoille.
Yhteyden syntaksi.Open on:
connection.open ([Yhteysmerkkijono merkkijonona], [Käyttäjätunnus merkkijonona], [Salasana merkkijonona], [Asetukset pitkä = -1])
Koska tietokannassani ei ole tunnusta ja salasanaa, käytän vain ConnectionString -järjestelmää. ConnectionString -muoto on "Provider =palveluntarjoaja_ haluat käyttää; Tietolähde =tietokannan täysin pätevä nimi"Teimme ja tallensimme tämän merkkijononconnString muuttuja.
query = "SELECT * from customerT;"
Tämä on kysely, jonka haluan suorittaa tietokannassa. Sinulla voi olla mitä tahansa kysymyksiä.
avoin kysely, yhdistä
Tämä lause suorittaa määritetyn kyselyn määritetyssä yhteydessä. Tässä käytämme tietuejoukko -objektin Open -menetelmää. Kaikki tulosteet tallennetaan tietueobjektiinrec. Voit hakea muokata tai poistaa arvoja tietuejoukko -objektista.
Solut, selkeä sisältö
Tämä rivi tyhjentää taulukon sisällön. Toisin sanoen, poistaa kaiken taulukon soluista.
Jos (rec.RecordCount 0) Tee sitten kun ei Rec.EOF -alue ("A" & Solut (Rows.Count, 1) .End (xlUp) .Row) .Offset (1, 0) .Arvo2 = _ rec.Fields (1). Arvo rec.MoveNext Loop End If
Yllä olevat rivit tarkistavat, onko tietue tyhjä vai ei. Jos tietuejoukko ei ole tyhjä (se tarkoittaa, että kysely palautti joitakin tietueita), silmukka alkaa ja alkaa tulostaa jokaisen kentän 1 arvon (toinen kenttä, etunimi tässä tapauksessa) sarakkeen viimeiseen käyttämättömään soluun.
(Tätä käytetään vain selittämään. Sinulla ei ehkä ole näitä rivejä. Jos haluat vain avata yhteyden tietokantaan, näiden rivien yläpuolella oleva VBA -koodi riittää.)
Olemme käyttäneet rec.EOF -ohjelmaa suorittamaan silmukan tietuejoukon loppuun asti. Rec.MoveNext -toiminnolla siirrytään seuraavaan tietuejoukkoon. rec.Fields (1) käytetään arvojen saamiseen kentästä 1 (joka on toinen, koska sen kenttäindeksointi alkaa nollasta. Tietokannassani toinen kenttä on asiakkaan etunimi).
sulje liitäntä sulje
Lopuksi, kun kaikki työ, jonka halusimme rec ja connilta, on tehty, suljemme ne.
Nämä rivit voivat olla erillisessä aliohjelmassa, jos haluat avata ja sulkea tiettyjä yhteyksiä erikseen.
Joten kyllä kaverit, näin muodostat yhteyden ACCESS -tietokantaan ADO: n avulla. On myös muita menetelmiä, mutta tämä on helpoin tapa muodostaa yhteys tietolähteeseen VBA: n kautta. Olen selittänyt sen mahdollisimman yksityiskohtaisesti. Kerro minulle, jos tämä oli hyödyllistä alla olevassa kommenttiosassa.
Aiheeseen liittyvät artikkelit:
Käytä suljettua työkirjaa tietokannana (DAO) käyttämällä VBA: ta Microsoft Excelissä | Jos haluat käyttää suljettua työkirjaa tietokantana DAO -yhteydellä, käytä tätä VBA -katkelmaa Excelissä.
Käytä suljettua työkirjaa tietokannana (ADO) käyttämällä VBA: ta Microsoft Excelissä | Jos haluat käyttää suljettua työkirjaa tietokantana ADO -yhteydellä, käytä tätä VBA -katkelmaa Excelissä.
Excel VBA UserForms -ohjelman käytön aloittaminen | Tietojen lisäämiseksi tietokantaan käytämme lomakkeita. Excelin käyttäjämuodot ovat hyödyllisiä tietojen saamiseksi käyttäjältä. Näin voit aloittaa VBA -käyttäjämuodoilla.
Muuta useiden UserForm-ohjausobjektien arvoa/sisältöä VBA: n avulla Excelissä | Voit muuttaa käyttäjämuodon ohjaimien sisältöä käyttämällä tätä yksinkertaista VBA -katkelmaa.
Estä käyttäjä lomakkeen sulkeminen, kun käyttäjä napsauttaa x-painiketta käyttämällä VBA: ta Excelissä | Käytämme UserForm_QueryClose -tapahtumaa, jotta käyttäjälomake ei sulkeutuisi, kun käyttäjä napsauttaa lomakkeen x -painiketta.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi | Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
VLOOKUP -toiminto Excelissä | Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja arkeilta.
COUNTIF Excel 2016: ssa | Laske arvot olosuhteilla käyttämällä tätä hämmästyttävää toimintoa. Sinun ei tarvitse suodattaa tietojasi tietyn arvon laskemiseksi. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä | Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.