Access advanced

Wstęp

Zaczynając przygodę z Accessem musisz wiedzieć, że jest to bardzo rozbudowane i potężne narzędzie do gromadzenia a także przetwarzania danych. Podczas dalszej lektury poznasz wiele powodów, którymi będę przekonywał, że Access jest niezastąpionym narzędziem w wielu (oczywiście nie wszystkich) procesach przetwarzania danych.

Podczas lektury poznasz wszystko będzie Ci potrzebne do stworzenia w pełni funkcjonalnej bazy danych. Wszystkie omawiane poniżej zagadnienia zostaną podporządkowane jednemu celowi. Celem tym będzie stworzenie bazy danych kino maniaka czyli zbioru informacji o filmach. Prace będę prowadził używając Accessa 2007 dlatego wszystko to będzie można powtórzyć używając wersji 2010 a prawie wszystko w 2003 i wcześniejszych.

Musisz wiedzieć, że niniejsze opracowanie prezentuje tylko wybrane możliwości Accessa. Wiele czynności, jak na przykład tworzenie tabel, czy kwerend można wykonać na różne sposoby. Z reguły będę prezentował jedynie jedną, moim zdaniem najlepszą, ścieżkę osiągnięcia celu. Jeśli znasz inny sposób i uważasz, że jest on dla Ciebie bardziej odpowiedni, powinieneś pozostać przy swoim ;-)

Na wstępie przedstawię kilka ogólnych pojęć. A przede wszystkim, strukturę bazy danych Accessa. Do podstawowych obiektów z jakich będziesz korzystać podczas pracy z MS Access to: tabele, kwerendy, formularze i raporty. Nie są to wszystkie obiekty a jedynie te najważniejsze. W dalszej części poznasz jeszcze kilka innych użytecznych obiektów ale teraz zajmiemy się najważniejszymi.

1. Tabele to podstawowy element każdej bazy danych Accessa. Tabel będziesz używać tylko i wyłącznie do przechowywania danych.
2. Kwerendy z kolei to zdecydowanie najczęściej używany obiekt. Dzięki nim można wyświetlać oraz modyfikować informacje znajdujące się w tabelach.
3. Formularze służą do przeglądania, dodawania i modyfikowania danych. Służą także do tworzenia interfejsu użytkownika aby korzystanie z bazy danych było szybkie i efektywne.
4. Raporty to obiekty umożliwiające analizę i przeglądanie danych a przede wszystkim prezentację ich w formie dostosowanej do wydruku.

Tabele

Tak jak wspomniałem we wstępie tabele to najważniejszy element bazy danych. Są bardzo ważne gdyż przechowują one wszystkie gromadzone i przetwarzane dane. O ich znaczeniu decyduje również to, że mają decydujący wpływ na kształt i efektywność wszelkich dalszych prac. Od struktury tabel oraz relacji pomiędzy nimi zależeć będzie sposób tworzenia kwerend oraz innych obiektów, możliwości tworzenia różnorodnych zestawień oraz ilość trudności i błędów jakie możemy napotkać. Mówiąc wprost: jeśli tabele zostaną źle zaprojektowane to cała baza danych będzie działała w sposób nieprawidłowy.

Jak projektować tabele?

Najważniejszą rzeczą jest zadecydowanie jak podzielić wszystkie informacje jakie znajdą się w bazie danych na mniejsze tabele. Na początek spójrzmy na jedną "płaską" tabelę zawierającą poniższe informacje.

Tytul                        Aktor              RokProdukcji     DataUrodzenia
Batman - Początek            Morgan Freeman     2005
Choć goni nas czas           Morgan Freeman     2007
Dwóch gniewnych ludzi        Jack Nicholson     2003             1937-04-22
Evan Wszechmogący            Morgan Freeman     2007
Infiltracja                  Jack Nicholson     2006             1937-04-22
Lepiej późno niż później     Jack Nicholson     2003             1937-04-22
Mroczny rycerz               Morgan Freeman     2008
Obietnica                    Jack Nicholson     2001             1937-04-22
Schmidt                      Jack Nicholson     2002             1937-04-22
Skąd wiesz?                  Jack Nicholson     2010             1937-04-01
Smaki miłości                Morgan Freeman     2007
Batman - Początek            Morgan Freeman     2005

Taka tabela stwarza kilka problemów:

