Kwerendy funkcjonalne - aktualizujące.

Kwerendy aktualizujące służą do modyfikowania danych:

• w tabelach
• w kwerendach (modyfikowalnych)
• w złączeniu tabel/ kwerend

Oto przykład (QBE) w którym ustalamy ceny wszystkich towarów na 100zł:

w języku SQL wygląda to tak:

UPDATE Towary SET Towary.cena = 100;

Problem:
Chcemy zwiększyć o 10% ceny wszystkich towarów należących do kategorii "qqq" i "www".

Próba rozwiązania problemu:

w języku SQL wygląda to tak:

UPDATE Kategorie INNER JOIN 
    (Towary INNER JOIN TowarKategorii 
       ON Towary.[id towaru] = TowarKategorii.[id towaru]) 
    ON Kategorie.[id kategorii] = TowarKategorii.[id kategorii] 
SET Towary.cena = [cena]*1.1
WHERE (((Kategorie.nazwa)="qqq")) OR (((Kategorie.nazwa)="www"));

Niestety, to wcale nie działa tak jak byśmy się tego spodziewali gdyż niektóre towary należą do obu kategorii równocześnie i operacja zwiększania o 10% jest wykonywana dwa razy !.

Zadanie 18

Zadanie 18a

Kiedy kwerenda jest "modyfikowalna" ?

Wtedy gdy kwerenda jest tak zdefiniowana że jej modyfikacje można przetransformować na modyfikacje tabel na których jest oparta. Sytuacja taka ma miejsce gdy:

• kwerenda zawiera klucze wszystkich tabel na których jest oparta
• nie ma grupowania/ funkcji agregujących
• nie ma słowa DISTINCT (opcja kwerendy "Wartości unikatowe"=NIE)

Z pomocy Access-u:
    To add, edit, or delete a record, there must be a unique index on the record in the underlying data source. If not, a "Permission denied" error will occur on the AddNew, Delete, or Edit method call in a Microsoft Jet workspace.

Zadanie 19

ZwiększyćCene (nazwa_kategorii)

chcemy zwiększyć o 10% cenę każdego towaru należącego do kategorii wymienionej w powyższej tabeli.

Wskazówka do zadania 19: Trzeba będzie użyć pomocniczej tabeli (a nie tylko pomocniczej kwerendy) z uwagi na "modyfikowalność" kwerend. Pomocniczą tabelę tworzymy kwerendą tworzącą tabele.

Kwerendy złożone c.d.

Problem "PKP":
Mamy następującą tabelę zawierającą rozkład jazdy pociągów:

RozkładJazdyPKP (p, m, g)

gdzie "p" oznacza pociąg, "m" oznacza miasto, "g" oznacza godzinę.
Tabela ta zawiera listę przystanków dla każdego pociągu.
Pociągi będziemy oznaczać liczbami: 1, 2, 3, ... miasta literami: A, B, C, ... .
Zakładamy że pociągi przejeżdżają swą trasę w ciągu jednego dnia (i nie ma pociągów nocnych).

Chcemy znaleźć wszystkie połączenia między miastami X i Y
(pod X i Y można podstawić dowolne miasta).
Chcemy zobaczyć połączenia bez przesiadek oraz z 1, 2 -przesiadkami !.

Rozwiązanie (wskazówki):

Jak znaleźć połączenia bezpośrednie ?
Aby zobaczyć wszystkie możliwe połączenia bezpośrednie wystarczy wstawić do kwerendy tabelę RozkładJazdyPKP dwukrotnie, pod nazwami (powiedzmy) RJ1 i RJ2.
Z iloczynu kartezjańskiego RJ1 i RJ2 należy wybrać tylko te wiersze które odpowiadają temu samemu pociągowi czyli :

RJ1.p = RJ2.p

oraz nie interesują nas wiersze w których "cofamy się w czasie" :

RJ1.g < RJ2.g

