ADO (ActiveX Data Objects) 

• W aplikacji MS Access wyświetlanie i aktualizacja danych na ogół odbywa się przy użyciu standardowego interfejsu. 

• Może być także zrealizowane bezpośrednio w języku VBA za pomocą programistycznego interfejsu do danych ADO (lub DAO): 

  • uniezależnienie od standardowego interfejsu Accessa,  

  • przeniesienie sposobu “programistycznego” tworzenia aplikacji do środowiska bazy danych, gdzie przeważają sposoby “projektowe”.

• Zarówno elementy interfejsu użytkownika jak i elementy bazy danych są traktowane jako obiekty, którymi posługujemy się kierując do nich odpowiednie komunikaty - realizowane w obiektach przez metody.

Obiekt Recordset jest używany w programach VBA do przetwarzania rekordów w bazie danych. Używając tego obiektu można nawigować po zbiorze rekordów, modyfikować istniejące rekordy, dodawać nowe rekordy i usuwać określone rekordy. 

• Obiekt Recordset reprezentuje cały zbiór rekordów z tabeli w bazie danych lub z wyniku zapytania na tabelach bazy danych. W danej chwili dostęp jest tylko do jednego rekordu nazywanego bieżącym rekordem. 

• Można też używać obiektu Recordset powiązanego z formularzem (właściwość Recordset) – z możliwością programistycznego dostępu do związanych pól formularza.

Model programowania ADO określa grupę obiektów, które używa się do uzyskania dostępu i aktualizacji źródła danych: 

• Połączenie ze źródłem danych.

• Określenie polecenia z dostępem do źródła danych.

• Wykonanie polecenia.

• Zapisanie wierszy w obiekcie typu Recordset w celu ich przejrzenia i aktualizacji.

• Aktualizacja źródła danych ze zmianą obiektu Recordset.

• Wykrycie błędów, które mogą wystąpić podczas połączenia i wykonywania polecenia.

Podstawowe obiekty modelu ADO:

• Connection - korzeń w hierarchii obiektów ADO, używany przy dokonywaniu połączenia między aplikacją i źródłem danych.

• Recordset - reprezentuje zbiór rekordów przekazanych ze źródła danych.

• Command - reprezentuje instrukcję SQL.

Użycie obiektów Connection i RecordSet

Uzyskanie połączenia z bieżącą bazą danych Access

Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection

Deklaracja i utworzenie obiektu Recordset

Dim rsCustomers As ADODB.Recordset
Set rsCustomers = New ADODB.Recordset

Otwarcie tabeli Customers

rsCustomers.Open "Customers", cnCurrent

Odświeżenie zbioru rekordów w obiekcie Rekordset

rsCustomers.Requery

Zamknięcie i usunięcie z pamięci obiektów Connection i Recordset

rsCustomers.Close
rsCurrent.Close
Set rsCustomers = Nothing
Set cnCurrent = Nothing

Odwołanie do pola rekordu

MsgBox rsCustomers!ContactName

Nawigacja po zbiorze rekordów - metody:

• MoveFirst

• MoveLast

• MoveNext

• MovePrevious

i właściwości:

• BOF - pozycja bieżącego rekordu jest przed pierwszym rekordem,

• EOF - pozycja bieżącego rekordu jest po ostatnim rekordzie.

 
rsOrders.MoveNext
If rsOrders.EOF Then
    rsOrders.MoveLast
End If
 

Przeglądanie wszystkich rekordów w pętli:

Do Until rsCustomers.EOF
   MsgBox rsCustomers!ContactName
   rsCustomers.MoveNext
Loop

Otwarcie obiektu RecordSet za pomocą SQL

rsCustomers.Open "SELECT * FROM Customers", cnCurrent

lub
strSQL = "SELECT * FROM Customers " & _
      "WHERE ContactName = '" & txtContactName.Value & "'"
rsCustomers.Open strSQL, cnCurrent

Wykonywanie instrukcji SQL

• (opisana na poprzednich wykładach) metoda RunSQL obiektu DoCmd - ograniczenie do tabel MS Access (w tym załączonych z innych baz danych),

• metoda Execute obiektu Connection,

• metoda Execute obiektu Command,

• metoda Open obiektu Recordset.

Uwaga: 

• W przypadku instrukcji SELECT metoda Execute w obiektach Connection i Command zwraca zestaw rekordów tylko-do-odczytu.

• Metodę Open obiektu RecordSet można stosować także do wykonywania instrukcji INSERT, UPDATE i DELETE. 

strSQL = "UPDATE Customers " SET ContactName = '" & _
         txtContactName.Value & "'" & _
        "WHERE CustomerID = '" & txtCustomerID.Value & "'"
cnCurrent.Execute strSQL

lub

rsCustomers!Contactname = InputBox("Podaj nazwisko:")
rsCustomers.Update

Podobnie dla instrukcji INSERT:

