Accessová databáze je systém pro správu relační databáze, který efektivně organizovaně ukládá velké množství dat. Kde je Excel účinný nástroj pro drcení dat do smysluplných informací. Excel však nemůže ukládat příliš mnoho dat. Když ale použijeme Excel a Access společně, síla těchto nástrojů se exponenciálně zvyšuje. Pojďme se tedy naučit, jak připojit databázi Accessu jako zdroj dat k Excelu prostřednictvím VBA.
Připojení přístupové databáze jako zdroje dat Excel
1: Přidejte odkaz na datový objekt AcitveX
K připojení k databázi budeme používat ADO. Nejprve tedy musíme přidat odkaz na objekt ADO.
Přidejte do svého projektu VBA modul a klikněte na nástroje. Zde klikněte na reference.
Nyní vyhledejte Microsoft ActiveX Data Object Library. Zkontrolujte nejnovější verzi, kterou máte. Mám 6.1. Klikněte na tlačítko OK a je hotovo. Nyní jsme připraveni vytvořit odkaz na databázi Access.
2. Napište kód VBA pro navázání připojení k databázi Access
Chcete -li připojit aplikaci Excel k databázi aplikace Access, musíte mít databázi aplikace Access. Moje databáze se jmenuje "Testovat databázi.accdb ". Je uloženo v "C: \ Users \ Manish Singh \ Desktop" umístění. Tyto dvě proměnné jsou důležité. Budete je muset změnit podle svých potřeb. Zbytek kódu lze ponechat tak, jak je.
Zkopírováním níže uvedeného kódu vytvořte modul Excel VBA a proveďte změny podle svého požadavku. Vysvětlil jsem každý řádek kódu níže:
Sub ADO_Connection () 'Vytváření objektů aplikace Connection and Recordset Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String 'Deklarace plně kvalifikovaného názvu databáze. Změňte jej pomocí umístění a názvu databáze. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" „Toto je poskytovatel připojení. Pamatujte si to při pohovoru. PRVD = "Microsoft.ace.OLEDB.12.0;" „Toto je připojovací řetězec, který budete potřebovat při otevírání připojení. connString = "Provider =" & PRVD & "Data Source =" & DBPATH “otevření spojení conn.Open connString “dotaz, který chci spustit v databázi. query = "SELECT * from customerT;" 'spuštěním dotazu na otevřeném připojení. Získá všechna data v souboru rec objekt. rec.Open query, conn „vymazání obsahu buněk Buňky. Vymazat obsah 'získání dat ze sady záznamů, pokud existují, a jejich vytištění ve sloupci A listu aplikace 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 Loop End If 'uzavření spojení rec.Close conn.Close End Sub
Zkopírujte výše uvedený kód nebo si stáhněte níže uvedený soubor a proveďte v souboru změny tak, aby vyhovovaly vašim požadavkům.
Stáhnout soubor: VBA Database LearningKdyž spustíte tento kód VBA, Excel vytvoří připojení k databázi. Poté spustí navržený dotaz. Vymaže veškerý starý obsah na listu a vyplní sloupec A hodnotami pole 1 (druhé pole) databáze.
Jak toto připojení k databázi VBA Access funguje?
Dim conn As New Connection, rec As New Recordset
Ve výše uvedeném řádku nejenom deklarujeme proměnné Connection a sada záznamů, ale také je inicializujeme přímo pomocí klíčového slova New.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Tyto dva řádky jsou soutěžící. DBPATH se změní pouze s vaší databází. PRVD se připojuje k poskytovateli OLE DB.
conn.Open connString
Tento řádek otevírá připojení k databázi. Otevřená je funkce objektu připojení, která má několik argumentů. Prvním a nezbytným argumentem je ConnectingString. Tento řetězec obsahuje poskytovatele OLE DB (zde PRVD) a zdroj dat (zde DBPATH). Může také trvat admin a heslo jako volitelné argumenty pro chráněné databáze.
Syntaxe Connection.Open je:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long = -1])
Protože ve své databázi nemám žádné ID a heslo, používám pouze ConnectionString. Formát ConnectionString je „Poskytovatel =provider_you chcete použít; Zdroj dat =plně kvalifikovaný název databáze". Vytvořili jsme a uložili tento řetězecconnString proměnná.
query = "SELECT * from customerT;"
Toto je dotaz, který chci spustit v databázi. Můžete mít jakékoli dotazy, které chcete.
rec.Open query, conn
Tento příkaz spustí definovaný dotaz v definovaném připojení. Zde používáme Open metodu objektu sady záznamů. Veškerý výstup je uložen v objektu sady záznamůrec. Můžete načíst manipulovat nebo odstranit hodnoty z objektu sady záznamů.
Buňky. Vymazat obsah
Tento řádek vymaže obsah listu. Jinými slovy, odstraní vše z buněk listu.
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 Loop End If
Výše uvedená sada řádků kontroluje, zda je sada záznamů prázdná nebo ne. Pokud sada záznamů není prázdná (to znamená, že dotaz vrátil některé záznamy), smyčka začne a začne tisknout každou hodnotu pole 1 (druhé pole, v tomto případě jméno) v poslední nepoužité buňce ve sloupci.
(Používá se to jen k vysvětlení. Tyto řádky možná nemáte. Pokud chcete pouze otevřít připojení k databázi, stačí kód VBA nad těmito řádky.)
Použili jsme rec.EOF ke spuštění smyčky až do konce sady záznamů. Rec.MoveNext se používá k přechodu na další sadu záznamů. rec.Fields (1) se používá k získání hodnot z pole 1 (což je druhé, protože jeho indexování polí začíná od 0. V mé databázi je druhé pole First Name of the customer).
rec.Close conn.Close
Nakonec, když je veškerá práce, kterou jsme od rec a conn chtěli, hotová, zavřeme je.
Pokud chcete samostatně otevírat a zavírat konkrétní připojení, můžete mít tyto řádky v samostatném podprogramu.
Takže ano, lidi, tímto způsobem vytvoříte připojení k databázi ACCESS pomocí ADO. Existují i jiné metody, ale toto je nejjednodušší způsob, jak se připojit ke zdroji dat přístupu prostřednictvím VBA. Vysvětlil jsem to co nejpodrobněji. Dejte mi vědět, jestli to bylo užitečné v sekci komentáře níže.
Související články:
Použijte uzavřený sešit jako databázi (DAO) pomocí VBA v Microsoft Excelu | Chcete -li použít uzavřený sešit jako databázi s připojením DAO, použijte tento fragment VBA v aplikaci Excel.
Použijte uzavřený sešit jako databázi (ADO) pomocí VBA v Microsoft Excelu | Chcete -li použít uzavřený sešit jako databázi s připojením ADO, použijte tento fragment VBA v aplikaci Excel.
Začínáme s UserForms aplikace Excel VBA | K vkládání dat do databáze používáme formuláře. Excel UserForms jsou užitečné pro získávání informací od uživatele. Zde je návod, jak byste měli začít s uživatelskými formami VBA.
Změňte hodnotu/obsah několika ovládacích prvků UserForm pomocí jazyka VBA v aplikaci Excel | Chcete -li změnit obsah ovládacích prvků uživatelského formuláře, použijte tento jednoduchý úryvek VBA.
Zabraňte zavření formuláře uživatele, když uživatel klikne na tlačítko x pomocí VBA v aplikaci Excel | Aby se zabránilo zavření formuláře uživatele, když uživatel klikne na tlačítko x formuláře, použijeme událost UserForm_QueryClose.
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Funkce VLOOKUP v Excelu | Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
COUNTIF v aplikaci Excel 2016 | Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétní hodnoty není nutné filtrovat data. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.
