Jak získat data z SQL dotazu v Microsoft Access VBA? Jak odeslat dotaz do databáze ve VBA Přístupové dotazy do databáze INSERT, UPDATE, DELETE ve VBA

Tato lekce je o SQL dotazy do databáze na Přístup VBA. Podíváme se, jak probíhají VBA dotazy INSERT, UPDATE, DELETE do databáze a také se dozvíme, jak získat konkrétní hodnotu z SELECT dotazu.

Ti, kteří programují Přístup VBA a při práci s databází SQL serveru často čelí tak jednoduchému a nezbytnému úkolu, jako je odeslání SQL dotazu do databáze, ať už jde o INSERT, UPDATE nebo jednoduchý SQL SELECT dotaz. A protože jsme začínající programátoři, měli bychom to také umět, takže dnes to uděláme.

Tématu získávání dat z SQL serveru, kde jsme psali kód ve VBA pro získání těchto dat, jsme se již dotkli např. v článku o Nahrávání dat do textového souboru z MSSql 2008 nebo se také trochu dotkli v materiálu Nahrávání dat z Accessu do šablony Wordu a Excelu, ale tak či onak jsme to zvažovali povrchně a dnes o tom navrhuji mluvit trochu podrobněji.

Poznámka! Všechny příklady níže jsou popsány pomocí projektu Access 2003 ADP a databáze MSSql 2008.

Počáteční údaje pro příklady

Řekněme, že máme tabulku test_table, která bude obsahovat čísla a názvy měsíců v roce (dotazy se provádějí pomocí manažerské studio)

VYTVOŘIT TABULKU .( NOT NULL, (50) NULL) ON GO

Jak jsem řekl, použijeme projekt ADP nakonfigurovaný pro práci s MS SQL 2008, ve kterém jsem vytvořil testovací formulář a přidal tlačítko start s popisem "Běh", který budeme potřebovat k otestování našeho kódu, tzn. celý kód zapíšeme do obsluhy události " Stisk tlačítka».

Databázové dotazy INSERT, UPDATE, DELETE ve VBA

Abychom to dlouho nenatahovali, začněme, řekněme, že potřebujeme přidat řádek do naší testovací tabulky ( kód je okomentován)/

Private Sub start_Click() "Deklarujte proměnnou pro uložení řetězce dotazu Dim sql_query As String "Zapište do ní dotaz, který potřebujeme sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "Červen")" "Spustit to pomocí DoCmd. RunSQL sql_query End Sub

V tomto případě se dotaz provede pomocí aktuálního nastavení připojení k databázi. Můžeme zkontrolovat, zda byla data přidána nebo ne.

Jak vidíte, data byla vložena.

Abychom odstranili jeden řádek, napíšeme následující kód.

Private Sub start_Click() "Deklarujte proměnnou pro uložení řetězce dotazu Dim sql_query As String "Zapište do ní mazací dotaz sql_query = "DELETE test_table WHERE id = 6" "Spusťte ji DoCmd.RunSQL sql_query End Sub

Pokud zaškrtneme, uvidíme, že požadovaný řádek byl smazán.

Pro aktualizaci dat napíšeme aktualizační dotaz do proměnné sql_query, doufám, že význam je jasný.

SELECT dotaz do databáze ve VBA

Zde jsou věci o něco zajímavější než u jiných konstrukcí SQL.

Nejprve řekněme, že potřebujeme získat všechna data z tabulky a například je zpracujeme a zobrazíme ve zprávě a vy je samozřejmě můžete použít pro jiné účely, k tomu píšeme následující kód

Private Sub start_Click() "Deklarování proměnných "Pro sadu záznamů z databáze Dim RS As ADODB.Recordset "Řetězec dotazu Dim sql_query As String "Řetězec pro zobrazení celkových dat ve zprávě Dim str As String "Vytvoření nového objektu pro sadu záznamů RS = Nový ADODB .Recordset "Řetězec dotazu sql_query = "SELECT id, name_mon FROM test_table" "Proveď dotaz pomocí aktuálního nastavení připojení projektu k zobrazení zprávy str = str & RS.Fields("id") & "-" & RS. Fields("name_mon") & vbnewline "přejít na další záznam RS.MoveNext Wend "Výstup zprávy msgbox str End Sub

