Databáza Access je systém správy relačných databáz, ktorý efektívne organizovane ukladá veľké množstvo údajov. Kde je Excel účinný nástroj na skracovanie údajov na zmysluplné informácie. Program Excel však nemôže ukladať príliš veľa údajov. Keď však použijeme Excel a Access spoločne, sila týchto nástrojov sa exponenciálne zvyšuje. Naučme sa teda pripojiť databázu Access ako zdroj údajov k Excelu prostredníctvom VBA.
Pripojenie prístupovej databázy ako zdroja údajov Excel
1: Pridajte odkaz na dátový objekt AcitveX
Na prístup k databáze budeme používať ADO. Najprv teda musíme pridať odkaz na objekt ADO.
Pridajte modul do svojho projektu VBA a kliknite na nástroje. Tu kliknite na referencie.
Teraz vyhľadajte knižnicu údajových objektov Microsoft ActiveX. Skontrolujte najnovšiu verziu, ktorú máte. Ja mám 6.1. Kliknite na tlačidlo OK a je hotovo. Teraz sme pripravení vytvoriť prepojenie na databázu Access.
2. Napíšte kód VBA na vytvorenie pripojenia k databáze Access
Na pripojenie Excelu k databáze Accessu potrebujete databázu Accessu. Moja databáza sa volá "Test Database.accdb ". Je uložené na "C: \ Users \ Manish Singh \ Desktop" umiestnenie. Tieto dve premenné sú dôležité. Budete ich musieť zmeniť podľa svojich potrieb. Pokojový kód je možné ponechať tak, ako je.
Skopírujte nižšie uvedený kód a vytvorte si modul Excel VBA a vykonajte zmeny podľa svojich požiadaviek. Každý riadok kódu som vysvetlil nižšie:
Sub ADO_Connection () 'Vytváranie objektov Connection a Recordset Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String „Vyhlásenie plne kvalifikovaného názvu databázy. Zmeňte to pomocou umiestnenia a názvu svojej databázy. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" „Toto je poskytovateľ pripojenia. Pamätajte si to pri pohovore. PRVD = "Microsoft.ace.OLEDB.12.0;" „Toto je reťazec pripojenia, ktorý budete potrebovať pri otváraní pripojenia. connString = "Provider =" & PRVD & "Data Data =" & DBPATH “otvorenie spojenia conn.Open connString “dotaz, ktorý chcem spustiť v databáze. query = "SELECT * from customerT;" 'spustenie dotazu na otvorenom pripojení. Získa všetky údaje vo formáte rec predmet. rec.Otvorený dotaz, spoj „vymazanie obsahu buniek Bunky. Vymazať obsah „získanie údajov zo sady záznamov, ak existujú, a ich vytlačenie v stĺpci A listu programu Excel. 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) .Hodnota rec.MoveNext Koniec slučky If „zatvorenie spojení rec.Close conn.Close End Sub
Skopírujte vyššie uvedený kód alebo si stiahnite súbor nižšie a v súbore vykonajte zmeny, ktoré vyhovujú vašim požiadavkám.
Stiahnite si súbor: Učenie databázy VBAKeď spustíte tento kód VBA, Excel nadviaže pripojenie k databáze. Potom spustí navrhnutý dotaz. Vymaže všetok starý obsah v hárku a vyplní stĺpec A hodnotami poľa 1 (druhého poľa) databázy.
Ako toto pripojenie k databáze VBA Access funguje?
Dim conn As New Connection, rec As New Recordset
V uvedenom riadku nielen deklarujeme premenné Pripojenie a množina záznamov, ale priamo ich inicializujeme pomocou kľúčového slova Nové.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Tieto dva riadky sú súťažiaci. DBPATH sa zmení iba s vašou databázou. PRVD sa pripája k poskytovateľovi OLE DB.
conn.Open connString
Tento riadok otvára pripojenie k databáze. Otvorená je funkcia objektu pripojenia, ktorá vyžaduje niekoľko argumentov. Prvým a potrebným argumentom je ConnectingString. Tento reťazec obsahuje poskytovateľa OLE DB (tu PRVD) a zdroj údajov (tu DBPATH). Správca a heslo môžu tiež používať ako voliteľné argumenty pre chránené databázy.
Syntax aplikácie Connection.Open je:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long = -1])
Pretože vo svojej databáze nemám žiadne ID a heslo, používam iba ConnectionString. Formát ConnectionString je „Poskytovateľ =provider_you chcete použiť; Zdroj údajov =plne kvalifikovaný názov databázy". Tento reťazec sme vyrobili a uložili doconnString premenná.
query = "SELECT * from customerT;"
Toto je dotaz, ktorý chcem spustiť v databáze. Môžete mať akékoľvek otázky, ktoré chcete.
rec.Otvorený dotaz, spoj
Tento príkaz spustí definovaný dotaz v definovanom spojení. Tu používame otvorenú metódu objektu sady záznamov. Všetky výstupy sa uložia do objektu sady záznamovrec. Z objektu sady záznamov môžete načítať alebo manipulovať s hodnotami.
Bunky. Vymazať obsah
Tento riadok vymaže obsah hárka. Inými slovami, vymaže všetko z buniek hárka.
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) .Hodnota rec.MoveNext Koniec slučky If
Vyššie uvedená sada riadkov kontroluje, či je sada záznamov prázdna alebo nie. Ak sada záznamov nie je prázdna (to znamená, že dotaz vrátil niektoré záznamy), slučka sa začne a začne tlačiť každú hodnotu poľa 1 (druhé pole, v tomto prípade meno) v poslednej nepoužitej bunke v stĺpci.
(Používa sa to len na vysvetlenie. Tieto riadky možno nemáte. Ak chcete len otvoriť pripojenie k databáze, stačí kód VBA nad týmito riadkami.)
Na spustenie slučky až do konca sady záznamov sme použili rec.EOF. Rec.MoveNext sa používa na zvýšenie úrovne ďalšej sady záznamov. rec.Fields (1) sa používa na získanie hodnôt z poľa 1 (ktoré je druhé, pretože jeho indexovanie polí začína od 0. V mojej databáze je druhým poľom krstné meno zákazníka).
rec.Close conn.Close
Nakoniec, keď sú všetky práce, ktoré sme chceli od rec a conn, hotové, zatvoríme ich.
Tieto riadky môžete mať v samostatnom podprograme, ak chcete samostatne otvárať a zatvárať konkrétne spojenia.
Takže áno, chlapci, takto vytvoríte pripojenie k databáze ACCESS pomocou ADO. Existujú aj ďalšie metódy, ale je to najľahší spôsob, ako sa pripojiť k údajovému zdroju prístupu prostredníctvom VBA. Vysvetlil som to tak podrobne, ako som len mohol. Dajte mi vedieť, či to pomohlo v nižšie uvedenej sekcii komentárov.
Súvisiace články:
Použite uzavretý zošit ako databázu (DAO) pomocou jazyka VBA v programe Microsoft Excel | Ak chcete použiť uzavretý zošit ako databázu s pripojením DAO, použite tento útržok VBA v programe Excel.
Použite uzavretý zošit ako databázu (ADO) pomocou jazyka VBA v programe Microsoft Excel | Ak chcete použiť uzavretý zošit ako databázu s pripojením ADO, použite tento útržok VBA v programe Excel.
Začíname s UserForms programu Excel VBA | Na vkladanie údajov do databázy používame formuláre. Excel UserForms sú užitočné pri získavaní informácií od používateľa. Tu je návod, ako by ste mali začať s užívateľskými formulármi VBA.
Zmeňte hodnotu/obsah niekoľkých ovládacích prvkov UserForm pomocou jazyka VBA v programe Excel | Na zmenu obsahu používateľských formulárov použite tento jednoduchý útržok VBA.
Pomocou VBA v programe Excel zabráňte zatvoreniu používateľského formulára, keď používateľ klikne na tlačidlo x | Aby sa používateľský formulár nezatvoril, keď používateľ klikne na tlačidlo x vo formulári, použijeme udalosť UserForm_QueryClose.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Funkcia VLOOKUP v programe Excel | Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
COUNTIF v Exceli 2016 | Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnej hodnoty nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.