Bazy danych - MS Access®

1. Wykłady

1. Elementy SZBD MS Access i ich zadania

1. Bazy danych: czym są i jak działają

Baza danych jest zbiorem informacji związanych z pewnym tematem lub zadaniem - na przykład analizą zamówień klientów lub ewidencją kolekcji nagrań. Jeśli baza danych nie jest przechowywana na komputerze lub też na komputerze są przechowywane tylko jej pewne części, to trzeba poświęcić dużo pracy na organizowanie i koordynowanie informacji pochodzących z bardzo różnych źródeł.

Za pomocą programu Microsoft Access można zarządzać wszystkimi niezbędnymi informacjami używając jednego pliku bazy danych. W takim pliku dane można dzielić na odrębne części zwane tabelami. Za pomocą formularzy elektronicznych można oglądać, dodawać i aktualizować dane w tabelach; za pomocą kwerend można znajdować i pobierać tylko te dane, które są rzeczywiście aktualnie niezbędne; za pomocą raportów można analizować i drukować dane w określonym układzie.

Najlepiej jest przechowywać dane tworząc po jednej tabeli dla każdego typu informacji, które mają być analizowane. Dane z różnych tabel można później łączyć razem w kwerendach, formularzach lub raportach - należy w tym celu zdefiniować relacje między tabelami.

Jeśli chcesz znaleźć i pobrać tylko te dane, które spełniają pewne warunki, utwórz kwerendę. Kwerenda może obejmować dane pochodzące z różnych tabel, może aktualizować lub usuwać wiele rekordów jednocześnie, pozwala także na wykonywanie obliczeń na danych, przy czym obliczenia te mogą być zarówno wbudowane (istniejące od razu w programie), jak i niestandardowe (definiowane przez użytkownika).

Jeśli chcesz łatwo wyświetlać, wprowadzać lub zmieniać dane w tabelach, utwórz formularz. Przy otwieraniu formularza, program Microsoft Access pobiera dane z jednej lub więcej tabel i wyświetla je na ekranie. Układ danych na ekranie możesz zarówno wybrać z Kreatora formularzy, jak i zdefiniować samodzielnie od początku.

Jeśli chcesz analizować swoje dane lub zaprezentować je w postaci drukowanej, utwórz raport. Możesz na przykład wydrukować raport grupujący dane i obliczający podsumowania, a następnie inny raport, zawierający dane wykorzystywane w nagłówkach korespondencji.

Ze wszystkimi obiektami bazy danych MS Access pracujesz używając okna bazy danych. Kliknij kartę (np. Tabele), aby zobaczyć listę dostępnych obiektów tego typu. Używając przycisków na prawo od listy, możesz otworzyć lub modyfikować istniejące obiekty lub tworzyć nowe.

2. Tabele

Tabela jest zbiorem danych dotyczących określonego tematu, jak produkty i dostawcy. Używanie oddzielnych tabel dla każdego tematu eliminuje duplikowanie danych (redundancję) co czyni przechowywanie danych bardziej efektywnym i eliminuje błędy wprowadzania danych. Tabele organizują dane w kolumnach (zwanych polami) i w wierszach (zwanych rekordami).

W widoku "Arkusz danych" można dodawać, edytować lub wyświetlać dane znajdujące się w tabeli. Można także sprawdzać pisownię, drukować dane tabeli, filtrować lub sortować rekordy, zmieniać wygląd arkusza danych oraz zmieniać strukturę tabeli dodając lub usuwając kolumny.

W widoku "Projekt" można tworzyć całą tabelę od początku, a także dodawać, usuwać i zmieniać pola istniejącej tabeli.

3. Kwerendy

Istotą baz danych jest stworzenie możliwości efektywnego wyszukiwania informacji. Do tego celu służą kwerendy (ang. query), są one obok tabel zawierających dane podstawowymi obiektami definiującymi schematy wyszukiwania pożądanej informacji. W MS Access istnieją dwa języki definiowania kwerend, język SQL (ang. Structured Query Language) oraz język QBE (ang. Query By Example).

