1 Tworzenie rozwiązań baz danych w Visual Basic for Applications

Tworzenie rozwiązań baz danych w Visual Basic for Applications
dla MS Access 2010
Dr inż. Dariusz Mrozek
(na podstawie materiałów firmy Microsoft)
Microsoft Access jest zintegrowanym systemem do tworzenia rozwiązań w zakresie
gromadzenia, przetwarzania i prezentacji danych. W systemie tym mamy możliwość łatwego
tworzenia baz danych, utrzymywania ich spójności, odpytywania i modyfikowania tabel bazy
przy pomocy kwerend, a także zbudowania warstwy prezentacji w postaci formularzy,
raportów i stron internetowych udostępniających dane w strukturalizowany sposób.
Dodatkowo mamy możliwość oprogramowania zbudowanej aplikacji przy pomocy makr
zawierających uporządkowany zestaw predefiniowanych poleceń, takich jak np. otwarcie
formularza, oraz przy pomocy procedur i funkcji napisanych w języku Visual Basic for
Access.
Wszystkie te cechy sprawiają, że Microsoft Access staje się doskonałym narzędziem do
tworzenia prostych baz danych w małych i średnich przedsiębiorstwach, bez ponoszenia
wysokich kosztów związanych z zakupem wysoko wyspecjalizowanego oprogramowania do
tworzenia systemów opartych na bazach danych.
Architektura systemu
Przetwarzanie danych w systemie MS Access 2010 jest realizowane przez motor bazy danych
Microsoft Access Database Engine (ACE Engine), który zastąpił znany z poprzednich wersji
Microsoft JET (Microsoft Joint Engine Technology). Na rys. 1 zaprezentowano, w jaki
sposób Access (UI) oraz ACE (engine) tworzą kompletny system zarządzania bazą danych
(SZBD).
Rys. 1 Ogólna architektura MS Access 2010
Interfejs użytkownika Access UI jest odpowiedzialny za sposób, w jaki użytkownicy
przeglądają, edytują i używają danych przy pomocy formularzy, raportów, zapytań, makr oraz
szeroko rozwiniętych kreatorów.
Z kolei motor ACE Engine udostępnia szereg usług typowych dla systemu zarządzania
bazą danych, tj.:
1
− fizyczne przechowywanie danych w systemie plików,
− możliwość definiowania, modyfikowania i usuwania obiektów bazy danych – tabel
i ich kolumn,
− utrzymywanie spójności danych – utrzymywanie więzów referencyjnych
zapobiegających pojawianiu się błędów w danych,
− manipulację danymi – dodawanie, edycję i usuwanie danych,
− pobieranie danych i wykonywanie poleceń SQL,
− szyfrowanie danych zapobiegające przed nieuprawnionym dostępem do
informacji,
− współdzielenie danych w sieciowym środowisku pracy wielu użytkowników,
− publikowanie danych w sieci Internet,
− import, eksport danych oraz możliwość dołączenia zewnętrznych źródeł danych.
Technologie przetwarzania danych w MS Access 2010
Microsoft udostępnia kilka technologii umożliwiających pracę z bazami MS Access. Do
oprogramowywania baz danych użytkownicy mogą użyć różnych interfejsów programowania
aplikacji API oraz warstw dostępowych do danych, m.in.:
− Data Access Objects (DAO)
− Object Linking and Embedding, Database (OLE DB)
− ADO.NET
− ActiveX Data Objects (ADO)
− Open Database Connectivity (ODBC)
Podczas dostępu do danych z poziomu kodu programiści mają możliwość użycia
którejkolwiek z wymienionych technologii, w zależności od bieżących potrzeb. Zależy to
w dużej mierze od preferowanego języka programowania, używanej platformy, spodziewanej
funkcjonalności, aspektów bezpieczeństwa, wydajności i późniejszej konserwacji. Jeśli na
przykład aplikacja ma współpracować wyłącznie z bazami danych Accessa, wówczas
naturalnym wydaje się użycie technologii DAO, ponieważ dostarcza ona najbardziej
wszechstronnej funkcjonalności. Z drugiej strony, jeżeli aplikacja ma być rozwijana przy
użyciu języka C# wówczas najlepszym wyborem wydaje się być ADO.NET.
W dalszej części skoncentrujemy się na technologii DAO, która jest natywną technologią
dostępu do baz danych MS Access.
Programowanie w Visual Basic for Applications dla MS Access 2010
Visual Basic for Applications (VBA) jest językiem programowania dla aplikacji
Microsoft Office opartym na języku Visual Basic. VBA zawiera w swojej składni większość
instrukcji znanych z innych języków programowania, przy czym pisany kod jest bardzo
przejrzysty i przyjazny.
2
UWAGA: Na potrzeby laboratorium Baz danych I podstawowe składnie instrukcji języka
VBA zostały omówione w opracowaniu Materiały do laboratorium MS ACCESS BASIC dr
inż. Katarzyny Harężlak.
Poniżej zaprezentowano kilka sposobów, w jaki programiści mogą przetwarzać dane
z bazy danych MS Access przy pomocy języka VBA.
Podstawową klasą obiektów używanych do operowanie na rekordach tabeli z poziomu
VBA jest klasa Recordset. Reprezentuje ona zwykle pojedynczą tabelę z bazy danych lub
tabelę utworzoną przez wykonanie zapytania/kwerendy.
Zanim zaczniemy przetwarzać rekordy należy najpierw obiekt Recordset otworzyć
i ustawić. Otwarcie i ustawienie obiektu Recordset można osiągnąć np. przy pomocy metody
OpenRecordset wywołanej na rzecz obiektu bazy danych lub metody RecordserClone
wywołanej na rzecz obiektu formularza:
'deklaracja zmiennych i obiektow
Dim rs As DAO.Recordset
'otwarcie i ustawienie zbioru rekordow na podstawie tabeli Studenci
Set rs = CurrentDb.OpenRecordset("Studenci", dbOpenSnapshot)
lub
'deklaracja recordsetu
Dim rs As DAO.Recordset
'ustawienie recordsetu na podstawie rekordow z formularza
'Me – biezacy formularz
Set rs = Me.RecordsetClone
Pierwszy sposób powoduje utworzenie zbioru rekordów na podstawie rekordów z tabeli
Studenci. Drugi sposób klonuje rekordy znajdujące się na formularzu. W zależności od
potrzeb związanych z przetwarzaniem możemy użyć jednego z podanych sposobów.
Tworząc reprezentację tabeli z układem wierszowo-kolumnowym w postaci obiektów
Recordset możemy poruszać się po tak utworzonym zbiorze rekordów w różnych kierunkach,
np. od początku do końca, korzystając z metod z rodziny Move.. (MoveFirst, MoveLast,
MoveNext, MovePrevious).
Przykład 1.
Otwarcie zbioru rekordów, przejście po kolejnych rekordach w zbiorze i ich zliczenie.
'procedura obslugujaca nacisniecie przycisku na formularzu
Private Sub btnOblicz_Click()
'deklaracja zmiennych i obiektow
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim licznik As Integer
'biezaca baza danych
Set db = CurrentDb
'otwarcie i ustawienie zbioru rekordow na podstawie tabeli
Set rs = db.OpenRecordset("Studenci", dbOpenSnapshot)
3
'przejscie po rekordach w petli, az do osiagniecia konca zbioru (EOF)
rs.MoveFirst
Do Until rs.EOF
licznik = licznik + 1
rs.MoveNext
Loop
'zwrocenie wyniku do pola tekstowego Tekst3 na bieżącym formularzu
Tekst3 = licznik
End Sub
Czasami, bardziej niż spacer po wszystkich rekordach, interesuje nas wyszukanie
rekordów spełniających zadane kryteria. Możemy wówczas skorzystać z metod z rodziny
Find.. (FindFirst, FindLast, FindNext, FindPrevious).
Przykład 2.
Procedura, która na naciśnięcie przycisku Oblicz sumę oblicza sumę wypłat pracownika
zaznaczonego na formularzu.
Rys. 2 Formularz użyty w przykładzie 2
'procedura obslugujaca nacisniecie przycisku na formularzu
Private Sub btnOblicz_Click()
'deklaracja zmiennych i obiektow
Dim rs As DAO.Recordset
Dim kryt As String
Dim vSuma As Single
'ustawienie rekordsetu poprzez sklonowanie rekordow
Set rs = Me.RecordsetClone
'ustawienie kryterium wyszukiwania
'+ sczytanie nr prac z biezacego rekordu na formularzu
kryt = "NR_PRAC = " & Me.NR_PRAC
' znalezienie pierwszego rekordu spelniajacego kryterium
4
' i kolejnych w petli
' az kryterium nie zostanie spelnione (NoMatch)
rs.FindFirst kryt
Do Until rs.NoMatch
' sczytanie pola KWOTA z biezacego rekordu i dodanie
vSuma = vSuma + rs!KWOTA
rs.FindNext kryt
Loop
'ustawienie pola na formularzu
txtSuma = vSuma
End Sub
Kryteria przekazywane do funkcji Find.. mają charakter łańcuchów tekstowych String
i z wyglądu przypominają zwykle warunki filtrujące klauzuli WHERE polecenia SELECT
w języku SQL. Np.:
'szukamy pracownika o nr = 5
"NR_PRAC = 5"
lub
'szukamy pracownika o nazwisku Kowalski
"NAZWISKO = 'Kowalski'"
Czasem jednak wartość dla skonstruowania kryterium jest przekazywana poprzez zmienną
lub parametr i wówczas należy ‘skleić’ ją z łańcuchem kryterium, jak to pokazano
w poniższych przykładach i przykładzie 2.
'szukamy pracownika o nr = wartosci zmiennej
"NR_PRAC = " & vNr
lub
'szukamy pracownika o nazwisku przekazanym w zmiennej
"NAZWISKO = '" & vNazw & "'"
Przydatną właściwością zbiorów rekordów jest właściwość Bookmark. Pozwala ona
zapamiętać położenie rekordu w zbiorze np. w pomocniczej zmiennej i później powrócić do
tego rekordu. Właściwość Bookmark zwraca bowiem unikalną wartość dla każdego rekordu
w zbiorze. Co więcej, jeśli do utworzenia zbioru rekordów użyto metody klonowania, to
wartości Bookmark dla rekordów formularza i rekordsetu są identyczne i mogą być używane
zamiennie lub służyć do synchronizacji zbiorów, jak w poniższym przykładzie.
Przykład 3.
Synchronizacja zbiorów. Procedura, która na naciśnięcie przycisku Pokaż kwotę
synchronizuje zbiory i wyświetla kwotę dla zaznaczonego na formularzu rekordu.
(Oczywiście można to zrobić prościej ☺, ale to już innym razem)
5
Rys. 3 Formularz użyty w przykładzie 3
Private Sub btnShow_Click()
'deklaracja zmiennych i obiektow
Dim rs As DAO.Recordset
Dim vKwota As Single
'ustawienie rekordsetu poprzez sklonowanie rekordow
Set rs = Me.RecordsetClone
'synchronizacja zbiorow
rs.Bookmark = Me.Bookmark
'sczytanie kwoty
If Not rs.EOF Then
vKwota = rs!KWOTA
End If
'wyswietlenie komunikatu
MsgBox vKwota
End Sub
6