Z takiej kwerendy możemy wybrać interesujące nas połączenia (bezpośrednie !) między miastami X i Y przy pomocy warunku:

(RJ1.m = X) and (RJ2.m=Y)

Jak znaleźć połączenia z 1 przesiadką ?
Należy utworzyć pomocniczą kwerendę:

Przesiadki (pp, gp, m, pw, gw)

chodzi o przesiadki w mieście "m";
"pp" oznacza pociąg przyjeżdżający do miasta "m" o godz "gp",
"pw" oznacza pociąg wyjeżdżający z miasta "m" o godz "gw",
oczywiście musi być gp<gw !!!.

Następnie należy zbudować kwerendę zawierającą tabele:
    RJ1 (p, g, m) [pierwsza kopia RozkładJazdyPKP],
    Przesiadki (pp, gp, m, pw, gw)
    RJ2 (p, g, m) [druga kopia RozkładJazdyPKP]
Z iloczynu kartezjańskiego tych trzech tabel/ kwerend należy wybrać wiersze spełniające warunki:
    RJ1.p = Przesiadki.pp
    RJ1.g < Przesiadki.gp
    Przesiadki.pw = RJ2.p
    Przesiadki.gw < RJ2.g
Będą to wszystkie możliwe połączenia z dokładnie 1 przesiadką ...

Jak znaleźć połączenia z 2 przesiadkami ?
Trzeba wprowadzić dwie tabele Przesiadki w dwóch kopiach P1 i P2 ... (reszta analogicznie).

Zadanie 20

• połączenia bezpośrednie
• z 1 przesiadką
• z 2 przesiadkami

Dane tabeli RozkładJazdyPKP są przedstawione graficznie na następującym rysunku :

Zakładamy że pociąg 1 wyrusza z miasta B o godz 9:00, a pociąg 2 wyrusza z miasta A o 10:00;
każdy odcinek trasy jest pokonywany w ciągu 1 godziny (przez oba pociągi).
Pokaż wszystkie możliwości dotarcia z miasta B do E.
 

....................................................................
 

Zadanie 21

Wskazówki: Naciśnięcia guzika powinno uruchamiać makro z akcją UruchomSQL, uruchamiające następujące polecenie SQLa:
        UPDATE Zamowienia
        SET zrealizowane=true
        WHERE Zamowienia![id klienta] = Forms![Klienci]![id klienta]
zwróć uwagę że w makrze trzeba się posługiwać pełnymi "ścieżkami" do kontrolek/pól (bo w momencie pisani makra nie wiadomo w jakim kontekście będzie ono uruchamiane ...).

Możesz zobaczyć jak to działa w bazie bd04_2002.mde na formularzy "Klienci 3 <--".
Wypróbuj działanie guzika w następujący sposób:

 

Zadanie 21a

Zadanie 22

• "ilosc w magazynie" informujące ile sztuk danego towaru znajduje się w magazynie
• "ilosc progowa"; gdy ilosc sztuk spadnie poniżej ilosci progowej to należy sprowadzić ten towar

Do tabeli "Zamówienia" dodaj pole "zrealizowane" typu logicznego, informujące czy dane zamówienie zostało zrealizowane. Następnie do formularza "Klienci" dodaj guzik "Zrealizowano wszystkie zamówienia", który uaktualnia pola "zrealizowane" i "ilosc w magazynie".
Zdefiniuj też kwerendę o nazwie "Brakuje towaru" informującą magazyniera które towary należy sprowadzić.

Wskazówki: Użyj kwerendy aktualizującej i akcji OtwórzKwerendę (lub jeśli wolisz polecenia UPDATE SQL-a i akcji UruchomSQL). W tym zadaniu NIE wystarczy modyfikowanie tabeli zamówienia, niezbędne jest zdefiniowanie kwerendy (modyfikowalnej) o nazwie ZT1 złączającej tabele Zamowiania i Towary. Należy modyfikować odpowiednio pola ZT1!zrealizowane, ZT1![ilosc w magazynie]. Uwaga: nie należy dwa razy realizować tego samego zamówienia !.