Kwerendy pozwalają odpowiedzieć na pytania na temat danych w celu uzyskania określonych informacji z tabel i różnorodnego zmieniania wybranych tabel. Właściwie możliwości przeprowadzania zapytań są głównym powodem posługiwania się aplikacjami zarządzającymi bazą danych. Zapytania umożliwiają oglądanie danych we właściwej kolejności. Umożliwiają również przeprowadzanie obliczeń na podstawie zebranych danych w celu tworzenia źródła danych dla formularzy, raportów i innych kwerend i aby wprowadzić zmiany globalne w tabelach i tworzyć nowe tabele. Przy uruchamianiu większości kwerend lub stosowaniu filtrów, Access zbiera w zestawieniu dane, o które pytamy. Chociaż to zestawienie wygląda i działa jak tabela, jest teraz dynamicznym, "żywym" widokiem jednej lub więcej tabel.

Najczęściej spotykanym rodzajem kwerendy jest kwerenda wybierająca. Pobiera ona dane z jednej lub więcej tabel, używając podanych kryteriów, a następnie wyświetla wybrane dane w żądanym porządku.

Kwerendę można utworzyć używając kreatora lub korzystając ze szkicu w widoku Projekt kwerendy. W widoku Projekt, przez dodanie tabel zawierających dane, określa się z jakimi danymi chcemy pracować i wypełnia siatkę projektu.

4. Formularze

Formularzy można używać w wielu zastosowaniach: do wprowadzania danych do tabeli, do otwierania innych formularzy i raportów, do tworzenia okienek dialogowych i in. Większość informacji zawartych w formularzu pochodzi z odpowiedniego źródła rekordów. Pozostałe informacje są przechowywane w projekcie formularza.

Połączenie między formularzem i jego źródłem rekordów jest realizowane za pomocą obiektów graficznych zwanych formantami. Najczęściej spotykanym typem formantu, stosowanym do wyświetlania i wprowadzania danych, jest pole tekstowe.

5. Raporty

Raport jest wygodnym sposobem prezentacji danych w postaci wydrukowanej. Użytkownik ma pełną kontrolę nad rozmiarem i wyglądem wszystkich elementów raportu, dzięki czemu może wyświetlać dane w żądany sposób.

Większość informacji zawartych w raporcie pochodzi z odpowiedniego źródła rekordów: tabeli, kwerendy lub wyrażenia SQL. Pozostałe informacje są przechowywane w projekcie raportu.

Używając graficznych obiektów zwanych formantami tworzymy łącze między raportem a jego źródłem danych. Formanty mogą być polami tekstowymi, które wyświetlają nazwy i numery, etykietami, które wyświetlają tytuły i liniami, które graficznie organizują dane i powodują, że raport staje się bardziej atrakcyjny wizualnie.

6. Makra

Makro jest akcją lub zestawem akcji, z których każda wykonuje określoną operację, taką jak otwarcie formularza lub wydrukowanie raportu. Makra pomagają zautomatyzować często wykonywane zadania. Można na przykład uruchamiać makro drukujące raport, gdy użytkownik klika przycisk polecenia.

Makro może być jednym makrem złożonym z sekwencji akcji lub może być grupą makr. Można również użyć wyrażenia warunkowego, aby określić, czy w pewnych sytuacjach akcja będzie wykonywana podczas wykonywania makra.

1. Sekwencja makr

Poniższe makro składa się z serii akcji. Program Microsoft Access wykonuje te akcje za każdym razem, gdy wykonywane jest makro. Aby uruchomić to makro należy się odwołać do nazwy makra „PrzeglądProduktów”.

2. Grupa makr

Mając wiele makr, pogrupowanie powiązanych ze sobą makr w grupy makr może pomóc w zarządzaniu bazą danych. Aby wyświetlić nazwy makr dla grupy makr, należy kliknąć polecenie Nazwy makr w menu Widok okna "Makro".

