Kwerenda dołączająca w widoku SQL

Kwerenda dołączająca w kodzie SQL zaczyna się zawsze od słów INSERT INTO ….
Pełen kod SQL takiej kwerendy można podejrzeć w widoku SQL projektu kwerendy:

kliknij, aby powiększyć

Wygląda to np. tak:

INSERT INTO TabelaArchiwum ( NumerKatalogowy, Autor, Tytul, Cena, Dzial, Bestseller, DataArchiwizacji )
SELECT TabelaKsiazki.NumerKatalogowy, TabelaKsiazki.Autor, TabelaKsiazki.Tytul, TabelaKsiazki.Cena, TabelaKsiazki.Dzial, TabelaKsiazki.Bestseller, Date() AS DataArchiwum
FROM TabelaKsiazki;

Cała procedura uruchamiająca taką kwerendę dołączającą:

Public Sub MojaProcedura()
Dim Kwera As String
Kwera = „INSERT INTO TabelaArchiwum ( NumerKatalogowy,  ” & _
„Autor, Tytul, Cena, Dzial, Bestseller, DataArchiwizacji )  ” & _
„SELECT TabelaKsiazki.NumerKatalogowy, TabelaKsiazki.Autor,  ” & _ „TabelaKsiazki.Tytul, TabelaKsiazki.Cena, ” & _
„TabelaKsiazki.Dzial, TabelaKsiazki.Bestseller, Date() AS DataArchiwum ” & _
„FROM TabelaKsiazki;”
DoCmd.SetWarnings False
DoCmd.RunSQL Kwera
DoCmd.SetWarnings True
End Sub

We wpisie na blogu  może to różnie wyglądać, ale na wszelki wypadek zwracam uwagę na łamanie linii w zapisie kodu SQL w edytorze VBA – jest to ciąg tekstowy, więc koniec linii musi być zakończony znakami & _  (w środku jest spacja).


Kurs SQL w analizie danych - zaawansowane techniki

 

Bezpieczeństwo makr

W najnowszych wersjach pakietu Office bardzo duży nacisk położono na bezpieczeństwo aplikacji. Generalnie jest to pewnie słuszny kierunek, ale w sytuacji, gdy chcemy uruchomić aplikację ze sprawdzonego i pewnego źródła, może być to uciążliwe. Coraz częściej się zdarza, że nawet umieszczenie pliku Accessa w zaufanej lokalizacji nie rozwiązuje problemu, konieczna jest jeszcze zgoda na uruchomienie makr i formatów ActiveX.

Robimy to również w ustawieniach Centrum Zaufania:

Karta Plik –> Opcje

Wybieramy opcje, a następnie przechodzimy do ustawień Centrum Zaufania:

kliknij, aby powiększyć

Po wejściu do ustawień wybieramy Ustawienia makr

kliknij, aby powiększyć

i sprawdzamy przypisaną opcję. Ja mam u siebie zaznaczoną ostatnią opcję, ale chyba najbardziej polecaną jest opcja druga. Choć trzeba się liczyć z tym, że każde otwarcie takiej bazy będzie się wiązać z wyświetleniem komunikatu i koniecznością wciśnięcia przycisku.

W kolejnym kroku wybieramy w ustawieniach Centrum Zaufania Ustawienia kontrolek ActiveX:

kliknij, aby powiększyć

Ja u siebie również  mam w tej chwili włączone wszystko, ale wiem jakie pliki Accessa otwieram i czy mogę im ufać.

 

Na koniec wystarczy zatwierdzić ustawienia przyciskiem OK. Pojawi się jeszcze tylko monit o wyłączenie i ponowne włączenie bazy i nowe ustawienia zostają zapisane.


 

Dodawanie nowego rekordu

W aplikacji Access dane są zapisywane w tabeli. Bezpośredni dostęp do tabel (zarówno w do jej projektu jak i zapisanych danych) powinien mieć jednak tylko administrator bazy. Normalny użytkownik zarówno edytować jak i dodawać/usuwać dane powinien tylko poprzez odpowiednio oprogramowane formularze.
Owszem, można normalny, oparty na kwerendzie lub tabeli formularz otworzyć w trybie dodawania, ale moim zdaniem nie jest to dobre wyjście. W takim formularzu dane trafiają od razu do tabeli, nie trzeba ich zapisywać i tracimy kontrolę nad poprawnością wprowadzanych danych. Oczywiście, można wstawić reguły poprawności, ale to, że w danym polu jakaś wartość nie da się wpisać, nie wyklucza wcale tego, że nowy rekord został dodany, choć z niepełnymi danymi. Bardzo szybko może spowodować to bałagan.