Zde již používáme přístupové smyčky VBA k procházení všech hodnot v naší sadě záznamů.

Poměrně často je však nutné získat ne všechny hodnoty ze sady záznamů, ale pouze jednu, například název měsíce podle jeho kódu. A k tomu je použití smyčky nějak drahé, takže můžeme jednoduše napsat dotaz, který vrátí pouze jednu hodnotu a odkážeme na ni, například dostaneme název měsíce kódem 5

Private Sub start_Click() "Deklarování proměnných "Pro sadu záznamů z databáze Dim RS As ADODB.Recordset "Řetězec dotazu Dim sql_query As String "Řetězec pro zobrazení konečné hodnoty Dim str As String "Vytvoření nového objektu pro sadu záznamů RS = New ADODB.Recordset "Query string sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Proveďte dotaz s použitím aktuálního nastavení připojení projektu RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Získejte naši hodnotu str = RS. ) msgbox str end sub

Pro univerzálnost jsme zde již oslovovali nikoli názvem buňky, ale jejím indexem, tzn. 0, což je úplně první hodnota v Sada záznamů, nakonec jsme dostali hodnotu "Smět".

Jak vidíte, vše je docela jednoduché. Pokud často potřebujete získat konkrétní hodnotu z databáze ( jako v posledním příkladu), pak doporučuji vypsat veškerý kód v samostatné funkci (Jak napsat funkci ve VBA Access 2003) s jedním vstupním parametrem, například kód měsíce ( s ohledem na náš příklad) a jednoduše tam, kde je potřeba tuto hodnotu zobrazit, zavoláme potřebnou funkci s potřebným parametrem a je to, výrazně zredukujeme VBA kód a zlepšíme vnímání našeho programu.

To je pro dnešek vše. Hodně štěstí!

Ahoj, právě jsem se dozvěděl něco o tom, jak vložit své příkazy SQL do VBA (nebo je alespoň napsat), ale nevím, jak získat vrácená data?

Mám několik formulářů (graf formulářů) založených na dotazech, které spouštím docela pravidelně, jen měním časové rámce (např. 10 nejlepších prodejů za měsíc tak nějak). Pak mám procedury, které automaticky předávají objekt grafu do powerpointové prezentace. Takže mám všechny tyto dotazy předem vytvořené (např. 63) a tvar grafu, aby se vešly (no jo.... 63... vím, že je to špatné) a pak se všechny tyto věci vytvoří na "otevřít/zavřít" »an událost vedoucí k další (je to jako můj nejlepší pokus být hackem....nebo domino; podle toho, čemu dáváte přednost).

Takže jsem se snažil naučit, jak používat příkazy SQL ve VBA, abych to nakonec mohl dělat tam (možná budu muset uložit všechny ty tvary grafů, ale nevím, protože mi zjevně chybí porozumění) .

Takže kromě otázky, kterou jsem položil nahoře, kdo může nabídnout radu? Děkuji

6 odpovědí

10

Je to trochu zastaralé, takže možná budete chtít vzít knihu na toto téma. Ale tady je spousta přístupu ke zdrojům a také trochu tutoriálů a příkladů. Ale v zásadě...

Dim dbs As Database Dim rs As Recordset Dim strSQL As String Set dbs = CurrentDb strSQL = "váš dotaz zde Set rs = dbs.OpenRecordset(strSQL) If Not (rs.EOF And rs.BOF) Then rs.MoveFirst "získat výsledky pomocí rs.Fields() Else "Použít výsledky

Za komentářem: podívejte se na třídu záznamů. Obsahuje kolekci s názvem pole, což jsou sloupce, které jsou vráceny z vašeho dotazu. Bez znalosti vašeho obvodu je těžké říct, ale něco jako...

Rs.MoveFirst Do While Not rs.EOF "udělejte něco jako rs("SomeFieldName") rs.MoveNext Loop

Jak jsem řekl, nejlepší je vzít si knihu na toto téma, mají tuny příkladů.

Použijte parametrizovaný QueryDef a volejte jej z VBA.
Dotaz je jednodušší na design...snadno testovatelný...a snadno dostupný pomocí VBA nebo formuláře.

Dim qd jako querydef set qd = currentdb.querydefs!myquerydef qd.parameters!parm1=val1