Na przykład, poniższa grupa makr o nazwie „Przyciski” składa się z trzech powiązanych ze sobą makr: „Pracownicy”, „Produkty” i "Reps". Każde z makr wykonuje akcję OtwórzFormularz (OpenForm), a makro „Produkty” wykonuje dodatkowo akcję RuchRozmiar (MoveSize).

Nazwa w kolumnie Nazwa makra identyfikuje każde makro. Gdy makro jest wykonywane w grupie makr, program Microsoft Access wykonuje odpowiadającą temu makru akcję z kolumny akcji, a następnie kolejne akcje, dla których kolumna Nazwa makra jest pusta.

Aby uruchomić makro z grupy makr, należy podać nazwę grupy i po kropce nazwę makra. Aby odwołać się do makra „Pracownicy” z grupy makr „Przyciski” z poprzedniego przykładu, należy wpisać Przyciski.Pracownicy

3. Akcje warunkowe

Aby wyświetlić kolumnę Warunek, należy kliknąć polecenie Warunki z menu Widok w oknie "Makro". Poniższe makro uruchamia akcje OknoKomunikatu (MsgBox) i ZatrzymajMakro (StopMacro) tylko wtedy, gdy warunek z kolumny Warunek jest prawdziwy (gdy wartością w polu „IDdostawcy” jest Null).

7. Moduły

Moduł jest zbiorem deklaracji i procedur języka Visual Basic for Applications przechowywanych razem jako jedna całość.

Istnieją dwa podstawowe typy modułów: moduły klasy i moduły standardowe. Każda procedura w module może być procedurą typu Function lub procedurą typu Sub.

1. Moduły klasy

Moduły formularza i moduły raportu są modułami klasy związanymi z określonym formularzem lub raportem. Moduły formularza i raportu często zawierają procedury zdarzeń, które są uruchamiane w odpowiedzi na zdarzenie formularza lub raportu. Procedur zdarzeń można używać do sterowania działaniem formularzy i raportów oraz ich odpowiedziami na akcje użytkownika, takie jak np. kliknięcie przyciskiem myszy przycisku polecenia.

Gdy użytkownik po raz pierwszy tworzy procedurę zdarzenia dla formularza lub raportu, program Microsoft Access automatycznie tworzy związany z nią moduł formularza lub raportu. Aby zobaczyć kod modułu dla formularza lub raportu, należy kliknąć przycisk Kod na pasku narzędzi w widoku "Projekt" formularza lub raportu.

Procedury w modułach formularza i raportu mogą wywoływać procedury dodane do modułów standardowych.

W programie Microsoft Access 95 moduły klasy istnieją tylko w powiązaniu z formularzem lub raportem. W programie Microsoft Access 97 moduły klasy mogą również istnieć niezależnie od formularza lub raportu. Ten rodzaj modułu klasy jest wyświetlony na karcie Moduły w oknie bazy danych. Modułu klasy z karty Moduły można użyć do utworzenia definicji obiektu niestandardowego.

2. Moduły standardowe

Moduły standardowe zawierają ogólne procedury, które nie są związane z żadnym innym obiektem, oraz często używane procedury, które można uruchamiać z dowolnego miejsca w bazie danych

Listę modułów standardowych w bazie danych można obejrzeć klikając kartę Moduły w oknie bazy danych. Lista modułów formularzy, raportów oraz modułów standardowych jest również wyświetlana w Przeglądarce obiektów.

8. Zdarzenia: Współdziałanie obiektów bazy danych

Zdarzenie jest określoną akcją, która występuje dla pewnego obiektu lub w związku z nim. Program Microsoft Access może odpowiedzieć na wiele różnych zdarzeń: kliknięcie przyciskiem myszy, zmianę danych, otwarcie lub zamknięcie formularza i inne. Zdarzenia są zazwyczaj wynikiem akcji użytkownika.

Używając procedury zdarzenia lub makra można dodać własną, niestandardową odpowiedź na zdarzenie zachodzące dla formularza, raportu lub formantu.

Przypuśćmy, że użytkownik chce, aby formularz „OpisyProduktów” otwierał się po kliknięciu przycisku polecenia „Szczegóły Produktów” na formularzu „Zamówienia”. W poniższych przykładach zostało pokazane, jak to zrobić za pomocą procedury zdarzenia lub makra.

1. Odpowiadanie na zdarzenie Click za pomocą procedury zdarzenia

Gdy użytkownik tworzy procedurę zdarzenia dla obiektu, program Microsoft Access dodaje szablon procedury zdarzenia określony dla danego zdarzenia i obiektu do modułu formularza lub modułu raportu. Wszystko, co musi zrobić użytkownik, to dodać kod odpowiadający w określony sposób, gdy dane zdarzenie występuje dla formularza lub raportu.

2. Odpowiadanie na zdarzenie „Przy kliknięciu” (Click) za pomocą makra

Aby w odpowiedzi na zdarzenie uruchamiane było makro, należy otworzyć arkusz właściwości dla formularza, raportu lub formantu, znaleźć właściwość odpowiadającą zdarzeniu i ustawić ją na makro, które ma być uruchamiane.

1. Podstawowe pojęcia

• rekord

Zbiór danych o osobie, miejscu, zdarzeniu lub dowolnym innym elemencie. W widoku "Arkusz danych" tabeli, kwerendy lub formularza rekord jest reprezentowany jako wiersz.

• pole (kolumna)

Element tabeli, w którym jest zapisana określona informacja, na przykład nazwisko. Pole jest reprezentowane przez kolumnę lub komórkę w arkuszu danych. W formularzu, do wyświetlania danych z pola można użyć formantu, na przykład pola tekstowego.

• pole obliczeniowe

Pole zdefiniowane w kwerendzie, które służy do wyświetlania wyniku pewnego wyrażenia, a nie do przechowywania danych. Wartość w polu jest obliczana za każdym razem, gdy wartości w wyrażeniu ulegną zmianie. Formant obliczeniowy jest formantem w formularzu lub raporcie, który również służy do wyświetlania wyniku wyrażenia, a nie do przechowywania danych.

• formant

Obiekt graficzny, taki jak pole tekstowe, pole wyboru, przycisk polecenia lub prostokąt, który można umieścić na formularzu lub raporcie w widoku "Projekt" w celu wyświetlenia danych, wykonania akcji lub w celu ułatwienia korzystania z formularza lub raportu.

• Null

Wartość wskazująca, że w danym polu brak jest danych lub są one nieznane. Wartości Null można używać w wyrażeniach. Wartości Null mogą być wprowadzane w polach, dla których informacje są nieznane oraz w wyrażeniach i kwerendach. W kodzie języka Visual Basic słowo kluczowe Null oznacza wartość Null. Niektóre pola, na przykład pola określone jako zawierające klucz podstawowy, nie mogą zawierać wartości Null.

• klucz podstawowy (główny) tabeli
  

Jedno lub więcej pól, których wartości jednoznacznie identyfikują każdy rekord w tabeli. Klucz podstawowy jest używany w relacji do odwoływania się do określonych rekordów w jednej tabeli z innej tabeli. Klucz podstawowy jest nazywany kluczem obcym, gdy występują odwołania do tego klucza z innej tabeli.

• klucz obcy

Jedno lub więcej pól tabeli, które odwołują się do pola klucza podstawowego lub pól w innej tabeli. Klucz obcy wskazuje sposób, w jaki tabele są powiązane - dane w polach klucza obcego i klucza podstawowego muszą być zgodne. Na przykład tabela "Produkty" w przykładowej bazie danych Northwind zawiera klucz obcy "IDdostawcy", który odwołuje się do klucza podstawowego "IDdostawcy" tabeli "Dostawcy". Dzięki tej relacji w tabeli "Produkty" dla każdego produktu jest wyświetlana nazwa dostawcy z tabeli "Dostawcy".

• indeks

Funkcja programu Microsoft Access przyspieszająca przeszukiwanie i sortowanie tabeli. Klucz podstawowy tabeli jest indeksowany automatycznie. Pola, których dane są typu Memo, Hiperłącze lub Obiekt OLE, nie mogą być indeksowane.

• relacja