1. Zwróć uwagę na nadmiarowość danych. Cała tabela zawiera informacje raptem o kilkunastu filmach w których gra tylko dwóch aktorów. Nie mniej jednak nazwisko każdego z nich pojawia się wielokrotnie. To spore marnotrawstwo.
2. Nadmiarowość danych prowadzi także do spadku efektywności wprowadzania danych. Zwróć uwagę, że chcąc wprowadzić datę urodzenia Morgana Freemana trzeba będzie to zrobić aż 6 razy.
3. Nadmiarowość danych prowadzi również do błędów i niespójności danych. Przyjrzyj się dokładnie kolumnie DataUrodzenia. Zauważysz, że Jack Nicholson ma wpisane dwie różne daty urodzenia. Raz jest to 1937-04-22 a raz 1937-04-01. To oczywiste że tylko jedna z nich jest prawdziwa, ale która? Czy aby na pewno ta, która pojawiła się więcej razy?
4. Nadmiarowość danych może także prowadzić do zduplikowania całych rekordów (wierszy). Zwróć uwagę na pierwszy i ostatni rekord - są identyczne.

Tabele podobne do powyższej (z tymi samymi przypadłościami) często można spotkać w Excelu. Aby rozwiązać problemy wymienione powyżej powinniśmy podzielić te dane na mniejsze tematyczne tabele. Pierwszą z nich może być tabela zawierająca wyłącznie tabele o aktorach. Znajdą się w niej dwie kolumny: pierwsza z imieniem i nazwiskiem, oraz druga z datą urodzenia.

Aktor              DataUrodzenia
Jack Nicholson     1937-04-22
Morgan Freeman     1937-06-01

Po stworzeniu powyższej tabeli możemy usunąć z pierwotnej tabeli informacje o nazwisku oraz dacie urodzenia aktora. W miejsce tych dwóch pól powinniśmy wstawić nowe pole - identyfikator autora aby możliwe było powiązanie ze sobą informacji z obu tabel. To powiązanie nazywać będziemy relacją.

Zmodyfikujmy zatem tabelę z autorami poprzez dodanie do niej dodatkowego pola z unikalnym identyfikatorem.

AktorID     Aktor              DataUrodzenia
1           Jack Nicholson     1937-04-22
2           Morgan Freeman     1937-06-01

Pole AktorID jest identyfikatorem, który będzie unikalny dla każdego aktora, tak więc jednoznacznie będzie identyfikował każdą osobę. To pole będzie naszym kluczem podstawowym.

Teraz zmodyfikujmy pierwotną tabelę i dodajmy w niej pole AktorID po modyfikacjach otrzymamy poniższą tabelę.

Tytul                        AktorID     RokProdukcji
Batman - Początek            2           2005
Choć goni nas czas           2           2007
Dwóch gniewnych ludzi        1           2003
Evan Wszechmogący            2           2007
Infiltracja                  1           2006
Lepiej późno niż później     1           2003
Mroczny rycerz               2           2008
Obietnica                    1           2001
Schmidt                      1           2002
Skąd wiesz?                  1           2010
Smaki miłości                2           2007
Batman - Początek            2           2005

Zwróć teraz uwagę na kolumnę AutorID w powyższej tabeli. Jest to tak zwany klucz obcy. Za każdym razem kiedy (na przykład przy pomocy kwerendy) będziemy chcieli wyświetlić pełne informacje o filmie (tytuł, rok produkcji, nazwisko i datę urodzenia autora) Access będzie wyświetlał dane z dwóch tabel. Wyświetlając dane z pierwszego rekordu Access wyświetli "Batman - Poczatek" i "2005" z tabeli z filmami a następnie korzystając z klucza obcego (AktorID=2) odwoła się do tabeli z aktorami gdzie wyszuka klucz podstawowy (AktorID=2) i wyświetli "Morgan Freeman" i "1937-06-01".

Jeśli jesteś użytkownikiem Excela to z pewnością zauważysz podobieństwa do funkcji WYSZUKAJ.PIONOWO (która użytkownikom Excela daje namiastkę możliwości relacyjnych baz danych).

Jak stworzyć zaprojektowaną tabelę?

Zamiast czytać o tym lepiej będzie gdy obejrzysz prezentację. Zawiera ona opis tworzenia tabeli z autorami, którą omówiłem powyżej. Prezentacja zwraca także uwagę na dodatkowe elementy jakie nie zostały wspomniane powyżej.

File:Access-tworzenie-tabeli-osoby.zip

Typy danych

Po wybraniu jakie dane (jakie pola) znajdą się tabeli trzeba przypisać im odpowiednie typy danych. Im lepiej je dobierzesz tym lepiej będzie działać Twoja baza danych.

Do dyspozycji masz następujące typy:

1. Tekst
2. Nota
3. Liczba
4. Data/Godzina
5. Waluta
6. Autonumerowanie
7. Tak/Nie
8. Obiekt OLE
9. Hiperłącze
10. Załącznik
11. Obliczeniowe
12. Kreator odnośników

Klucze podstawowe i klucze obce

Na początek przykład. W rozdziale "Jak projektować tabele" kluczem podstawowym jest pole AutorID w tabeli autorzy a kluczem obcym jest również pole AutorID tylko, że w tabeli filmy. Jak pamiętasz te dwa pola a więc klucz podstawowy i obcy pozwalają nam łączyć dane w dwóch tabelach (tworzyć relacje).

Czym zatem jest klucz podstawowy? Jest on unikalnym identyfikatorem każdego rekordu w danej tabeli. Jeśli weźmiemy pod uwagę tabelę autorzy to zwróć uwagę, że data urodzenia nie nadaje się do tego celu, gdyż wiele osób rodzi się tego samego dnia, podobnie imię i nazwisko może się powtórzyć. Żadne z tych pól nie nadaje się na klucz podstawowy, dlatego wprowadziliśmy dodatkową kolumnę z unikalnym autonumerem, która przejmie rolę klucza podstawowego.

Gdyby jednak nasza tabela zawierała dane polaków i był tam PESEL to moglibyśmy wykorzystać pole PESEL jako klucz podstawowy. Gdyby to była baza firm moglibyśmy wykorzystać na przykład pole NIP. Generalnie kluczem podstawowym może zostać każde pole które jednoznacznie identyfikuje rekord. Moje doświadczenie podpowiada jednak aby jako klucz podstawowy zawsze wykorzystywać dodatkową kolumnę typu autonumerowanie (tylko czasem jest to zbędne ale nigdy nie jest szkodliwe).

Klucz podstawowy może zostać także utworzony na więcej niż jednym polu. W poniższej tabeli, prezentującej kurs złotówki do innych walut, klucz podstawowy może zostać utworzony na parę pól Data + Waluta. Pamiętaj jednak, że nadal jest to jeden klucz składający się z dwóch pól a nie dwa odrębne klucze.

Data           Waluta     Kurs
2012-01-02     USD        3,4454
2012-01-02     EUR        4,4640
2012-01-03     USD        3,4277
2012-01-03     EUR        4,4597
2012-01-04     USD        3,4320
2012-01-04     EUR        4,4753

Z kolei klucz obcy jest polem po którym będziemy odwoływać się do innych tabel. W jednej tabeli może znajdować się nawet kilka kluczy obcych. Na przykład w tabeli z filmami mogą znaleźć się klucze AktorID, WytwórniaID, GatunekID, itp. Każdy z tych kluczy odwołuje się do innej tabeli, która z kolei zawiera odpowiedni klucz podstawowy.

Relacje

Relacje to najzwyczajniej zależności pomiędzy danymi znajdującymi się w różnych tabelach. Relacje określają w jaki sposób dane z tych tabel możemy łączyć ze sobą.

W relacyjnych bazach danych można określić trzy typy relacji:

1. Relacja jeden-do-jednego. Dość rzadko spotykana. Charakteryzuje się tym, że każdemu rekordowi z jednej tabeli odpowiada dokładnie jeden z drugiej tabeli. Obie tabele wyglądają jakby miały ten sam klucz podstawowy.
2. Relacja jeden-do-wielu. Najczęstszy typ relacji. W tym przypadku każdemu rekordowi z jednej tabeli może odpowiadać dowolna ilość rekordów (od zera w górę) z drugiej tabeli. Ten typ relacji występuje na przykład pomiędzy tabelami aktorzy i filmy. Każdemu rekordowi z tabeli aktorzy może odpowiadać dowolna liczba rekordów z tabeli "filmy". Ten typ relacji odzwierciedla "życiową relację", w której każdego dobrego autora możemy zobaczyć w wielu filmach. Ta relacja została zilustrowana w rozdziale "jak projektować tabele".
3. Relacja wiele-do-wielu. Być może czytając o relacji jeden-do-wielu pojawiła się w Twojej głowie pewna wątpliwość. Skoro jeden aktor gra w wielu filmach to przecież w drugą stronę też to działa - czyli w jednym filmie gra wielu aktorów. W tym momencie znasz już przykład relacji wiele-do-wielu. W Accessie nie występuje ten typ relacji wprost. Osiąga się go poprzez stworzenie trzech tabel pomiędzy którymi zachodzą dwie relacje jeden-do-wielu. Zagadkowe? Przeczytaj kolejny rozdział w którym zaprezentuję powyższy przykład.

Kwerendy

Kolejność stosowania operatorów

W Accessie stosowana jest następująca kolejność wykonywania działań:

1. Oliczenia wyrażeń znajdujących się w nawiasach
2. Potęgowanie (operator "^" np. dwa do potęgi trzeciej to 2^3)
3. Zmiana znaku (-)
4. Mnożenie (*) i dzielenie (/)
5. Dodawanie (+) i odejmowanie (-)
6. Łączenie (konkatenacja) ciągów znaków (&)
7. Operacje dopasowywania i porównywania ciągów (=, <>, >, <, >=, <=, like, between, is)
8. Działania logiczne w następującej kolejności: NOT, AND, OR

Działania wymienione w tym samym punkcie (np. mnożenie i dzielenie) mają ten sam priorytet a zastosowane w wyrażeniu są wykonywane w kolejności od lewej do prawej.

Makra

Makra w Accessie spełniają bardzo ważną funkcję. Na początku mogą wydawać się niepotrzebne ale później trudno bez nich żyć. Są jak wisienka na torcie ;-)
Makra pełnią dwie główne funkcje:

1. Makra pozwalają nam automatyzować prace jakie wykonujemy w Accessie. Wyobraź sobie, że aby osiągnąć pewien raport musisz najpierw wykonać 5 kwerend, które w odpowiedni sposób przygotują dane, zanim będzie mógł powstać z nich raport. Co więcej każda ze wspomnianych kwerend musi zostać uruchomiona w odpowiedniej kolejności a jej wykonanie zajmuje pewien czas - zbyt długi aby go nie zauważyć ale zbyt krótki aby zrobić sobie w tym czasie kawę. Czy nie byłoby wspaniale aby jednym kliknięciem uruchomić cały proces? To "jedno kliknięcie" to właśnie uruchomienie makra, które wykona wszystkie niezbędne, zdefiniowane wcześniej operacje, zawsze w ten sam precyzyjny sposób.
2. Poprzez automatyzację procesów, makra pozwalają nam uchronić się przed błędami jakie czas od czasu popełniamy. Łatwo przecież pominąć jeden krok analizy, szczególnie jeśli jesteśmy już spóźnieni, dzwoni telefon a końca pracy jeszcze nie widać.

Uruchamianie makr Accessa w nocy

Uruchamianie makr w nocy (bądź każdej innej porze) może być interesującym rozwiązaniem dla osób prowadzących czasochłonne cykliczne analizy lub też osób pragnących aby pewne czynności (generowanie raportu, kompaktowanie bazy) miały miejsce o określonej porze niezależnie od czynności wykonywanych przez użytkowników bazy.

Na początku musisz stworzyć makro, które wykona odpowiednie czynności. W tym przykładzie posłużymy się prostym makrem wyświetlającym jedynie komunikat informujący o wykonaniu makra (w przykładowej bazie danych nosi ono nazwę MakroCMD).

Sam Access nie posiada wbudowanego mechanizmu uruchamiania makr o wybranej porze dlatego wykorzystamy Menedżera Zadań systemu Windows oraz możliwości uruchamiania makr Accessowych z poziomu wiersza poleceń systemu operacyjnego.

Sposób uruchomienia makra z poziomu wiersza poleceń zaprezentowałem w poniższym przykładzie.

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "c:\NobleProg.mdb" /x MakroCMD
Powyższy przykład uruchamia makro o nazwie MakroCMD znajdujące się w bazie NobleProg.mdb
zapisanej w katalogu głównym dysku C:

Zanim spróbujesz je uruchomić na swoim komputerze sprawdź gdzie znajduje się plik MSACCESS.EXE na Twoim dysku i w razie potrzeby zmodyfikuj ścieżkę. Gdy ścieżka do pliku msaccess.exe oraz pliku bazy danych będzie prawidłowa możesz spróbować uruchomić makro. Jednakże w tym momencie może pojawić się drobny problem - uruchomienie makra może zostać zablokowane przez ustawienia zabezpieczeń. Stanie się tak przy domyślnych ustawieniach zabezpieczeń. Jeśli problem wystąpi konieczne będzie zmodyfikowanie zabezpieczeń Accessa.

Teraz gdy masz pewność, że ręczne uruchomienie makra kończy się sukcesem możesz przejść do konfiguracji Menedżera Zadań. W menu START wybierz polecenie Uruchom a tam wpisz poniższe polecenie.

control schedtasks