Zdecydowanie lepszym rozwiązaniem jest formularz bez źródła danych, z niezwiązanymi polami.
Na przykład dla takiej tabeli:

dobrym rozwiązaniem może być taki formularz dodawania

Warto każde pole odpowiednio sformatować i nadać własne nazwy. To ostatnie nie jest wprawdzie konieczne, ale standardowe nazwy typu Tekst123, Tekst378 utrudnią później pracę, nazwy typu TekstTytul, czy Dzial itp. zdecydowanie ułatwią odwoływanie się  do nich w kwerendach czy kodzie VBA. Można tu też wpisać wartości domyślne – np.Data przyjecia=Date() (czyli bieżąca data systemowa).

Oczywiście też przyciski Zapisz – do zapisania danych w tabeli oraz Anuluj – do zamknięcia formularza bez zapisywania.
A jak zapisać dane? Jest tu kilka sposobów. Można uruchomić np. taką kwerendę dołączającą:

kliknij obrazek, aby powiększyć kwerendę

Poszczególne pola kwerendy to np…:
Wyr1: [Formularze]![Formularz_DodajKiazke]![TAutor]
dołaczane do pola Autor i w analogiczny sposób – kolejne. Potem wystarczy uruchomić kwerendę i dane zostają zapisane w tabeli.
Np. przez takie makro:

Oczywiście wskazane jest tu dodanie sprawdzenia czy wszystkie pola obowiązkowe są wypełnione i ewentualnie jaki typ danych  jest wpisany. No i samo uruchomienie kwerendy dołączającej spowoduje wyświetlenie komunikatów ostrzegawczych – też powinny zostać wyłączone na czas uruchomienia kwerendy.

Zdecydowanie lepszym sposobem na zapisanie danych z takiego formularza jest jednak procedura VBA.
Przypisujemy wartości poszczególnych pul formularza do zmiennych, sprawdzamy ich poprawność, a następnie wykonujemy kod SQL oparty na kodzie kwerendy dołączającej.
Przykładowy kod w tym przypadku może wyglądać tak:

Private Sub PolecenieZapisz_Click()
Dim JAutor As String
Dim JTytul As String
Dim JDzial As Long
Dim JCena As Currency
Dim JDataP As Date
Dim JBest As Boolean
Dim Kwera As String
If IsNull(Me.TAutor) Then
MsgBox „Brak autora”, vbCritical, „Brak wymaganych danych”
Me.TAutor.SetFocus
Exit Sub
Else
JAutor = Me.TAutor
End If
If IsNull(Me.TTytul) Then
MsgBox „Brak tytułu”, vbCritical, „Brak wymaganych danych”
Me.TTytul.SetFocus
Exit Sub
Else
JTytul = Me.TTytul
End If
If IsNull(Me.TDzial) Then
MsgBox „Przypisz dział”, vbCritical, „Brak wymaganych danych”
Me.TDzial.SetFocus
Exit Sub
Else
JDzial = Me.TDzial
End If
JCena = Nz(Me.TCena, 0)
JDataP = Nz(Me.TData, Date)
JBest = Nz(Me.TBestseller, False)
Kwera = „INSERT INTO TabelaKsiazki ( Autor, Tytul, Dzial, Cena, DataP, Bestseller ) ” & _
„SELECT '” & JAutor & „’ AS Wyr1, '” & JTytul & „’ AS Wyr2, ” & JDzial & ” AS Wyr3, ” & JCena & ” AS Wyr4, #” & JDataP & „# AS Wyr5, ” & JBest & ” AS Wyr6;”
DoCmd.SetWarnings False
DoCmd.RunSQL Kwera
DoCmd.SetWarnings True
DoCmd.Close
End Sub

Moją ulubioną metodą dopisywania rekordów jest natomiast Recordset i Add.New. To już jednak temat na zupełnie oddzielną  notkę.