Wojciech Gardziński, Krzysztof Rumiński, 2009

ADO – skomplikowana technologia bazodanowa

- łatwa do użycia w Excelu

1. Najpierw o namacalnych efektach…

a. Pobierz plik: http://afin.net/excel/ADO4Excel.xls oraz dane do ćwiczeń http://afin.net/excel/KatalogDane/Dane.exe (Spakowany folder Dane z różnymi typami baz plikowych do ćwiczeń bazodanowych)

b. Rozpakuj dane na dysk – najlepiej do folderu o prostej ścieżce dostępu, np. bezpośrednio na C:\ . Utworzy się folder C:\Dane z plikami i podkatalogami.

c. Otwórz plik ADO4Excel.xls

W skoroszycie są trzy arkusze (trzy scenariusze użycia ADO w Excelu):

· ‘IntoASheet’ – Arkusz–przeglądarka wartości zapytania. Informatycy nazywają taki sposób zadawania zapytań SQL ‘Edytorem SQL’. Wpisujesz zdanie SQL – coś klikasz (uruchamiasz) – wyświetla się tablica wartości.

Nasz ‘Edytor’ ma dodatkową opcję: źrodło danych (tzw. ‘Connection string’, oznaczany jako ‘ConnectionString’ albo ‘ConnString’) – określa sterownik oraz ścieżkę do danych (Tu, przykładami sa bazy plikowe, ale działa, oczywiście, na wszystkich typach baz danych). Więcej przykładów – w skoroszycie. Tutaj podajemy ten parametr w tzw. składni ODBC.

Więcej na ten temat na stronie: http://www.connectionstrings.com

Aby uruchomić naszego ‘Edytora’, należy okreslić miejsce (ścieżkę), gdzie rozpakowaliśmy dane przykładowe i wpisać ją jako parametr DBQ=[ścieżka]\BAZA_DBF. Klikamy przycisk Insert data i, natychmiast, otrzymujemy rezultat w tablicy komórek, rozpoczynającej się od komórki A10.

To nie jest zwykłe wklejenie danych z pliku zewnętrznego!

To pełnoprawne zapytanie języka SQL do zewnętrznej bazy danych – tu: pliku DBF.

Dane zostały pobrane z pliku nie-excelowego, przefiltrowane (tylko rekordy, w których ‘nazwa’ kończy się literą ‘X’) i wklejone do arkusza, bez zastosowania kwerendy MS Query (skoroszyty z kwerendami nie działają na innych komputerach, niż zostały stworzone). Odświeżanie jest bardzo proste – jedno klikniecie myszą.

Największa zaletą takiego rozwiązania jest jednak to, że oba parametry, zarówno ‘ConnString’, jak i ‘SQL’ są parametryzowalne, czyli można je łatwo i wygodnie (bo w komórkach Excela) edytować i, w całości lub części, pobierać z jeszcze innych komórek akrusza. Tego inne edytory SQL nie zapewniają.

· ‘ExecuteSQL’ – Do wykonywania ‘zewnętrznych’ komend SQL, czyli sterowania operacjami bezpośrednio w bazie danych – dla bardziej zaawansowanych

To nieco trudniejsze, ale możliwe. Sterownie bazą danych (np. ad hoc stworzoną mini-hurtownią danych w pliku Accessa) z poziomu Excela jest wykonalne. Język SQL posiada komendy ‘manipulujące danymi’: może tworzyć, modyfikować i usuwać(!) tabele, dodawać, modyfikować i usuwać pola (kolumny) dodatkowych atrybutów, wypełniać (i zmieniać) je wartościami, również ‘obliczanymi na podstawie innych pól’ i wiele, wiele więcej. Warto się temu przyjrzeć bliżej pod okiem fachowca.

· ‘FunctionSQL’ – Przykład użycia funkcji, pobierającej wartość (jedna liczba!) z zewnętrznej bazy danych.

Prosta funkcja! Dwa argumenty! Dokładnie takie same, jak w komendach w arkuszu ‘IntoASheet’. Z tym, że funkcja zwraca JEDNĄ wartość – trzeba więc tak napisać zdanie SQL, żeby jego wynikiem była JEDNA wartość na JEDNYM polu (dowolnym). Od tego są w SQL tzw. funkcje ‘agregujące’: SUM, AVERAGE, COUNT, MAX, MIN.