Access/ język VBA.

VBA = Visual Basic for Application

Wszystkie programy pakietu Office są wyposażone w prosty język programowania VBA.

Jak eksperymentować z kodem VBA ?

Najlepiej utworzyć formularz niezwiązany (z żadną tabelą) o nazwie "Uruchamianie kodu VBA" ...

Umieścić na nim guzik (kontrolka typu "Przycisk polecenia"), a następnie:
    kliknąć prawym guzikiem myszy,
    wybrać konstruuj zdarzenie,
    wybrać konstruktor kodu.

Powinno się pojawić okienko VBE (VB Editor), a w nim procedura obsługi naciśnięcia guzika:

Private Sub Polecenie0_Click()
   ' tutaj można wpisać kod obsługujący zdarzenie "Przy kliknięciu" 
   ' kontrolki o nazwie "Polecenie0"

End Sub

Inny sposób włączania edytora VBE:

Narzędzia | Makro | Edytor Visual Basic

Uwaga: Z każdym formularzem jest związany tzw moduł klasy. Jest to plik zawierający procedury obsługi zdarzeń generowanych przez formularz i kontrolki na tym formularzu; dodatkowo moduł może zawierać zmienne dostępne dla tych procedur. W rzeczywistości moduł klasy to definicja typu obiektowego (inaczej definicja klasy). Formularz jest zmienną tego typu obiektowego (lub inaczej jest obiektem tej klasy).
 

Zadanie 23

Podstawowe konstrukcje języka programowania VBA.

Zadanie 24

    Sub Statement         ' definiowanie procedur
    Function Statement    ' definiowanie funkcji
    Dim Statement         ' deklarowanie zmiennych
    data types		  ' opis typów zmiennych
    Let Statement	  ' przypisywanie wartości do zmiennej (słowo "let" można opuścić !)
    If...Then...Else Statement  ' instrukcja if
    While...Wend Statement      ' pętla while
    For...Next Statement        ' pętla typu for
    Set Statement		' przypisywanie referencji obiektu do zmiennej

Przykłady kodu w VBA:

''' to jest tylko komentarz

''' deklaracje zmiennych i przypisywanie wartości
Dim i As Integer, moja_zmienna As Integer, str As String
i= 123
i= i + 1
moja_zmienna= 123
str= "ABC def GHI"

''' pętla for
For i = 1 To 10
   Debug.Print "i=", i
Next i

''' pętla while 
   ' tutaj robi dokładnie to samo co powyższa pętla "for"
i=1
While i <= 10
   Debug.Print "i=", i
   i= i + 1
Wend

''' instrukcja warunkowa if
Dim i as integer : i= 123
   ' instrukcje w jednej linii trzeba rozdzielać znakiem ":"
If i>10 Then
   Debug.Print "zmienna i ma wartość >10"
Else
   Debug.Print "zmienna i ma wartość <=10"
   i= i*2
   Debug.Print "teraz zmienna i ma wartość = ", i
End If

''' definiowanie procedur
Sub moja_proc (i As Integer, s As String)
   Debug.Print "uruchomiono moja_proc z argumentami i=", i, " s=", s
End Sub

''' definiowanie funkcji
Function moja_func (i As Integer, s As String) As Integer
   Debug.Print "uruchomiono moja_func z argumentami i=", i, " s=", s
   moja_func = 123
      ' w ten sposób określamy wartość funkcji
End Function

''' definiowanie funkcji (definicja rekurencyjna funkcji silnia())
Function Silnia (n As Integer) As Integer
   If n <= 1 Then
      Silnia= 1
   Else
      Silnia= n * Silnia(n-1)
   End If
End Function

Obiekty.

Z punktu widzenie użytkownika obiekt jest "zmienną złożoną" posiadającą:

• metody - są to procedury składowe obiektu, które można uruchamiać
• właściwości - są to zmienne składowe obiektu, które można czytać / zapisywać

Uruchamianie metod na rzecz obiektu:

    obiekt.metoda parametr1 parametr2 ...

Dostęp do właściwości obiektu:

    obiekt.właściowść= wartość
	' zapis
    zmienna= obiekt.właściowść
	' odczyt

Właściwość może zawierać obiekt lub kolekcję obiektów;
dostęp do elementów kolekcji:

    obiekt.kolekcja![nazwa obiektu kolekcji]
    obiekt.kolekcja("nazwa obiektu kolekcji")
    obiekt.kolekcja(nr obiektu) 
	' nr obiektu od 0 do (obiekt.kolekcja.Count-1)

Zmienne obiektowe:

Dim ob As typ_obiektowy
Set ob = wyrażenie_którego_wartością_jest_obiekt

Zmienne obiektowe to "referencje" do obiektów (inaczej odnośniki lub wskaźniki).
Polecenie "Set" przypisuje zmiennej obiektowej referencje do podanego obiektu; kilka różnych zmiennych obiektowych może odwoływać się do tego samego obiektu.

Obiekty i kolekcje obiektów tworzą pewną hierarchię obiektów.
Przykładowo aplikacja ma otwarte formularze, te formularze posiadają kontrolki, niektóre kontrolki są podformularzami, które także zawierają kontrolki itd ...

Obiekty w hierarchii identyfikujemy poprzez "ścieżki do obiektów".
Oto ścieżka identyfikująca kontrolkę [id towaru] na podformularzu Towary:

Application.Forms!Kategorie!Towary.Form![id towaru]

w przykładzie tym:

Application 
	- jest obiektem
Application.Forms 
	- jest kolekcja obiektów (otwartych w danym momencie formularzy)
Application.Forms!Kategorie 
	- jest obiektem typu Form o nazwie "Kategorie";
	  czyli jest to ścieżka która reprezentuje formularz "Kategorie"
Application.Forms!Kategorie!Towary
	- jest obiektem typu Control o nazwie "Towary";
	  jest to kontrolka/podformularz
Application.Forms!Kategorie!Towary.Form
	- odwołujemy się do właściwości Form kontrolki Towary, 
	  której wartością jest obiekt typu Form reprezentujący podformularz
Application.Forms!Kategorie!Towary.Form![id towaru]
	- odwołujemy się do kontrolki o nazwie "id towaru" 
	  na podformularzu Towary

Operacje na danych z poziomu VBA (obiekty DAO).

DAO = Data Access Objects

DAO to biblioteka obiektów (zbiór obiektów) pozwalających wykonywać operacje na danych.

Biblioteka DAO jest dostępna we wszystkich programach potrafiących używać obiektów COM (=Component Object Model). Jest dostępna m.in. w innych programach pakietu Office (w Excel-u, w Word-zie), ale także w builderze "Delphi" ...

.................................................................

W DAO można się posługiwać:

• kwerendami zdefiniowanymi w Accessie
• "czystym" SQL-em

Można także przetwarzać dane "bez kwerend", przeglądając tabele rekord-po-rekordzie (nazywam to metodą programową).

Zadanie 26

Przykłady kodu używającego DAO:

Przykład 1:

''' programowo odczytujemy tabelę

Dim rs As DAO.Recordset
   ' deklarujemy zmienną "rs" jako referencje (wskaźnik) do obiektu typu Recordset
   ' obiekt Recordset zawiera zbiór rekordów, np wynik działania kwerendy

Set rs = CurrentDb().OpenRecordset("Tabela1", dbOpenDynaset)
   ' otwieramy tabelę "Tabela1" w bieżącej bazie danych 
   ' innymi słowy: tworzymy obiekt Recordset na podstawie "Tabela1"
   ' i przypisujemy do zmiennej "rs" referencje do tego obiektu

rs.MoveFirst
   ' przejście do pierwszego rekordu w "rs"
while not rs.EOF
   Debug.Print rs![pole 1], rs![pole 2]
      ' wyswietlamy pola o nazwach [pole 1] i [pole 2] w okienku "Immediate"
   rs.MoveNext
      ' przejście do następnego rekordu w "rs"
wend

rs.MoveFirst
while not rs.EOF
   rs.Edit
   rs![pole 1] = "[" & rs![pole 1] & "]"
   rs.Update
      ' modyfikacja pola [pole 1] każdego rekordu tabeli
      ' ("&" oznacza łączenie stringów czyli konkatenacje)
   rs.MoveNext
wend

rs.MoveLast
Debug.Print rs.RecordCount
   ' obliczanie ilości rekordów w "rs" (czyli w "Tabela1")
   ' (rs.MoveLast jest niezbędne !)

rs.AddNew
rs![pole 1] = "tra la la"
rs![pole 2] = "a ku ku"
rs.Update
   ' dodajemy nowy rekord do tabeli 

Uwaga: w powyższych przykładach zamiast "Tabela1" może wystąpić:

Zadanie 26a

Przykład 2:

''' programowo uruchamiamy kwerendy funkcjonalne (aktualizujące)

dim q as DAO.QueryDef
   ' deklarujemy zmienną "q" jako referencję do obiektu QueryDef
   ' obiekt QueryDef reprezentuje kwerendę

set q = CurrentDb().CreateQueryDef("")
   ' tworzymy obiekt typu QueryDef (bez nazwy !) 
   ' oraz przypisujemy zmiennej "q" ref do tego obiektu

q.SQL = "UPDATE Tabela1 SET [pole 1]=[pole 1]*1,1 WHERE [pole 2]>10"
   ' definiujemy kwerendę funkcjonalną przy pomocy języka SQL

q.Execute
   ' wykonujemy kwerendę
Debug.Print "zmodyfikowano=", q.RecordsAffected
   ' wypisujemy ile rekordów zostało zmodyfikowanych przez ostatnie "Execute"

' --------------------------------------------------

dim q as DAO.QueryDef

set q = CurrentDb().QueryDefs![Kwerenda 1]
   ' zmienna "q" będzie ref do obiektu klasy QueryDef
   ' reprezentującego kwerendę o nazwie "Kwerenda 1"
   ' musi to być kwerenda funkcjonalna (np aktualizująca)
   ' --------------------------------------------------------------
   ' "CurrentDb().QueryDefs" to kolekcja obiektów QueryDef 
   ' czyli kwerend w bieżącej bazie danych

q.Execute
   ' wykonujemy kwerendę o nazwie "Kwerenda 1"
Debug.Print "zmodyfikowano=", q.RecordsAffected

Przykład 3:

''' wypisywanie towarów których nazwy zaczynają się na "ppp"

Dim rs As DAO.Recordset, sql As String

sql = "SELECT * FROM Towary WHERE nazwa LIKE 'ppp*'"
Set rs = CurrentDb().OpenRecordset(sql, dbOpenDynaset)

rs.MoveFirst
while not rs.EOF
   Debug.Print rs![nazwa], rs![cena], rs![id towaru]
   rs.MoveNext
wend

' --------------------------------------------------

Dim rs As DAO.Recordset, sql As String, war As String

sql = "SELECT * FROM Towary WHERE nazwa LIKE 'ppp*'"
Set rs = CurrentDb().OpenRecordset(sql, dbOpenDynaset)

war = "[id towaru] MOD 2 = 0"
   ''' dodatkowo wybieramy tylko towary które mają parzyste [id towaru]

rs.FindFirst war
While Not rs.NoMatch
     ' koniecznie trzeba użyć NoMatch a nie EOF !
   Debug.Print rs![nazwa], rs![cena], rs![id towaru]
   rs.FindNext war
Wend