Związek ustanowiony między wspólnymi polami (kolumnami) w dwóch tabelach. Wśród relacji można wyróżnić relacje typu jeden-do-jednego, jeden-do-wielu oraz wiele-do-wielu.

• fokus

Możliwość przyjmowania informacji wejściowych ustawiana za pośrednictwem myszy, klawiatury lub metody SetFocus. Tylko jeden element może w danej chwili mieć fokus. Na przykład, wprowadzany tekst pojawi się w polu tekstowym tylko wtedy, gdy pole tekstowe ma fokus.

• filtr

Zestaw kryteriów stosowanych do rekordów w celu ich posortowania lub pokazania pewnego podzbioru rekordów. W programie Microsoft Access występują cztery typy filtrów: Filtruj według wyboru, Filtruj według formularza i Zaawansowany filtr/ sortowanie i Filtruj dla wyrażenia.

2. Tabele, podział danych, szczegółowość rekordu

W projekcie prostej bazy danych do ewidencjonowania faktur sprzedaży dane zawarte na każdej fakturze zapiszemy jako pojedynczy wiersz (rekord) w tabeli. Poszczególne kategorie danych (numer faktury, data wystawienia, odbiorca...) stanowić będą pola tej tabeli.

Jednak każda faktura rzadko dotyczy tylko jednego rodzaju towaru. Trzeba więc w pola Nazwa towaru, Cena netto, Stawka VAT i Ilość wpisywać po kilka wartości. Tabela będzie zawierała tyle wierszy, ile jest towarów na fakturze. Powoduje to jednak konieczność wielokrotnego wpisywania danych wspólnych dla każdego towaru, jak: Numer faktury, Nazwa odbiorcy, Adres odbiorcy, NIP odbiorcy itp., gdyż wiele faktur znajduje się w tej samej tabeli.

Każde powtórzenie tej samej informacji jest nieefektywne. Redundancja danych (niepotrzebne powtarzanie danych w tabeli czy w bazie danych) powiększa objętość bazy, zmniejsza efektywność obsługi, powoduje zagrożenie nieuchronnymi błędami przy wprowadzaniu danych. Wielokrotne powielanie danych np. adresowych klienta stwarza problemy z ich aktualizacją.

Sprawą zupełnie podstawową jest wymóg, aby pojedyncze pole tabeli zawierało informację elementarną, jednostkową.

Warto więc zwrócić uwagę na to, że pole Adres odbiorcy zawiera zarówno nazwę miejscowości, nazwę ulicy i numer domu. Rozdzielenie tych informacji pozwoliłoby na wyszukiwanie odbiorców według nazw miejscowości, a także np. automatyczne skojarzenie z numerem kierunkowym.

Dane dotyczące odbiorców powinniśmy umieścić w osobnej tabeli, np. Odbiorcy a dane z faktur w tabeli Sprzedaż. Teraz wystarczy, aby każdy rekord w tabeli Sprzedaż zawierał pole identyfikujące odbiorcę (tzw. klucz obcy).

Nie może to jednak być pole Nazwa odbiorcy. Prawo pozwala bowiem na istnienie dwóch firm o tej samej nazwie, byle tylko miały one siedziby w różnych miejscowościach. Zamiast jednak używać kombinacji pól Nazwa odbiorcy i Miejscowość jako klucza głównego w tabeli Odbiorcy, warto każdemu klientowi przypisać jednoznaczny identyfikator, np. skrót nazwy wraz z kodem cyfrowym albo po prostu numer kolejny w tabeli (autonumer). Tego samego pola można także użyć jako identyfikatora w tabeli Sprzedaż.

1. Typy danych w tabelach

1. Tekst

Pól typu Tekst należy używać do przechowywania takich danych, jak nazwy czy adresy oraz danych liczbowych, na których nie będą dokonywane żadne obliczenia (na przykład numery telefonów, numery części, czy kody pocztowe). Pole tekstowe może zawierać do 255 znaków. Domyślnie pola mieści 50 znaków. Aby umożliwić wprowadzenie do pola większej ilości danych, należy zmienić ustawienie właściwości Rozmiar pola.

2. Memo

Pola typu Memo są używane do przechowywania danych dłuższych niż 255 znaków. Pole Memo może zawierać do 64 000 znaków. Pola Memo nie mogą być indeksowane ani sortowane. Do przechowywania sformatowanych tekstów i długich dokumentów, zamiast pola Memo należy użyć pola OLE.

3. Liczba

Używany dla danych liczbowych, które mogą być używane w obliczeniach matematycznych

4. Data/Godzina

Używany dla dat i godzin.

5. Walutowy

Używany dla wartości pieniężnych.

6. Autonumer

Typ danych pola przechowujący unikatową liczbę automatycznie przyporządkowaną każdemu rekordowi w chwili jego dołączenia do tabeli. Można generować trzy rodzaje liczb: kolejne (rosnące o 1), losowe oraz identyfikator replikacji (używany przez Menedżera replikacji w celu zapewnienia integralności danych w replikowanej bazie danych). Liczby generowane przez pole typu Autonumer nie mogą być usuwane ani modyfikowane.

7. Tak/Nie

Właściwość Format można ustawić na jeden z predefiniowanych formatów: Tak/Nie, True/False lub Wł/Wył lub na format niestandardowy

8. Obiekt OLE

Używany dla obiektów, takich jak np. dokumenty programu Microsoft Word i rysunki, utworzonych w innych programach przy użyciu protokołu OLE.

9. Hiperłącze

Kolorowy i podkreślony tekst lub grafika, który można kliknąć, aby skoczyć do pliku, określonego miejsca w pliku, strony HTML w sieci WWW lub strony HTML w internecie. Hiperłącza mogą również prowadzić do miejsc Gopher, Telnet, grup dyskusyjnych i FTP.

2. Kryteria wyboru w kwerendzie - przykłady możliwości wyszukiwania i sortowania rekordów

Operator	Znaczenie operatora	Przykład	Znaczenie przykładu
Operatory porównawcze
=	Równość	=nowak	tylko nazwisko nowak
>	Większe niż	>5000
<	Mniejsze niż	<98/05/15	mniejsze niż (wcześniej niż) 15 maja 1998
>=	większe lub równe	>=M	większe lub równe literze M
<=	mniejsze lub równe	<=98/07/02	wcześniejsze lub równe 15 maja 1998
<>	nie równe	<>PL	nie równe PL
Between	między dwoma wartościami (włącznie)	Between 15 and 25	liczba od 15 do 25
In	wewnątrz ustawienia lub spisu wartości	In(PL, GB, USA)	PL, GB, lub USA
Is Null	pole jest puste	Is Null	zapisy, które nie mają wartości w polu
Is Not Null	pole nie jest puste	Is Not Null	zapisy, które mają wartość w polu
Like	określa wzorzec	Like ma*	zapisy rozpoczynające się literami „ma” z dowolnymi znakami po tym wyrażeniu (patrz znaki specjalne w tej tabeli)
Operatory logiczne
And	obie wartości są prawdziwe	>=1 And <=10	między 1 i 10
Or	jedna z wartości jest prawdziwa	PL or USA	albo PL, albo USA
Not	nieprawda	Not Like ma???	zapisy które nie rozpoczynają się literami „ma” z trzema innymi znakami
Znaki specjalne
?	pojedynczy znak	8?-791	kod pocztowy z dowolnym drugim znakiem
*	znaki	(052)*	tekst, który zaczyna się na (052), np.: nr telefonu lub faksu
[nazwa pola]	inne pola z kwerendy	<[StanMagazynu]	zapisy, w których ta wartość pola jest mniejsza od wartości w polu „StanMagazynu”

Określanie kryteriów wyboru:

• w polu Tak/Nie można wprowadzić -1, Tak, True dla wartości „Tak”; 0, Nie, False dla „Nie”

• Operatory są dowolne. Jeśli operator zostanie pominięty, przyjmowane jest, że oznacza to równość (=).

• Date() oznacza aktualną datę

• Now() oznacza „teraz”, czyli aktualną datę i godzinę

15

---