Podstawy VBA

VBA i ADO
Zadanie 1 (Łączenie się serwerem MS SQL Server)
ADO posiada kilka możliwości nawiązania połączenia z danymi SQL Servera. Najprościej
jest skorzystać z dostawcy SQLOLEDB.
Poniższy kod należy utworzyć w VBA jako moduł MS Accessa. Nawiązuje on połączenie z
bazą pubs serwera o nazwie C115-25 na lokalnym komputerze. Nazwę C115-25 należy
zmienić.
Uwaga. Ze względu na sposób połączenia zdefiniowany w łańcuchu połączenia poniższego
przykładu, serwer z którym się łączymy musi pracować w trybie autoryzacji „mixed”. Zmianę
trybu autoryzacji można wykonać z poziomu Enteprise Managera: prawym przyciskiem
myszy kliknąć na nazwie serwera, potem Properties | Security | Authentication | SQL Server
and Windows.
Sub
Dim
Dim
Dim
ConnectToMSSQL()
conn As ADODB.Connection
cmd As ADODB.Command
rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
conn.Provider = "SQLOLEDB"
conn.ConnectionString = "Data Source=C115-25;” & _
”Database=pubs;uid=sa;password=;"
conn.Open
If conn.State = adStateOpen Then
MsgBox "Connection successful"
End If
conn.Close
Set conn = Nothing
End Sub
Zadanie 2
Utworzymy kwerendę do zdalnego źródła danych. MS SQL Serwerem. Aby połączyć się z ze
zdalnym źródłem danych MS SQL Servera kwerenda ma być skierowana do bazy danych o
nazwie pubs na serwerze o nazwie TestMSSQL (jeżeli Twój serwer ma inną nazwę, to trzeba
jej użyć). Po wykonaniu poniższej procedury wynik można obejrzeć w okienku Immediate.
Sub
Dim
Dim
Dim
TestSQLOLEDB()
conn As ADODB.Connection
cmd As ADODB.Command
rs As ADODB.Recordset
‘utworzenie połączenia z bazą danych
Set conn = new ADODB.Connection
Conn.provider = ”sqloledb”
Conn.ConnectionString = ”data source=TestMSSQL;uid=sa;initial catalog=pubs”
Conn.open
‘budowanie zapytanie SQL
Set cmd = new ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = ”SELECT authors.au_lname, authors.au_fname” & _
FROM authors”
cmd.CommandType = adCmdText
‘wykonanie zapytania
Set rs = cmd.adCmd.Execute
Debug.Print = rs.GetString
End sub
Zadanie 3
Utworzyć formularz w MS Accessie, w którym będą dwa pole tekstowe i przycisk. Do pól
wpisywać będziemy nazwę instancji MS SQL Servera, nazwę bazy danych i nazwę tabeli w
tej bazie. Po kliknięciu przycisku powinno ostatecznie pojawić się okienko komunikatu z
informacją o tym ile jest rekordów w podanej tabeli.
Otwieranie bazy danych Microsoft Jet (czyli MS Access) w trybie do odczytu-zapisu
Zadanie 4
Połączyć się z bazą MS Accessa (przykładowa baza Northwind). Poniżej jest pokazany
podstawowy szkielet procedury.
Sub ConnectToJet()
Dim conn As ADODB.Connection
Dim path As String
path = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Set conn = New ADODB.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.ConnectionString = "Data source=" & path
conn.Open
‘tutaj przetwarzamy dane, a nastęonie można połączenie zamknąć
conn.Close
Set conn = Nothing
End Sub
Zadanie 5
Rozbudować przykład z Zadania 4 w następujący sposób. Utworzyć formularz w Accessie z
polem tekstowym, w które można wpisać ścieżkę do pliku z bazą danych Jet (pliki MS
Accessa *.mdb). Następnie po kliknięciu przycisku na formularzy, w okienku Immediate
pokazać się powinna lista tabel bazy, z którą się łączymy.
Wsk.
Należy skorzystać z obiektu ADOX.Catalog oraz ADOX.Table:
Dim cat as new ADOX.Catalog
Dim tbl as ADOX.Table
...
cat.ActiveConnection = conn
...
for each tbl In cat.Tables
Debug.Print tbl.Name & ”---” & tbl.Type
Next tbl
2
Zadanie 6
Skopiuj bazę danych kadry.mdb.
Wykorzystując ADO napisz procedurę w VBA wypisującą osoby, które mają prawo jazdy.
Następnie po wciśnięciu OK w oknie dialogowym procedura ta ma wykonać podwyżkę o
10% dla wszystkich takich osób. Przy wciśnięciu „Cancel” podwyżka nie będzie wykonana.
Wsk.
Należy wykorzystać funkcję MsgBox, która zwraca kod przyciśniętego przycisku (patrz
pomoc do VBA).
Można wykorzystać stałą vbCrLf, która oznacza przejście do nowej linii. Stała ta może być
dołączana do ciągu znaków, jeśli chcemy by ciąg ten był wyświetlany w kilku liniach.
a) Wykorzystaj obiekt ADO.Recordset, który pobierze dane z tabeli Pracownicy.
Podwyżkę wykonuj na tym samym rekordsecie.
Sub
Dim
Dim
Dim
Dim
podwyzka()
cnCurrent As ADODB.Connection
rsPrac As New ADODB.Recordset
policz As Long
intRes As Integer
Set cnCurrent = CurrentProject.Connection
rsPrac.Open "Pracownicy", cnCurrent, adOpenDynamic, adLockOptimistic,
adCmdTable
policz = 0
rsPrac.MoveFirst
While Not rsPrac.EOF
If (rsPrac![prawo jazdy] = yes) Then
policz = policz + 1
End If
rsPrac.MoveNext
Wend
intRes = MsgBox("Liczba pracowników z prawem jazdy: " & policz & " & _
vbCrLf & "po naciśnięciu OK nastąpi podwyżka", vbOKCancel)
If intRes = vbOK Then
rsPrac.MoveFirst
While Not rsPrac.EOF
If (rsPrac![prawo jazdy] = yes) Then
RsPrac!Stawka = rsPrac!Stawka*1.1
End If
rsPrac.MoveNext
Wend
End If
End Sub
b) Wykorzystaj obiekt ADODB.Command i odpowiednie zdanie w SQL. Podwyżkę
wykonaj jako zdanie SQL w obiekcie ADODB.Connection. Rekordset otwarty przez
obiekt ADODB.Command jako zdanie SQL nie może być aktualizowany ani
nieustawia poprawnie własności RecordCount. Dlatego w zadaniu tym po otwarciu
rekordsetu wykonaj od razu podwyżkę, a liczbę zaktualizowanych rekordów wyświetl
wykorzystując parametr RecordsAffected metody (procedury) Execute obniektu
Command.
3
Sub
Dim
Dim
Dim
Dim
podwyzka2()
cnCurrent As ADODB.Connection
rsPrac As New ADODB.Recordset
cmdPrac As New ADODB.Command
RecordsAffected As Integer
Set cnCurrent = CurrentProject.Connection
With cmdPrac
Set .ActiveConnection = cnCurrent
.CommandType = adCmdText
.CommandText = "SELECT * From Pracownicy WHERE [data zatrudnienia]" _
& [Prawo jazdy]=yes"
End With
Set rsPrac = cmdPrac.Execute
With cmdPrac
Set .ActiveConnection = cnCurrent
.CommandType = adCmdText
.CommandText = “UPDATE Pracownicy SET stawka=stawka*1.1” & _
“WHERE [Prawo jazdy]=yes"
End With
cmdPrac.Execute RecordsAffected
MsgBox "Zaktualizowano " & RecordsAffected & " rekordów"
End Sub
Otwieranie arkusza MS Excel przy użyciu ADO
Zadanie 7
Przetestować poniższą procedurę, która demonstruje, w jaki sposób można otworzyć przy
użyciu ADO arkusz kalkulacyjny.
Sub ConnectToExcel()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\bazy_danych\raport.xls;Extended Properties=Excel 8.0"
MsgBox "Arkusz zpstał otwarty."
End Sub
4