"Okienka do wyszukiwania danych" - różne rozwiązania.

Zadanie 27

Wskazówki: do obsługi naciśnięcia guzika użyj makra + akcji OtwórzKwerendę; kwerenda ta powinna mieć kryterium w którym odwołuje się do kontrolki na tym właśnie formularzu (użyj wyrażenia typu: Forms![wszystkie dane - par]![szukam_kategoria], gdzie [szukam_kategoria] to nazwa kontrolki do której wpisuje się poszukiwaną kategorie ...).

Zadanie 28

Wskazówki: naciśnięcie guzika "szukaj" ma powodować uruchomienie kodu VBA powodującego:
1. zdefiniowanie kwerendy na podstawie danych na formularzu (metoda CreateQueryDef())
2. następnie otwarcie tej kwerendy; do otwarcia formularza użyj: DoCmd.OpenQuery "nazwa kwerendy".

Przykładowy kod:

UWAGA: obiekt DoCmd i metody które nazywają się tak jak akcje (tylko w j. ang.) służą do uruchamiania akcji z poziomu VBA !.
 

Zadanie 29

Wskazówki: naciśnięcie guzika "szukaj" ma powodować uruchomienie kodu VBA powodującego:
1. zdefiniowanie kwerendy na podstawie danych na formularzu (metoda CreateQueryDef())
2. następnie otwarcie tej kwerendy; do otwarcia formularza użyj: DoCmd.OpenQuery "nazwa kwerendy".

Pkt 1 będzie nieco bardziej skomplikowany niż w zadaniu poprzednim; jak to najłatwiej zaprogramować ... ?

Zadanie 30

czy jedyny sposób aby to zrobić to formularz oparty na tabeli Kategorie,
zawierający podformularz oparty na kwerendzie TK1
(złączającej tabele: Towary, TowaryKategorii, Kategorie)
???

Odp: NIE; można to zrobić prościej:

1. utworzyć formularz oparty na TK1
2. dodać do niego kontrolkę niezwiązaną "szukana_kat", w której wpisujemy kategorię
3. napisać procedurę obsługującą zdarzenie "Po aktualizacji"  kontrolki szukana_kat;

proc ta powinna zdefiniować i uaktywnić filtr wybierający z TK1 tylko rekordy wybranej kategorii (właściwości Filter i FilterOn obiektu Form)

Innymi słowy: programowo założyliśmy filtr.
Procedura obsługi z pkt 3 powinna wyglądać tak:

Private Sub szukana_kat_AfterUpdate()
  If Me.szukana_kat <> "" Then
    Me.Filter = "[nazwa kategorii]='" & Me.szukana_kat & "'"
      ' jest to fraza WHERE polecenia SELECT SQL-a
    Me.FilterOn = True
      ' definiujemy i uaktywniamy filtr
  Else
    Me.FilterOn = False
  End If
  Me.Requery
    ' odświeżamy "recordset" na którym jest oparty formularz
End Sub

(Co należy zrobić w zadaniu 30.)
Utwórz następujący formularz:

Można w nim wprowadzać kategorie na 2 sposoby:

1. wpisując "ręcznie" nazwę kategorii

(powyższa procedura dotyczy tego przypadku)
2. wybierając kategorię z listy

(w tym wypadku mamy pole kombi; ponadto używamy [id kategorii] zamiast [nazwa kategorii])

Po wpisaniu kategorii do kontrolki (i naciśnięciu enter) lub wybraniu kategorii z listy powinny być dostępne wyłącznie towary należące do wybranej kategorii.

Procedura obsługi danej kontrolki do wpisywania kategorii powinna też zerować drugą kontrolkę
(Me!szukana_kat_kombi = Null).

Po wpisaniu pustej wartości do jednej z kontrolek powinny być dostępne wszystkie towary.

Uwaga: filtr dla formularza można też ustawić "ręcznie" poprzez menu

Rekordy | Filtr | ...

(Koniec zadania 30)