ExcelMagazine

Dzisiaj zajmę się problemem połączenia Excela przy pomocy VBA do zewnętrznego źródła danych. Obsługa każdej bazy danych jest nieco inna i dlatego, aby zachować przenośność kodu korzystamy z uniwersalnych intefejsów API, które pozwalają na jednorodny sposób obsługi bazy. Takimi technologiami są np ADO czy DAO. Zajme się tylko ADO ponieważ jest to jedyna technologia dostępu rozwijana i promowana przez Microsoft.

Wprowadzenie do ActiveX Data Object (ADO)

ADO jest technologią dostępu do baz danych zaprojektowaną przez Microsoft w celu uproszczenia dostępu do baz danych. Można powiedzieć że ADO jest łącznikiem pomiędzy aplikacją w Excelu, a bazą danych, do korzystania z którego wystarczy jedynie znajomość SQL.

Jak ADO komunikuje się z bazą danych?

ADO korzysta z technologii OLE DB (innego interfejsu niższego poziomu), której jądrem jest uniwersalny komponent zwany dostawcą. Przekształca on instrukcje dowolnego źródła danych do wspólnego dla całego obiektu ADO zestawu komend. W obiekcie ADO znajdują się dostawcy do najczęściej występujących klientów danych m.in. do Accessa, SQL Servera czy Excela. W pozostałych przypadkach (np. MySQL), musimy korzystać z OLE DB dla ODBC, czyli wczesnego, ale najpopularniejszego sposobu dostępu do danych.

Hierarchia obiektów ADO

ADO opiera się na modelu obiektowym, który pokazałem na rysunku.

Na białym tle znajdują się obiekty, na szarym tle znajdują się kolekcje obiektów. W praktyce korzystanie z ADO wygląda tak:

• Łączymy się z bazą danych przy pomocy obiektu Connection.
• Wywołujemy zapytanie, które zwracane jest jako obiekt Recordset.
• W obrębie RecordSet znajdują sie pola (kolekcja Fields) odpowiadające kolejnym kolumnom w zapytaniu
• Zamiast zapytania możemy wywołać przy pomocy obiektu Command możemy wywołać kwerendę zmieniającą zawartość bazy danych. Kwerenda ta w wyniku swojego działania może nie zwrócić nam niczego, albo obiekt Recordset.
• Kwerendę modyfikującą dane możemy wywołać z parametrami przechowywanymi w kolekcji Parameters.

Połączenie do źródłą danych (Connection string)

Aby połączyć się ze źródłem danych należy przekazać do ADO, w formie łańcucha znaków informacje, o dostawcy, źródle danych, itp. Ciąg ten nosi nazwę Connection Strings. Każdy Connection String rozpoczyna się od podania dostawcy (Provider), który będzie używany przy połączeniu. Jeżeli dostawca nie został określony to przyjmuje się, że będziemy domyślnie używać ODBC.

Większość potrzebnych Connection Stringów znajdziesz tutaj lub tutaj. Pamiętaj, jeżeli przy połączeniu będziesz korzystał z ODBC, to najpierw sprawdź czy posiadasz odpowiedni sterownik (Panel Sterowania -> Narzędzia Administracyjne -> Źródła danych ODBC). Jeżeli nie (a tak dzieje się gdy np. chcesz połączyć się z MySQL), ściągnij odpowiedni sterownik ze strony producenta.

Operacje na bazie danych

Korzystanie z ADO w celu połączenia się z bazą danych zawsze działa tak samo -- otwieramy połączenie z bazą, wykonujemy zapytanie, a następnie zamykamy połączenie. Przeanalizujmy poniższy przykład

Public gcnAccess As ADODB.Connection
Sub Poloczenie()
    Dim sFile As String
    Dim sConnect As String
    'sciezka do do źródła danych
    sFile = "C:\Apps\test.mdb"
    sConnect = "Provider = Microsoft.Jet.OLEDB.4.0; " & _
               "Data Source = " & sFile
    On Error GoTo ErrHandler
    Set gcnAccess = New ADODB.Connection
    gcnAccess.connectionString = sConnect
    'otwieramy połączenie
    gcnAccess.Open
    'sprawdzamy czy się połączyliśmy
    If gcnAccess.State = adStateOpen Then
       MsgBox "Połączony!"
    End If
    'konczymy polaczenie
    gcnAccess.Close
    'niszczymy obiekt połączenia
    Set gcnAccess = Nothing
    Exit Sub
    'obsługa błędu jeżeli nie możemy się połączyć z bazą.
ErrHandler:
    MsgBox "Błąd połączenia: " & Err.Description
End Sub

Obiekt ADO Connection należy deklarować jako zmienna globalna. Jest to wymagane, aby ułatwić connection pooling. Niszczenie i tworzenie połączenia jest operacją bardzo pochałaniającą zasoby. Jeżeli twój obiekt Connection nie jest zmienną globalną, a w swojej aplikacji będziesz wiele razy odwoływał sie do bazy danych to może się okazać, że częste tworzenie i niszczenie połączenia źle wpłynie na wydajność aplikacji. Dlatego, aby wymusić grupowanie połączeń należy obiekt Connection ustawić jako zmienną globalną, utworzyć połączenie przy starcie aplikacji, i zniszczyć go przy jej zakończeniu. Należy ponadto dbać, aby każda procedura procedura otwierająca połączenia, po wykorzystaniu natychmiast go zamykała.

Jak uzyskać dane z bazy danych zajmę się w następnym poście.

Powiązane artykuły:

• ADO w Excelu cz. 2
• Excel w sieci – Importowanie plików XML cz. 1/3
• Wtorki z VBA – Zamykanie formularza
• Wtorki z VBA – Obsługa formularzy z poziomu VBA
• Wtorki z VBA – Formularze i obsługa klawiatury

One Response to “ADO w Excelu cz. 1”

1. Maciej says:

   August 11, 2008 at 10:11 am

   Witam.
   Wpisałem te skrypy w VB i wychodzi błąd ( sConnect = “Provider = Microsoft.Jet.OLEDB.4.0; ” & _
   “Data Source = ” & sFile )
   w tej lini. Czy użył Pan pliku access a moze innego. Jak połączyć sie z baza access