nebo qd.execute

Dim rs jako sada záznamů rs = qd.openrecordset()

Zde je funkce, kterou byste mohli zvážit refaktorování, abyste ji mohli vložit a budete ji moci znovu použít kdekoli v kódu.

Takže konstujte nebo vytvořte řetězec pro váš příkaz SQL a jako argument vložte řetězec dezinfekce, NON SQL INJECTION :)

StrSQL = "SELECT * FROM Customer WHERE ID = " & SecureParamIsNotSQLInjection(customerID)

Poté zavolejte funkci/sub, odkud potřebujete získat data/sada záznamů/provést instrukci. Vytvořením více funkcí/subs pro přístup k datům, kde můžete pouze spustit příkaz UPDATE nebo načíst jednu hodnotu nebo načíst úplné záznamy.

Klíčem je zde zachovat všechny tyto funkce na jednom místě a používat je všude. Zde je příklad ve VBScript.

Sub DoStuff(strSQL) Set adoCon = Server.CreateObject("ADODB.Connection") strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Zdroj dat=" & Server.MapPath("db\Database.mdb") "strConnString = "OVLADAČ=(Ovladač Microsoft Access (*.mdb)); DBQ=" & Server.MapPath("db\Database.mdb") adoCon.Open strConnString Set rsMain = Server.CreateObject("ADODB.Recordset") rsMain.Open strSQL, adoCon Proveďte Zatímco NE rsMain.EOF customerName = rsMain(" CustomerName") "hloupý příklad RsMain.MoveNext Loop rsMain.Close Set adoCon = Nothing End Sub

Dalším způsobem, jak toho dosáhnout, o kterém se, jak se zdá, nikdo nezmínil, je propojit váš graf s jedním uloženým QueryDef a poté za běhu QueryDef přepsat. Nyní nedoporučuji měnit uložené QueryDefs pro většinu kontextů, protože to způsobuje frontální nadýmání a obvykle to ani není nutné (ve většině kontextů, kde používáte uložený QueryDef, lze filtrovat tak či onak, v kontextu, ve kterém jsou se používají například jako jeden z formulářů RecordSource, jednoduše předáte jeden argument DoCmd.OpenForm).

Grafy se liší, protože jízdní grafy SQL nelze za běhu měnit.

Někteří z nich navrhovali možnosti, ale otevřením formuláře s grafem, který používá řetězec SQL s možnostmi, se zobrazí dialogy výchozích možností. Jedním ze způsobů, jak se tomu vyhnout, je použít dialogový formulář pro shromáždění kritérií a poté nastavit odkazy na ovládací prvky v dialogu jako parametry atd.

PARAMETRY!! dlouho;

Pokud používáte odkazy formuláře, je důležité, abyste tak učinili, protože od Accessu 2002 je služba Jet Expression Service nezpracovává vždy správně, když jsou ovládací prvky null. Jejich zadáním jako parametrů se tento problém napraví (který se před Access XP nevyskytoval).

Jednou ze situací, kdy musíte přepsat QueryDef pro graf, pokud chcete, je umožnit uživateli vybrat N v TOP N SQL příkazu. Jinými slovy, pokud chcete mít možnost vybrat TOP 5 nebo TOP 10 nebo TOP 20, budete muset upravit uložený QueryDef, protože N nelze parametrizovat.

Přístup uložil dotaz navržený pomocí nástroje pro tvorbu dotazů s názvem „myQuery“. Databáze se připojuje k systému prostřednictvím připojení ODBC. Makra jsou všechna zahrnuta.

vynikat má ADODB připojení pro připojení k databázi přes

Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = Nové ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Otevřít "MyDatabase.accdb" End With

Obvykle pokračujete a prostě napíšete své SQL, což je naprosto v pořádku, a pak uděláte něco jako

Dim sqlQuery As String sqlQuery = "SELECT * FROM myTable" Set rs = New ADODB.Recordset rs.Open sqlQuery, con, ...

Ale chci získat přístup k dotazu, který mám uložený v databázi přístupu. Jak tedy mohu zavolat uložený dotaz na databázi, kterou jsem právě připojil.

Již vyzkoušeno

1. con.Execute("EXEC myQuery"), ale řekl mi, že to nemůže být find myQuery.
2. rs.Otevřít "myQuery", con ale tento je neplatný a chce od něj příkazy SELECT /etc