strSQL = "INSERT INTO Customers(CustomerID, ContactName)_
  VALUES (' _
   & txtCustomerID.Value & "','" _
   & txtContactName.Value & "')"
cnCurrent.Execute strSQL

lub

rsCustomers.AddNew
rsCustomers!CustomerID = txtCustomerID.Value
rsCustomers!Contactname = InputBox("Podaj nazwisko:")
rsCustomers.Update

Dla DELETE:

• metoda Execute obiektu Connection

strSQL = "DELETE * FROM Products " & _
   "WHERE ProductID = " & txtProductID.Value
cnCurrent.Execute strSQL

• metoda Delete obiektu Recordset

rsProducts.Delete
rsProducts.MoveNext
If rsProducts.EOF Then
   rsProducts.MoveLast
End If

Konieczne przesunięcie wskaźnika bieżącego rekordu za usunięty rekord.

Przykład

Załóżmy, że chcemy w tabeli “Pracownicy” zmienić zawód “Sprzedawca” na “Księgowy”. Najprostsze rozwiązanie to użyć instrukcji SQL (w metodzie RunSQL lub Execute):

UPDATE Pracownicy
SET Tytuł = "Księgowy"
WHERE Tytuł = "Sprzedawca";

Natomiast używając języka programowania, napisalibyśmy instrukcję iteracji sprowadzającą kolejne rekordy z tabeli “Pracownicy”. W przypadku napotkania wartości “Sprzedawca” zmienilibyśmy ją na “Księgowy”. Dostęp do kolejnych rekordów tabeli (a także rekordów kwerend i formularzy) umożliwia konstrukcja zestawu rekordów. Stosując ją należy określić na samym początku źródło rekordów, a następnie używając metod MoveFirst i MoveNext można przejść po wszystkich rekordach ze źródła rekordów.

Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection

Dim rsPracownicy As ADODB.Recordset
Set rsPracownicy = New ADODB.Recordset

rsPracownicy.Open "Pracownicy", cnCurrent

rsPracownicy.MoveFirst
Do Until rsPracownicy.EOF
   If rsPracownicy!Tytuł = "Sprzedawca" Then
     rsPracownicy!Tytuł = "Księgowy"
       rsPracownicy.Update 'Zapisanie zmian
   End If
   rsPracownicy.MoveNext
Loop
rsPracownicy.Close

Jest możliwość przeglądania rekordów zgodnie z porządkiem określonym przez pewien, uprzednio założony na tabeli indeks. Gdyby na kolumnie Nazwisko był założony indeks, moglibyśmy przeglądać rekordy zgodnie z porządkiem określonym przez ten indeks, kładąc przed instrukcją        

    rsPracownicy.MoveFirst 

instrukcję:
    rsPracownicy.Index = "Nazwisko"

Podstawą użyteczności modelu ADO jest niezależność modelu programistycznego od źródła danych. Wystarczy określić odpowiednio napis połączenia ConnectionString a następnie korzystać z jednolitego kodu. 

Biblioteki ADO można użyć wszędzie tam gdzie można użyć kodu Visual Basic  np. w niezależnym programie klienckim napisanym w języku Visual Basic lub skrypcie ASP – Active Server Pages – w aplikacji internetowej.

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=Pubs;UID=sa;PWD=sss;"
cnn.Open

Jeśli w napisie połączenia załączymy parametr Provider możemy w ConnectionString używać dodatkowe parametry zdefiniowane przez tego szczególnego "dostawcę danych".

•  Provider = SQLOLEDB  oznacza "Microsoft OLE DB Provider for SQL Server", 

•  Provider = MSDAORA oznacza "Microsoft OLE DB Provider for Oracle".

• Domyślnie Provider = MSDASQL - Microsoft OLE DB Provider for ODBC.

Przykład

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection 
cnn.Provider = "sqloledb" 
cnn.Open "Data Source=srv;Initial Catalog=pubs;","sa",""

Transakcyjność (o ile "dostawca danych" realizuje transakcje)

Domyślnie każda instrukcja SQL kończy się automatycznym zatwierdzeniem (auto-commit). 

Za pomocą metod obiektu Connection:

BeginTrans – rozpoczyna nową transakcję.
CommitTrans – zapisuje zmiany i kończy aktualną transakcję. 
RollbackTrans – kasuje zmiany dokonane w trakcie transakcji i kończy aktualną transakcję.

Transakcje mogą być zagnieżdżone.

Składnia (obiekt – obiekt typu Connection)

poziom = obiekt.BeginTrans() – zwraca poziom zgnieżdżenia transakcji
obiekt.BeginTrans
obiekt.CommitTrans
obiekt.RollbackTrans

Ten sam efekt można uzyskać przy pomocy instrukcji SQL (wykonywanych przez metodę Execute):

• BEGIN TRANSACTION

• COMMIT

• ROLLBACK