Akademia VBA – Oferta

Wyróżnione

Tworzę i naprawiam aplikacje VBA/SQL/Makra. Moje aplikacje wyróżnia:

  • Dopasowanie do potrzeb klienta
  • Otwarty i dobrze skomentowany kod źródłowy
  • Instrukcja obsługi aplikacji
  • Szkolenia umożliwiające rozwijanie aplikacji własnymi siłami.

Mam 25 lat doświadczenia w szkoleniach i tworzeniu aplikacji dla biznesu. Tworzę aplikacje głównie w oparciu o środowisko Excel i VBA ale także SQL Server, Access i Power BI.

Pozdrawiam i zapraszam do współpracy

Dariusz Kolasa

Podstawy SQL – Kolejność klauzul w SELECT

Poważna aplikacja VBA z często korzysta z jakiejś bazy danych aby odczytać, dopisać a czasem zaktualizować lub usunąć jakieś dane. Dlatego warto poznać co najmniej podstawy języka SQL. Jest to język dość prosty jeżeli chodzi o podstawy, ale oczywiście jeżeli mamy nietrywialne potrzeby, zapytania też się komplikują. Co musisz wiedzieć na pewno to jak odczytać dane z bazy. Z artykułu o ADO wiesz, że z VBA można nawiązać połączenie z Access i SQL Server. Tam jako źródło zapytania jest po prostu podana nazwa tabeli. Ale oczywiście zamiast tego możemy podać dowolne wyrażenie typu SELECT. I tu na początek trzeba wiedzieć kilka rzeczy. Po pierwsze kolejność klauzul jest ściśle określona i jest następująca:

 SELECT pole1, pole2, poleN...
 FROM tabela (tabela1 JOIN tabela2 ON klucz1 = klucz2)
 WHERE warunek1 AND/OR warunek2...

Tak wygląda najprostszy select bez grupowania i wyliczeń. Zasadą jest, że piszemy najpierw FROM a potem wypisujemy pola w SELECT, bo tylko wtedy dobry edytor kodu SQL może nam podpowiedzieć nazwy pól. We FROM podajemy nazwę pojedynczej tabeli lub wiele tabel ale wtedy musimy wiedzieć po jakich polach kluczowych trzeba je połączyć. Sytuacja komplikuje się gdy chcemy w zapytaniu grupować dane i coś wyliczać dla tych grup:

 SELECT pole1, pole2, SUM(pole3) AS Razem
 FROM tabela (tabela1 JOIN tabela2 ON klucz1 = klucz2)
 WHERE warunek1 AND/OR warunek2...
 GROUP BY pole1, pole2
 HAVING SUM(pole3) > 1000
 ORDER BY Razem DESC

Jak widać użyliśmy w SELECT funkcji SUM, która współpracuje z klauzulą GROUP BY definiującą dla jakich grup chcemy policzyć sumy. Dodatkowo w klauzuli HAVING informujemy, że interesują nas tylko sumy przekraczające 1000. ORDER BY to oczywiście sortowanie, w tym wypadku po sumach w porządku malejącym (DESC jak descending). W SELECT za polem trzecim mamy as Razem co oznacza jak chcemy nazwać to wyliczane pole. I tu ciekawostka. Skoro już nazwaliśmy to pole Razem to czemu wykorzystaliśmy tą nazwę tylko w ORDER BY Razem DESC ale już nie w HAVING gdzie ponownie użyliśmy wyrażenia SUM(pole3) > 1000?. Otóż wynika z ważnej zasady, że kolejność pisania klauzul w SELECT nie odpowiada kolejności przetwarzania ich przez serwer! Okazuje się, że klauzula SELECT jest przetwarzana dopiero jako piąta!

 SELECT pole1, pole2, SUM(pole3) AS Razem              --5!!!
 FROM tabela (tabela1 JOIN tabela2 ON klucz1 = klucz2) --1
 WHERE warunek1 AND/OR warunek2...                     --2
 GROUP BY pole1, pole2                                 --3
 HAVING SUM(pole3) > 1000                              --4
 ORDER BY Razem DESC                                   --6

I dlatego tylko klauzula ORDER BY jako jedyna przetwarzana po SELECT może użyć zdefiniowanej w niej nazwy pola!

ADO – i wszystkie bazy są nasze :)

Od 2000 roku czyli już od ponad 20 lat Microsoft Office może bez problemu łączyć się w zasadzie z dowolnymi bazami danych, dzięki bibliotece ADO (Microsoft ActiveX Data Object XX Library). I od tych samych ponad 20 lat budzi to niezmiennie zdziwienie wśród moich klientów i studentów. Jak to? Excel może pobrać dane z Oracle?, SQL Servera? O! Może nawet je edytować?! No oczywiście, że może, i to od wielu lat…
Biblioteka ADO (w kodzie widoczna jako ADODB) jest domyślnie podłączana tylko w Accessie. We wszystkich innych produktach, w tym w Excelu trzeba ją wybrać w referencjach. Tools – References – Microsoft ActiveX Data Object XX Library, gdzie XX to numer wersji biblioteki, ja osobiście wybieram zwykle wersję 2.8
Drugi warunek to oczywiście poprawnie zainstalowany sterownik OLEDB do określonej bazy (http://en.wikipedia.org/wiki/OLE_DB_provider)

Poniżej przykład pobierający do Excela dane z Accessa 2003, ale uważny czytelnik zobaczy drogę pobierania z Accessa 2007/10/13/16/19 tudzież z SQL Servera. Wystarczy podmienić wartość właściwości ConnectionString obiektu Connection

Option Explicit
Public Const WERSJA As String = "ADO Klient v.0.1"
Public Const ARK_DANE As String = "Dane"
Public Const ZRODLO As String = "tbBrutto"
 
'Załozenia:
' - mamy arkusz o nazwie "Dane"
' - mamy bazę PodstawyVBA.mdb a w niej tabelę tbBrutto
' - mamy sterownik do mdb
 
'Access mdb
'==========
Public Const CN_STR As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=D:\kursy\AC04\PodstawyVBA.mdb;" & _
    "Persist Security Info=False"
 
 
'Ponizej przykłady konfiguracji dla innych baz
 
'Access accdb
'============
'Public Const CN_STR As String = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=D:\kursy\AC04\PodstawyVBA.accdb;" & _
    "Persist Security Info=False"
 
'SQL Server
'==========
'Public Const CN_STR As String = _
    "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=Produkcyjna;" & _
    "Data Source=SKARBEKMC\SQLEXPRESS"
 
 
Sub OdczytDoExcela()
    Dim Zakres As Range
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
 
    On Error GoTo Obsluga
 
    Sheets(ARK_DANE).Select
    Range("A1").Select
    Set Zakres = _
        Sheets(ARK_DANE).Range("A1").CurrentRegion
    Zakres.ClearContents
 
    cn.ConnectionString = CN_STR
    cn.Open
    rs.Open ZRODLO, cn
    Sheets(ARK_DANE).Range("A2").CopyFromRecordset rs
 
    'nagłówki
    Dim LK As Long, K As Long
    LK = rs.Fields.Count
    For K = 1 To LK
        Sheets(ARK_DANE).Cells(1, K) = rs.Fields(K - 1).Name
    Next
 
    Zakres.EntireColumn.AutoFit
 
Czyszczenie:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    Exit Sub
Obsluga:
    MsgBox Err.Number & " - " & Err.Description, vbExclamation, WERSJA
    Resume Czyszczenie
End Sub