5 odpovědí

6

Myslím, že si to můžete představit jako uloženou proceduru.

Pokud začneme těsně před Dim sqlQuery As String

Dim cmd jako nový ADODB.Command cmd.CommandType = adCmdStoredProc cmd.CommandText = "myQuery" cmd.ActiveConnection = con Set rs = cmd.Execute()

Poté si vezměte svou úlohu sady záznamů.

1

Byl jsi skoro tam

Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = Nové ADODB.Connection With con .Provider = "Microsoft.ACE.OLEDB.12.0" .Otevřít "z:\docs\MyDatabase.accdb" End with con.Execute "MyQuery"

Nechte Exeka stranou.

Můžete také přidat možnosti, je to trochu staré, ale mělo by pomoci:

0

Toto je druh hacku, ale můžete požádat o žádost. To znamená, že nahraďte řádek sql následujícím:

SqlQuery = "SELECT * FROM QueryName;"

Před spuštěním tohoto programu se musíte ujistit, že databáze Accessu byla uložena, tzn. stiskněte Ctrl+S (nestačí spustit dotaz v Accessu).

0

Podařilo se mi spustit aktualizační dotaz, který již byl uložen v Accessu pomocí:

Connection.Execute "My_Update_Query_Already_Saved_In_Access", adExecuteNoRecords, adCmdStoredProc

To mi způsobilo chyby, dokud jsem nenahradil mezery v názvu dotazu podtržítky v databázi Accessu i v příkazu vykonat.

0

Od založení tohoto vlákna už uplynula dlouhá doba. Pokud vše správně chápu, mohu přidat něco užitečného. Pojmenoval jsem to, co OP popisuje, což je proces použití SQL z dotazu uloženého v ACCDB ke spuštění ve VBA přes DAO nebo ADOBD. Dal jsem mu název „poskytovatel vlastnosti objektu“ i se zkratkou OPP v poznámkách a předponou/příponou pro název objektu.

Myšlenka je taková, že existující objekt v ACCDB (obvykle dotaz) odhaluje vlastnost (obvykle SQL), kterou je třeba použít ve VBA. Dal jsem dohromady funkci, abych vysál SQL z dotazů; viz. níže. Varování: omlouvám se, ale toto je vše v DAO, ADODB moc nepoužívám. Doufám, že vám tyto nápady budou stále užitečné.

Dokonce jsem zašel tak daleko, že jsem vyvinul metodu pro použití/vkládání náhradních parametrů v SQL, které pochází z těchto OPP dotazů. Potom použiji VBA.Replace() k provedení nahrazení před použitím SQL ve VBA.

Cesta objektu DAO k dotazu SQL v ACCDB je následující:

MySqlStatement = Access.Application.CurrentDb.QueryDefs("myQueryName").SQL

Používám nahraditelné parametry tak, že vyhodnotím, co by se mělo nahradit, a zvolím neobvyklý název pro parametr, který nemůže existovat ve skutečné databázi. Z velké části jsou jedinými substitucemi, které jsem provedl, názvy polí nebo tabulek nebo výrazy klauzule WHERE a HAVING. Takže jim říkám věci jako "(ReplaceMe00000001)" a pak použiji funkci Replace() k provedení práce...

SqlText = VBA.Replace(sqlText, "(ReplaceMe00000001)", "SomeActualParameter") ...

a poté použijte sqlText ve VBA. Zde je pracovní příklad:

Veřejná funkce MySqlThing() Dim sqlText jako řetězec Dim myParamater jako řetězec Dim myExpression jako řetězec "Nastavit vše. sqlText = getSqlTextFromQuery("myQuery") myParameter = "(ReplaceMe00000001)" myExpression = "Something12Do17Some" . sqlText = VBA.Replace(sqlText, myParameter, myExpression) "Potom použijte SQL. db.Execute sqlText, dbFailOnError End Function getSqlTextFromQuery(ByVal oppName As String) As String Dim app As Access.Application Dim Database Dim Ddefs As DAO.QueryDefs Dim qdef As DAO.QueryDef Dim sqlText As String Set app = Access.Application Set db = app.CurrentDb Set qdefs = db.QueryDefs Set qdef = qdefs(oppName) oppGetSqlText = qdef.SQL End Function

Mám soubor Excel, který potřebuji zautomatizovat. Když uživatel otevře sestavu Excel, bude vyzván k aktualizaci dat. Pokud řeknou ano, musím spustit dotaz...

Jak spustit makro VBA pomocí dotazu na zobrazení v MS Access 2013 z JS ActiveX ADO? Funkce VBA je přimět aktuálního uživatele přihlášeného pomocí: Public Declare...

Snažím se spustit dotaz uložený v databázi přístupu z aplikace Excel vba. Dotaz funguje dobře, pokud jej otevřu a spustím v databázi přístupu, ale při spuštění z modulu vyvolá chybu...

Jak provést uložený dotaz v MS Access 2007 ve VBA? Nechci kopírovat a vkládat SQL do VBA. Raději udělám jen název dotazu. To nebude fungovat... VBA nemůže najít dotaz....

Jak mohu požádat o vrácení záznamů v databázi ms-access pomocí kódu VBA?

Jak provést dotaz v MS access db z kódu Excel VBA nebo makra. Požadavek MS-Access přebírá některé parametry, které je třeba předat z Excelu. Děkuji

Mám situaci velmi podobnou následujícímu příspěvku: Žádost o přístup do excelu 2010 pro vytvoření grafu přes vba V mém případě exportuji tabulku, ale pro soubor chci udělat mnohem víc...

Mám UPDATE pass through dotaz uložený v Access 2007. Když dvakrát kliknu na pass through dotaz, běží úspěšně. Jak mohu dosáhnout toho, aby byl tento dotaz proveden z VBA? Já bych...

Mám obrovskou datovou sadu, kterou potřebuji importovat z Excelu do Accessu (~800 000 řádků). Mohu však ignorovat řádky se specifickou hodnotou sloupce, které se dají dohromady jako 90 %...

existuje způsob, jak spustit dotaz MDX v aplikaci Excel VBA? Myslel jsem, že by to šlo udělat přes ADO , stejně jako v případě SQL (ano, vím, že SQL je jiný než MDX - problém, který mnohokrát...

S makrem OpenRequest V databázích Accessu můžete otevřít vybrané dotazy a křížové dotazy v zobrazení datového listu, zobrazení návrhu nebo zobrazení náhledu. Tato akce spustí požadavek na změnu. Můžete také vybrat režim zadávání dat pro dotaz.

Poznámka: Toto makro je dostupné pouze v prostředí databáze Access (MDB nebo ACCDB). Pokud používáte Access Project Environment (ADP), viz makra OpenView, Otevřete uloženou proceduru A OpenFunction. makro OpenRequest není k dispozici ve webových aplikacích Accessu.

Nastavení

makro OpenRequest má následující argumenty:

Poznámky

Pokud pro argument Pohled nastavená hodnota Stůl, Access zobrazí sadu výsledků, když je použit výběrový dotaz, křížový dotaz, sjednocovací dotaz nebo serverový dotaz, vlastnost ReturnsRecords na čem záleží Ano. Pokud se jedná o požadavek na změnu, požadavek na definici dat nebo požadavek na server, vlastnost ReturnsRecords který je nastaven na Ne, požadavek se provede.

makro OpenRequest je totéž jako poklepání na dotaz v navigačním podokně nebo kliknutí pravým tlačítkem v navigačním podokně a výběr pohledu. Při použití makra můžete vybrat další možnosti.

Poraďte

Dotaz můžete přetáhnout z navigačního podokna do okna návrháře maker. Tím se automaticky vytvoří makro. OpenRequest, který otevře dotaz v zobrazení datového listu.

Pokud přepnete na konstruktor, když je dotaz otevřený, hodnota argumentu Datový režim je odebrán. Toto nastavení nebude mít žádný účinek, i když se uživatel vrátí do zobrazení datového listu.

Pokud nechcete zobrazovat systémové zprávy, které se obvykle objevují při spuštění požadavků na změnu (říkají, že jde o požadavek na změnu a počet záznamů, které ovlivňuje), můžete je vypnout pomocí makra SetWarning.

Chcete-li spustit makro OpenRequest v modulu Visual Basic for Applications (VBA), použijte metodu OpenRequest objekt DoCmd.