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.
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.
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
Obiekt Command jest reprezentacją instrukcji SQL w celu jej wykonania na źródle danych.
Obiekt Command jest istotny w przypadkach gdy trzeba powtórnie wykonać tę samą instrukcję bądź, gdy wygodnie jest używać parametrów.
Dim SQLStat As String
SQLStat = "UPDATE Titles SET Type = 'Informatyka' " & _
"WHERE Type = 'Computer Science'"
Dim strCnn As String
strCnn = "Provider=sqloledb;Data Source=srv;" & _
"Initial Catalog=pubs;User Id=sa;Password=;"
Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
Dim cmdChange As ADODB.Command
' Tworzenie obiektu typu Command:
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = cnn1
cmdChange.CommandText = strSQLChange
cmdChange.Execute
Nieodzowność użycia języka programowania przy tworzeniu aplikacji bazy danych w MS Access w przypadku:
przetwarzanie danych wymagające iteracji i podejmowania wyborów;
sprawdzanie poprawności danych, ich poprawianie i diagnostyka;
odpowiednia reakcja na zaistniałe błędy – diagnostyka i poprawianie;
współpraca z innymi programami systemu Windows;
dawanie sobie rady z dużym rozmiarem aplikacji, możliwość wielokrotnego użycia tego samego kodu.