MS Excel

pro úplné pochopení mého dotazu je asi dobré držet se určitého scénáře.
Scénář předpokládá, že chcete do listu excelu tahat data z databázového serveru se kterými se dále libovolně pracuje.
Vyhněme se také možnosti, že máme k dispozici robustní řešení datových skladů potažmo BI nebo programátorské znalosti moderních objektových jazyků a frameworků.
V takových případech byste asi raději využili výhod reportovacích nástrojů plnohodnotných databázových serverů nebo knihoven jako např ADO.NET potažmo ORM.
V našem modelu jsme nuceni využít nativních nástrojů excelu, případně VBA. Lze však využít skutečnosti, že kromě selectů můžete vytvořit nebo požádat o vytvoření databázových objektů jako uložené procedury nebo pohledy.

Otázka je už vlastně prostá. Jaké nástroje byste preferovali Vy v rámci uvedeného scénáře? Pokud možno uveďte vaše poznatky, třeba i výhody a nevýhody.
Používáte MS Query, plníte QueryTable nebo používáte ADO a mnohovrstvé ovladače ODBC? Nebo něco jiného? Proč?

S těmi, kteří to vydrželi číst až sem

se můžu podělit o vlastní dosavadní zkušenosti.

V době, kdy jsem se na této problematice zamýšlel, kladl jsem si určité (základní a obecné) požadavky, které mohou být součástí našeho scénáře.

1) Dostatečná rychlost bez zbytečné zátěže serveru - zejména držet připojení pouze po nezbytnou dobu.
2) Možnost parametrizace.
3) Flexibilita pří rozložení na listu.
4) Komplexnost, která umožní vytvářet i robustnější řešení.
5) Dostatečná obecnost a znovupoužití.

Vyzkoušel jsem některé zmiňované nativní nástroje s různými výsledky. Nakonec jsem se rozhodl pustit se do VBA a zastaralého ADO (nyní již VBA/VB6 snippet) a pokusit se ušít si vše na míru podle vlastního gusta. Protože jsem nechtěl uživatele řešení zatížit nutností znalosti jazyka SQL, vybral jsem si pro získání dat uložené procedury nebo-li Stored procedures (dále SP).
Ty mají několik zásadních výhod. Pokud si je neumíte vytvořit sami, je ještě možnost požádat o vytvoření zkušeného vývojáře, který umí napsat optimalizovaný dotaz zapouzdřený v SP. Vytvořenou SP pak můžete bezpečně volat z excelu a předávat jí dynamicky parametry. SP je předkompilovaná tzn., že databázovému serveru odpadá zbytečná režie. Trocha ušetřeného výkonu se hodí zejména v případě, kdy jste nuceni využít pomalé mnohovrstvé ODBC. Dalším benefitem je, že veškerá zátěž je ponechána databázovému serveru. SP mají ještě jednu zajímavou vlastnost. Jde o možnost vracet více než jednu sadu záznamů.
Vygenerované sady záznamů lze pak zachytávat pomocí metody NextRecordset objektu Recordset. Lehce tak vytvoříte např. několik tabulek s agregovanými daty pouze pomocí volání jedné SP. Bohužel při práci s ADO je znát, že je to značně zastaralá technologie. Naopak příjemným překvapením byla metoda CopyFromRecordset kolekce Cells ve VBA, která je poměrně svižná. Největším žroutem volného času se nakonec stala implementace velkého množství flexibility. Nakonec jsem bohužel vygeneroval takové množství kódu, že jsem si musel vytvořit "objektovou vrstvu" umístěním kódu do modulu třídy. Nutno dodat, že nemůže být řeč o objektovém programování. V tomto případě se jedná spíše o jednoduché nahrazení funkcí a sub procedur, ale s tím, že se kód lépe udržuje a získáte omezené zapouzdření.
Snad se mi povede přiložit soubor s ukázkou kódu. Sešit obsahuje tři moduly. mdl_Globals pro veřejné konstanty, mdl_Main, kde je ukázka použití modulu třídy, který pracuje s uloženými procedurami pomocí ADO. Soubor jsem vytvořil v MS Excel 2013.

