ADO w Excelu cz. 2
W części pierwszej dowiedzieliśmy się jak połączyć się i przetestować połączenie z bazą danych. Dzisiaj przyszedł czas na praktykę. Pokażę jak połączyć się z bazą danych, wyciągnąć z niej informacje.
Potrzebne pliki
Zacznijmy od przygotowania prostej tabeli w Accesie dotyczącej pracowników. Wygląda ona tak:
i możesz ją ściągnąć stąd.
Na naszej bazie wykonamy zapytanie, które wyświetli wszystkie rekordy z tabeli pracownicy.
Wyświetlenie zawartości tabeli przez ADO
Przeanalizujmy poniższy przykład:
Sub Poloczenie() Dim sFile As String Dim sConnect As String 'sciezka do do źródła danych sFile = "C:\pracownicy.mdb" sConnect = "Provider = Microsoft.Jet.OLEDB.4.0; " & _ "Data Source = " & sFile 'zadajemy pytanie Dim sSQL As String sSQL = "SELECT * FROM Pracownicy;" 'tworzymy nowy obiekt RecordSET Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset 'otwieramy obiekt rs.Open sSQL, sConnect 'kopiujemy zawartosc tablicy If Not rs.EOF Then ActiveSheet.Range("A1").CopyFromRecordset rs End If 'zamykamy polaczenie rs.Close Set rs = Nothing End Sub
Jak zwykle zaczynamy od utworzenia odpowiedniego połączenia. Następnie tworzymy zapytanie w języku SQL. Jeżeli nie znasz tego języka to polecam szybkie dokształcenie się np korzystając z tej pozycji. Wynikiem zapytania jest tabela, którą w VBA reprezentuje obiekt Recordset. Metoda open wymaga podania dwóch parametrów -- zapytania oraz połączenia. W wyniku wykonania metody open zwracana jest albo tablica albo albo pusty zbiór. Aby sprawdzić czy nasz obiekt zawiera dane wywołujemy metodę EOF, która zwraca prawdę jeżeli Recordset jest pusty. Informacje zawarte w tablicy są kopiowane przy pomocy metody .CopyFromRecordset do zakresu w aktywnym arkuszu.
Zauważ, że tworząc połączenie nie skorzystałem z obiektu Connection, tylko od razu przekazałem łańcuch połącznenia do RecordSet. Jest to użyteczne, gdy wykonujemy pojedyncze zapytanie i nie chcemy korzystać z mechanizmu grupowania połączeń.
W wyniku powyższej procedury dostaniemy TYLKO zawartość tablicy. Jeżeli potrzebujemy również nazw kolumn będziemy musieli skorzystać dodać do naszej procedury kod
If Not rs.EOF Then Dim iKol As Integer iKol = 0 Dim element As Field For Each element In rs.Fields ActiveSheet.Range("A1") _ .Offset(0, iKol) = rs.Fields(iKol).Name iKol = iKol + 1 Next ActiveSheet.Range("A2").CopyFromRecordset rs End If
Obiekt Fields jest kolekcją obiektów Field reprezentujący poszczególne pola w rekordzie. Tak więc Recordset jest zbiorem kolekcji Fields. Domyślnie wywołujemy kolekcję odpowiadającą pierwszemu rekordowi. Każdy element kolekcji Fields zawiera w sobie obiekt Field, który posiada przynajmniej dwie ciekawe właśności -- Name, nazwę kolumny do której należy pole oraz Value, czyli zawartość pola.
Często zdarza się tak, że zanim wyświetlimy wynik kwerendy, chcemy w jakiś sposób go przefiltrować. W tym celu potrzebujemy przejść przez wszystkie elementy zapytania. Poniższy kod pokazuje, jak wyświetlić wszystkie pola zapytania iterując po nich. Nic nie stoi na przeszkodzie, aby w odpowiednim miejscu wstawić if-a 
If Not rs.EOF Then Dim iKol As Integer iKol = 0 Dim element As Field For Each element In rs.Fields ActiveSheet.Range("A1") _ .Offset(0, iKol) = rs.Fields(iKol).Name iKol = iKol + 1 Next Dim iWiersz As Integer iWiersz = 1 Do While Not rs.EOF For i = 0 To rs.Fields.Count - 1 Cells(iWiersz + 1, i + 1).Value = rs.Fields(i).Value Next iWiersz = iWiersz + 1 rs.MoveNext Loop ActiveSheet.Range("A2").CopyFromRecordset rs End If
I to tyle na dziś -- za tydzień pokażę, jak modyfikować zawartość tabel przy pomocy ADO oraz jak dostać się poprzez ADO do Excela.