2. Trochę o praktycznych zastosowaniach…

Analitycy potrzebują danych. Skąd je biorą? Zwykle z plików eksportowych systemów transakcyjnych, które zapisują dane albo do plików Excela, albo do plików tekstowych, rzadziej do plików DBF. To, z punktu widzenia technologii bazodanowej, pełnoprawne bazy danych, dostępne przez ODBC. A jak przez ODBC, to i przez ADO (technologia wyższego poziomu).

Analitycy jednak uparcie (bo tego ich uczą na studiach i taka jest ogólna praktyka) otwierają te eksporty w Excelu, a potem, tysiącami funkcji lub specjalnymi makrami, przetwarzają dane z tabel na inne (ulepszone) dane, te z kolei przeklejają do innych arkuszy, i tak, codziennie, za każdym razem, od początku.

Tymczasem jest ADO – podstawowa obróbka danych nie powinna się odbywać w arkuszu Excela – powinna być robiona wcześniej, już na ‘poziomie bazy danych’ – tu rozumiemy przez to owe eksporty, lub pliki dodatkowe, ale nie ‘otwierane’ Excelem (choć mogą to być pliki Excela).

Przykład: Analityk otrzymuje z systemu sprzedaży listę faktur w pliku Excela (50.000 wierszy, już martwi się, co będzie, gdy ilość wierszy przekroczy pojemność arkusza). Nie otwiera tego pliku jednak Excelem, tylko zapisuje na dysk i uruchamia jedną lub parę komend ADO, żeby:

· Dodać dodatkowe kolumny z dodatkowymi (własnymi) atrybutami

· Przefiltrować dane

· Połączyć dane z tabelą słownikową w innym arkuszu Excela

· Wybrać tylko rekordy spełniające warunki – mogą to być warunki nieoczywiste i bardzo skomplikowane, np. wybierz tylko tych klientów, dla Których suma wartości sprzedaży przekracza jakąś wartość (W Excelu możliwe dopiero po zastosowaniu tabeli przestawnej lub obliczeniu sum pośrednich), wybierz tylko faktury niezapłacone (dane o zapłatach w innej tabeli), itp.

· Wiele innych opcji, o których przeciętny analityk nie wie, że w ogóle są możliwe – firmowy informatyk jednak na pewno zna podstawy SQL…

3. Parę uwag ogólnych…

W niniejszym opracowaniu, a także w omawianych plikach testowych, stosujemy ‘angielszczyznę’. To razi, ale taka jest praktyka informatyków, szczególnie, jeśli chodzi o kod programu. Trzeba się przyzwyczaić, albo, we własnym zakresie, dodać polskie komentarze lub nazwy.

ADO – ActiveX Data Objects – technologia Microsoft dostępu do baz danych. Rozwija technologię DAO (recordset-y) i ODBC (uniwersalność), jak również posiada rozszerzenia dodatkowe, np. ADOMD (Multidimentional) do zapytań do kostek OLAP.

ADO wykorzystuje różne sposoby dostępu do bazy, określone tzw. ‘Provider-em’ czyli ciągiem znaków, specyfikujących sterownik, jego parametry (przykłady poniżej oraz w skoroszycie przykładowym), lokalizację bazy danych i inne.

Dla analityka jednak, ważne jest, że ‘obiekt ADO’, jak inne obiekty ‘COM’ może być wykorzystywany z poziomu programu VBA. I to, że poborem danych steruje się SQL-em, a nie makrami VBA. I… już.

Jedynym ograniczeniem jest znajomość SQL (Structured Query Language) – jest to jednak język (w prostych zastosowaniach analitycznych) bardzo łatwy, składający się z 10-20 komend (słów kluczowych). Trzeba jednak wiedzieć, jak ich używać.

Do poznania podstaw SQL wystarczy najbardziej podstawowa książka lub kurs

http://afin.net/samples/lessons

Zdania SQL można również tworzyć edytorami graficznymi, np. programem MS Query

http://afin.net/artykuly/gazetaprawna/9.%20MS%20Query.htm

4. A, na końcu, o technice i programowaniu

Kod programu, wykorzystującego ADO, jest, wbrew pozorom, bardzo prosty;

80% poniższego kodu to komentarze, interlinie, deklaracje zmiennych, obsługa błędów i komendy ‘sprzątające’ – ‘ważne’ linijki kodu są, właściwie,… trzy (pogrubioną czcionką). Analiza kodu – poniżej. Analizujemy wyłącznie kod modułu ‘IntoASheet’ – kod w pozostałych modułach jest, w gruncie rzeczy, podobny.

Sub IntoASheet()

    Dim objAdoDbRecordset As Object, rngMyRange As Object

    'Dim objAdoDbRecordset As New ADODB.Recordset

Dim i As Integer, varUserCalculationOption As Variant

    '>Temporarily sets calculation option to 'manual'

    varUserCalculationOption = Application.Calculation

    Application.Calculation = xlCalculationManual

    On Error GoTo ErrorLabel

    '>Where do you want to place the data?

    Set rngMyRange = ThisWorkbook.Sheets("IntoASheet")

    '>e.g.: Workbooks("YourWorkbook.xls").Sheets("YourWorksheetName")

    'Creates an ADO object

    Set objAdoDbRecordset = CreateObject("ADODB.Recordset")

    '>Opens the recordset

    objAdoDbRecordset.Open _

     rngMyRange.Range("B4").Value, _

     rngMyRange.Range("B3").Value, _

     2, 4

    objAdoDbRecordset.UpdateBatch

    '>Clears the data range

    rngMyRange.Range("A10").CurrentRegion.ClearContents

    '>Fills the columns' labels

    For i = 0 To objAdoDbRecordset.Fields.Count - 1

        Cells( _

         rngMyRange.Range("A10").Row, _

         rngMyRange.Range("A10").Column + i).Value = _

         objAdoDbRecordset.Fields(i).Name

    Next i

    '>Inserts the data

rngMyRange.Range("A10").Offset(1, 0).CopyFromRecordset _

     objAdoDbRecordset

    objAdoDbRecordset.Close

    GoTo EndLabel

ErrorLabel:

    MsgBox Err.Description, vbCritical, "Error"

EndLabel:

    Application.Calculation = varUserCalculationOption

    Set rngMyRange = Nothing

Set objAdoDbRecordset = Nothing

End Sub

Analiza kodu:

Set objAdoDbRecordset = CreateObject("ADODB.Recordset")

Tworzy obiekt ADO.Recordset, czyli wirtualny zbiór rekordów, na razie – pusty.

objAdoDbRecordset.Open _

     rngMyRange.Range("B4").Value, _

     rngMyRange.Range("B3").Value, _

     2, 4

To jedna, ‘przełamana’, linia programu. Metoda Open obiektu Recordset wypełnia Recordset danymi (rekordami), zgodnie z argumentami, pobranymi tu z komórek B3 i B4 naszego arkusza. Pozostałe argumenty ‘2’ i ‘4’, prosimy przyjąć jako sztywne – bez tłumaczenia.

rngMyRange.Range("A10").Offset(1, 0).CopyFromRecordset _

     objAdoDbRecordset

Z obiektu Recordset dane do arkusza pobiera się nadzwyczaj łatwo – zapewnia to gotowa metoda CopyFromRecordset obiektu Range. Tu dane wklejane są jedną linię poniżej A10, gdyż w wierszu 10. zapisany został nagłówek naszej tabeli (parę linijek wyżej w kodzie).

Życzymy Państwu przyjemnej… zabawy, bo, proszę nam wierzyć, żonglowanie danymi, tysiącami rekordów, filtrowanie, uszlachetnianie, grupowanie – to niezła zabawa. Szczególnie, jak się ma w pamięci metody i problemy, gdy robiliśmy to makrami VBA.

Oczywiście, to nie jest zabawka. Ale do tego, jak to można wykorzystać w praktyce, nie musimy już Was, analityków, przekonywać.

Życzymy więc, przede wszystkim, Efektów, przez bardzo duże ‘E’.