Uzamčeno - nelze přidávat nové příspěvky.

AL(23.2.2015 12:00)#023816

To, čo popisujete, je úplne iná liga, než ktorú som schopný zatiaľ hrať ja. Rád by som diskutoval, ale tu, priznám sa, nemám znalosti, ktorými by som sa čo i len priblížil...citovat

daal(23.2.2015 19:13)#023822

@AL, děkuji za odpověď.
Musím, ale napsat, že podle toho jak Vás znám (z různého fóra) se mě tomu těžko věří

.
Myslím, že máte na PC nainstalovanou min. express edici sql server-u , a že koketujete s sql. Také se většinou inspiruji právě od Vas, uživatele elCHa a dalších zde.

Četl jsem také Vaše odpovědi, kde využíváte QueryTable.Add, MS Query ...
Pro mě je opravdu důležitý jakýkoli postřeh. Mě se např. stalo, že kontingenční tabulka postavená na zdrojových datech z objektu QueryTable vynechala některá data, přestože ve zdrojových datech byla obsažena a filtrem nebyla skryta. U QT jsem rovněž pozoroval, že si drží připojení déle než by se mi líbilo. Nikde se třeba nemůžu pořádně dočíst jak u toho využívá mezipaměť. Atd...

Vítám i konstruktivní kritiku. Pro ty, kteří se ochotně podívali i do VBE jsem se snažil vše důkladně zdokumentovat.

Nejen od zkušených jako je eLCHa, LUBO, AL ... uvítám jakékoli kritické komentáře ke kódu, něco jako "máš toho tam moc, je to nečitelné". Nebo "raději vždy využívám nativní 'odladěné' nástroje".

Prosím, nevzdávejte to ještě s tímto vláknem. Každá rada dobrá.citovat

AL(23.2.2015 19:29)#023824

Myslím, že máte na PC nainstalovanou min. express edici sql server-u , a že koketujete s sql.
Áno, oboje je pravda a mám podozrenie, že s inštaláciou SQL server express ste mi pomáhal práve Vy

Pravdou je i to, že mám za sebou nejaké pokusy s ADO, MS Query používam v prípade, že potrebujem jednorazovo dostať nejaké veci z Accessu a nechcem v Accesse tvoriť ďalší jednorazový dotaz. Proti Vám som ale v týchto veciach obyčajný břídil, skutočne pochybujem, že by Vám zrovna moje postrehy v tejto problematike k niečomu boli. Ale sľubujem, že SQL server v blízkej dobe už začnem naozaj študovať serióznejšie, v aktuálnej práci mi síce stačí Access a už tak predčím znalosťami kolegov z práce, ale treba myslieť i na prípadnú zmenu zamestnania a tieto veci ma zaujímajú, takže s motiváciou problém nemám.
Ja Vám rád nejakú spätnú väzbu dám, akonáhle usúdim, že mám čo k veci povedať, nebojte

ešte ma napadlo: Profil na LinkedIn máte, že áno? Skúste kontakt na osobu menom Craig Hatmaker, myslím, že s ním by ste si mohli predávať vzájomné hodnotné skúsenosti. Craig určite nebude proti..citovat

stanher(24.2.2015 14:06)#023835

Jsem taky amatér, ale jestli mužů jeden postřeh, a to k vynechávání některých dat v kontingenční tabulce i když ve zdrojových datech data jsou. Zjistil jsem, že se to stává v případě, když v prvním řádku zdrojových dat daného sloupce není žádná hodnota. Aby to načítalo vše stačí do prázdných buněk zapsat "0".citovat

daal(24.2.2015 14:16)#023836

@stanher
děkuji moc za postřeh. Rozhodně se na to zaměřím a prubnu. Díkycitovat

daal(25.2.2015 10:55)#023850

Admin může uzamknout. Děkuji za odpovědicitovat

Téma: Připojení k databázi - preference

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje