Wstęp........................................................................................................................................1Access.......................................................................................................................................1

Linkowanie tabel.............................................................................................................11Import i Export.................................................................................................................16Migracja..............................................................................................................................17Kwerendy przekazujące...............................................................................................18

EXCEL......................................................................................................................................19Przygotowanie środowiska pracy.............................................................................19Pobranie danych.............................................................................................................23

Table.................................................................................................................................25PivotTable Report.............................................................................................................26PivotChart And PivotTable Report...................................................................................27

Programowanie w VBA.................................................................................................27MSSQL.....................................................................................................................................32Import i Export danych kreatorem: Import and Export Data (32-bit)......................................37

Wstęp

Office, jako pakiet biurowy jest obecny w praktyczni każdym biurze oferując takie programy jak Word, Excel, Access i kilka innych nie mniej użytecznych narzędzi. Całość tak została zaprojektowana, aby mogła pracować razem bez zbędnego wysiłku ze strony użytkownika. Niewiele jednak osób zdaje sobie sprawę z możliwości łatwej integracji z serwerem MSSQL 2008. Wykorzystamy do tego celu standardowe mechanizmy zaszyte w poszczególne programy z pakietu Office. Dla bardziej ambitnych czytelników postaram się przygotować rozwiązanie niestandardowe znacznie rozszerzające standardowe mechanizmy.

Access

Pakiet Office dostarcza zintegrowane środowisko programistyczne przygotowane głównie do przetwarzania danych. Jest to aplikacja MS Access. We wcześniejszych wersjach tego programu operowała ona na plikach MDB lub skompilowanych MDE, w Wersji Aktualnej rozszerzenia plików to .accdb i .accde. Różnice między poszczególnymi wersjami plików z punktu widzenia użytkownika MSSQL 2008 są nieistotne.

Na początku pracy trzeba sobie przygotować niezbędne środowisko pracy, w skład, którego będzie wchodzić połączenie plikowe DSN (ang. Data Source Name, nazwa źródła danych). Opcjonalne jest też takie skonfigurowanie bazy oraz systemu by móc korzystać z danych w bazie z maszyn zewnętrznych.

Plikowe DSN (File DSN) to specjalny plik zawierający informacje o tym, z jakim serwerem i konkretnie, którą bazą się łączymy. Najprostszą metodą przygotowania takiego pliku jest skorzystanie z kreatora dostępnego w Access-e:

Import->External Data -> More -> ODBC Database -> Link to the data source by creating a linked table.

Rysunek 1

Wybieramy zakładkę: File Data Source, następnie klikamy New, z listy wybieramy SQL Server Native Client 10, i klikamy w przycisk Next. Następnie wpisujemy nazwę pliku np. Conn, klikamy Next i Finish. Poczym przechodzimy do kolejnego kreatora, w którym definiujemy szczegóły połączenia.

Rysunek 2

Pierwszym krokiem jest określenie serwera, z którym będziemy pracować. W nazwie tej jest zawarta nazwa maszyny oraz nazwa instancji. W przypadku MSSQL 2008 Express będzie to SQLEXPRESS. Dla przykładu teraz załóżmy, że komputer nosi nazwę TEST. Pełna nazwa wpisana w polu serwer to TEST\SQLEXPRESS. Szczególnym przypadkiem nazwy komputera jest kropka lub (local). Są to synonimy odwołania się bezpośrednio do maszyny lokalnej np. (local)\SQLEXPRESS.

Uwaga: takie definiowanie połączenia na pewno nie zadziała po przeniesieniu pliku na inny komputer. Kolejnym problemem po przeniesieniu pliku może okazać się rozpoznanie nazwy komputera. W sieciach bez odpowiednio skonfigurowanej usługi DNS lub działającego WINS lepszym rozwiązaniem może okazać się podawanie adresu IP maszyny z SQL Serwerem np. 192.168.1.2\SQLEXPRESS

Standardowo zainstalowany SQL Serwer blokuje możliwość łączenia się z maszyn zewnętrznych. Włączenie tej opcji wymaga wykonania kilku czynności tj.

Włączenia protokołu TCP\IP lub Named Pipes

Rysunek 3

Restartu usługi SQL Oraz konfiguracji firewall-a, o ile zachodzi taka konieczność.

Rysunek 4

W kolejnym oknie wybieramy metodę autoryzacji - zintegrowaną z, Windows, czyli będzie przekazywana informacja o koncie, na którym aktualnie pracujemy.

Rysunek 5

Dla przykładu nazwa konta, na którym pracujemy to Jan, nazwa komputera, do którego jesteśmy zalogowani to TEST, wynikiem, czego MSSQL 2008 otrzyma informację podczas logowania, że użytkownik TEST\Jan próbuje uzyskać jakiś dostęp. Tą informację będzie też zwracał sam serwer np. za pomocą funkcji CURRENT_USER. Zaletą takiej metody jest to, że serwer sam wie, kto próbuje się połączyć oraz to, że nie przechowujemy jawnie haseł bazie Accessowej. Wadą jest natomiast to, że jest to dosyć skomplikowane do ustawienia w środowisku bez domeny, Active Directory. Druga metoda opiera się na jawnym przekazaniu loginu i hasła. Domyślnie jest to wyłączone i żeby skorzystać z tej możliwości trzeba najpierw włączyć MIXED MODE na serwerze SQL.

Rysunek 6

Lub też za pomocą T-SQL-a

USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2 GO

Po wykonaniu tej operacji konieczny będzie restart serwera SQL np. mamy taką możliwość w SQL Serwer Management Studio po kliknięciu prawym klawiszem w serwer, który chcemy restartować, lub też linii poleceń

NET STOP MSSQL$SQLEXPRESSNET START MSSQL$SQLEXPRESS

UWAGA: W przypadku, gdy nie posiadamy stosownego konta na serwerze trzeba jest stworzyć za pomocą kreatora:

Rysunek 7

Lub też można skorzystać z poleceń SQLCREATE LOGIN [user01] WITH PASSWORD=N'' MUST_CHANGE

, DEFAULT_DATABASE=[TEST], DEFAULT_LANGUAGE=[polski], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Kolejnym krokiem w kreatorze możemy zmienić domyślną bazę, dla Loginu, z którego korzystamy.

Rysunek 8

Jeżeli nie stworzyliśmy do tej pory bazy, z którą będziemy pracować możemy posłużyć się kreatorem dostępnym w Menagment Studio. Jest on dostępny pod prawym klawiszem myszy w gałęzi Databases.

Rysunek 9

Lub też stworzyć bazę poleceniem SQL:

CREATE DATABASE [baza_testowa] ON PRIMARY ( NAME = N'baza_testowa'

, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\baza_testowa.mdf'

, SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'baza_testowa_log'

, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\baza_testowa_log.ldf'

, SIZE = 1024KB , FILEGROWTH = 10%)

Lub prościej

CREATE DATABASE [baza_testowa]

W ostatnim kroku możemy zmienić defaultowy język z, w którym będzie pracował serwer w trakcie wykonywania operacji za pomocą tego połączenia. Jest to o tyle istotne, że takie

funkcje, jak DATENAME mogą zwracać dziwne wartości w przypadku źle ustawionego języka.

Rysunek 10

Uwaga: Oba te ustawienia można ustawić w MSSQL dla każdego loginu oddzielnie

Rysunek 11

Lub też za pomocą T-SQL-a

USE [master] GO ALTER LOGIN [TEST\Jan] WITH DEFAULT_DATABASE=[BAZA], DEFAULT_LANGUAGE=[polski] GO

Warto tutaj wspomnieć, że istnieje możliwość zdefiniowania połączenia DSN jako wpisu w rejestrze systemu dostępnego w zależności od wybranej opcji dla każdego użytkownika danego systemu (System DSN) lub też tylko dla użytkownika tworzącego (User DSN). Zaleta tak stworzonego połączenia jest wygoda podczas korzystania - gdyż wszystkie niezbędne informacje są w nim już zawarte. Niewątpliwą wadą zaś jest to, że połączenie takie jest widoczne tylko na maszynie, na której zostało zdefiniowane.

Wszystkie opcje związane z zarządzaniem wpisami DSN są dostępne po uruchomieniu odbcad32.exe lub wybraniu Data Sources (ODBC) w panelu sterowania.

Linkowanie tabel

Po przygotowaniu połączenia możemy zacząć korzystać ze zgromadzonych danych. Najłatwiej jest to uczynić poprzez podlinkowanie tabeli: External Data -> More -> ODBC Database -> Link to the data Source by creating a linked table

Rysunek 12

Z listy dostępnych obiektów wybieramy plikowe DSN, które przed chwilą stworzyliśmy

Rysunek 13

Po wybraniu tej opcji pojawi się okienko umożliwiające wybranie kilku obiektów. Dostępna jest też opcja Zapisz hasło. Pozwala ona zapisać hasło do bazy danych w definicji tabeli podlinkowanej. Nie jest to zalecane ze względów bezpieczeństwa.

Rysunek 14

Ewentualnie możemy dodać link za pomocą kody VBA:

DoCmd.TransferDatabase _ acLink _ , "ODBC" _ , "ODBC;DRIVER=SQL Server;SERVER=.\SQLEXPRESS;Trusted_Connection=Yes;DATABASE=Baza" _ , acTable _ , "Tabela" _ , "Tabela_link" _ , True _ , True

W Accessie problem ten rozwiązano tak, że podczas łączenia ze źródłem danych, w którym nie ma zdefiniowanego hasła system prosi o jego podanie. Hasło następnie jest przechowywane w pamięci komputera aż do zakończenia pracy z aplikacją Access. Będzie ono wykorzystane podczas każdego kolejnego połączenia z bazą danych.

Uwaga: Access czasem pyta o Primary Key, w miarę możliwości trzeba wybrać kolumnę z listy.

Rysunek 15

Mogą też wystąpić problemy z aktualizacją rekordów. Problem ten można rozwiązać dodając po stronie serwera SQL kolumnę o dowolnej dozwolonej nazwie i typie danych Timestamp. Proces kończy zaś odświeżenie linku za pomocą kreatora lub kodu VBA. Tak dodaną kolumnę dodajemy np. do formularza, jako pole ukryte.

(obrazki)

Lub też za pomocą T-SQL-a

ALTER TABLE [Users] ADD [TStamp] timestamp NULL

Odświeżenie linku: Database Tools -> Linked Table Menager

Rysunek 16

Lub z pomocą kodu VBA

currentdb.TableDefs("Tabela").RefreshLink

Polecenie takie można wykonać bezpośrednio w edytorze VBE (Alt + F11) w oknie Immediate (Ctrl + G). Polecenie zadziała po wciśnięciu Enter.

Import i Export

Najprostszą metodą importu jest skorzystanie z kreatora dostępnego w Accessie: External Data -> More -> ODBC Database -> Import data from Excel to a new table in Access

Rysunek 17

A następnie wybieramy połączenie z którego mamy zamiar skorzystać.

Ewentualnie możemy wykonać taką operację za pomocą DoCmd.TransferDatabase z poziomu kodu VBA.

DoCmd.TransferDatabase _ acImport _ , "ODBC" _ , "ODBC;DRIVER=SQL Server;SERVER=.\SQLEXPRESS;Trusted_Connection=Yes;DATABASE=Baza" _ , acTable _ , "Tabela" _ , "Tabela_link" _ , True _ , True

Eksport pojedynczego obiektu jest równie prosty, co import. Możemy skorzystać z wbudowanego mechanizmu eksportu obiektu External Data -> More -> ODBC Database lub też skorzystać z menu dostępnego po kliknięciu w prawy klawisz myszy na wybranym obiekcie

Uwaga: eksportowany obiekt trzeba zaznaczyć, W ten sposób możemy eksportować tabele, kwerendy oraz tabele podlinkowane z innych źródeł np. pliki Excel

Ewentualnie możemy wykonać taką operację za pomocą DoCmd.TransferDatabase z poziomu kodu VBA.

DoCmd.TransferDatabase _ acExport _ , "ODBC" _

, "ODBC;DRIVER=SQL Server;SERVER=.\SQLEXPRESS;Trusted_Connection=Yes;DATABASE=Baza" _ , acTable _ , "Tabela" _ , "Tabela" _ , False

Migracja

Do eksportu całej bazy danych wraz z informacjami o indeksach i relacjach możemy skorzystać z kreatora migracji: Database Tools -> Move Data -> SQL Serwer

Rysunek 18

Rysunek 19

Uwaga: Opcja ta może nie działać poprawnie z MSSQL 2008. Działa za to poprawnie z MSSQl 2005

Alternatywą dla wbudowanego kreatora może być oprogramowanie: SQL Server Migration Assistant 2008 for Access V4.0. Pozwoli ono przenieść dane w prosty i wygodny sposób z bazy Access do na serwer MSSQL 2008.

Kwerendy przekazujące

Specjalnym obiektem dostępnym w środowisku Access są kwerendy przekazujące. Od standardowej kwerendy różnią się tym, że są wykonywane bezpośrednio na serwerze, dzięki czemu możemy wykonać operacje standardowo niedostępne dla użytkownika Accessa np. wykorzystać zaawansowane mechanizmy T-SQL.

Aby stworzyć taką kwerendę korzystamy z Create -> Other -> Query Design

W Query type wybieramy Pass-Throught. Okno projektowania w tym momencie zmienia tryb pracy do tekstowego. W tym momencie możemy pisać polecenia w T-SQL-u lub też je skopiować z SQL Serwer Menagment Studio.

Końcową operacją jest przypisanie połączenia ODBC skojarzonego z kwerendą przekazującą. Opcja ta jest dostępną po kliknięciu w Show/Hide -> Property Sheet -> ODBC Connect Str ikonka trzech kropek włączy kreator wyboru połączenia (np. wcześniej zdefiniowane plikowe DSN).

Istotny jest parametr Return Records, jeżeli jest ustawiony na No kwerenda nie będzie zwracać żadnych wyników.

Rysunek 20

EXCEL

Aplikacja Excel jest swego rodzaju scyzorykiem wśród innych aplikacji w pakiecie Office. Potrafi gromadzić dane, analizować, prezentować, liczyć, można pisać zaawansowane makra za pomocą VBA. W zasadzie zastosowanie tej aplikacji jest uzależnione tylko od wiedzy użytkownika Końcowego. Firma Microsoft włożyła wiele wysiłku, aby praca z najnowszymi wersjami pakietu była efektywna i efektowana zarazem. Dostarczono lub poprawiono szereg mechanizmów pozwalających analizować dane i prezentować je w zasadzie za pomocą kilku kliknięć myszy. Tylko jak to sie ma do pracy z MSSQL 2008? Postaram się to pokazać w zwięzłej formie na kilku następnych stronach.

Przygotowanie środowiska pracy

Podobnie jak w MS Access pierwszym krokiem będzie przygotowanie niezbędnych elementów pozwalających wygodnie pracować z danymi z bazy danych. Pierwszym krokiem może być przygotowanie pliku z połączeniem do zewnętrznego źródła danych, którym będzie wcześniej wspomniany MSSQL 2008 Express Edition. Po uruchomieniu Excel-a przechodzimy do: Data -> Get External Data -> From Other Sources -> From SQL Serwer – W tym przypadku Excel sam określa sterownik, z jakiego będziemy korzystać.

Rysunek 21

W tym kroku kreatora wpisujemy nazwę serwera oraz opcjonalnie instancję, do której będziemy się łączyć oraz wybieramy metodę autoryzacji. Do wyboru mamy Logowanie zintegrowane lub login i hasło.

UWAGA: wpisanie, jako nazwy serwera (local) lub spowoduje, że połączenie takie nie będzie działać lub będzie działać niepoprawnie na innej maszynie np. po przesłaniu pocztą

Data -> Get External Data -> From Other Sources -> From Data connection Wizard. Ta opcja sprawia, że jesteśmy wstanie wybrać preferowany sterownik do konkretnego zadania.

Rysunek 22

Z listy sterowników wybieramy np: Other/Advanced -> SQL Server Native Client 10.0. Na następnej stronie kreatora wybieramy serwer, z którym się będziemy łączyć (można wybrać z listy rozwijanej, jeżeli Serwera na niej nie ma to można nazwę wpisać samodzielnie), wybieramy metodę autoryzacji zintegrowaną lub opartą na loginie i haśle a następnie wybieramy bazę danych, z którą będziemy się łączyć. Jeżeli bazy nie ma na Liście to znaczy, że użytkownik w kontekście teraz pracujemy (metoda zintegrowana) lub login i hasło nie mają dostępu do wybranej bazy danych. Na końcu możemy przetestować połączenie. W zakładce Advanced możemy określić TimeOut - jest to o tyle istotne, że czasem operacje które chcemy wykonać są stosunkowo długie. Modyfikacja tego ustawienia sprawi, że serwer sam nas nie rozłączy przed upływem określonego czasu. W zakładce All możemy wybrać szczególny parametr i ustawić go ręcznie. Jest to przydatne w niektórych sytuacjach.

Kolejny krok jest wspólny i polega na wybraniu obiektu, do którego będziemy się łączyć

Rysunek 23

Widzimy tu listę wszystkich obiektów, do których mamy dostęp. Jest informacja o tym w którym schemacie obiekt się znajduje, data stworzenia obiektu oraz typ VIEW - widoki oraz TABLE - tabele.

UWAGA: Widok (VIEW) jest to obiekt po stronie SQL Serwera będący swego rodzaju aparatem przetwarzającym dane z postaci A do postaci B, Tabela (TABLE) jest fizycznym obiektem przechowującym dane.

Następnie możemy wybrać obiekt z listy lub też odznaczyć: Connect to a specyfic table, dzięki czemu połączenie nie będzie łączyć się automatycznie z określonym obiektem w bazie danych. W pewnych okolicznościach jest to wygodne gdyż umożliwia nam wybranie obiektu z listy wszystkich dostępnych obiektów.

Ostatni krok kreatora to stworzenie pliku o podanej nazwie. Możemy ponadto dodać opis dla danego połączenia oraz słowa kluczowe

Rysunek 24

Po kliknięciu w Finish zostanie utworzony pliki zawierający pełną definicję połączenia. W przypadku zaś, gdy wcześniej wskazaliśmy jakiś obiekt uruchomi się kreator odpowiedzialny za pobranie i przetworzenie danych. 

Pobranie danych

Po stworzeniu połączenia możemy pobrać dane wprost do arkusza za pomocą Data -> Get External Data -> Existing Connections. Po kliknięciu w ikonkę otwiera się okno zawierające wszystkie zdefiniowane połączenia. Wybieramy to z którego chcemy skorzystać. Jeżeli połączenie ma zdefiniowany obiekt pojawi się kreator umożliwiający wybranie miejsca, w którym dane mają się znaleźć:

Table PivotTable Report PivotChart And PivotTable Report

Rysunek 25

Kreator ten ponadto umożliwia wskazanie komórki, od której zaczniemy wstawianie danych lub też pozwoli na stworzenie nowego skoroszytu w bieżącym pliku.

Przycisk Properities umożliwia zaawansowaną edycję danego obiektu.

UWAGA: Z bardziej przydatnych możliwości wpisania zapytania SQL zamiast pobierania danych z tabeli lub widoku. Trzeba wybrać zakładkę Definition, Command type zmienić na SQL, a w polu Command text wpisać polecenie SQL np. EXEC usp_test @par='200901'

Dzięki temu uzyskujemy prostą możliwość uruchamiania procedur po stronie SQL-a zawierających zaawansowaną logikę biznesową.

Rysunek 26

Table

Po wybraniu tej opcji dane zostaną umieszczone bezpośrednio w arkuszu. Jeżeli zaznaczymy jakąś komórkę wewnątrz obszaru gdzie znajdują się pobrane dane pojawi się dynamiczna wstążka Table Tools -> Design.

Rysunek 27

Umożliwia ona formatowanie wyników zapytań z bazy danych do postaci przejrzystej tabelki, przekonwertowania do zwykłego arkusza, trwałego odłączenia od źródła danych, usunięcia duplikatów oraz stworzenia tabeli przestawnej z otrzymanych wyników.

UWAGA: Tak wygenerowana tabela przestawna odwołuje się do zakresu danych w Excelu i po modyfikacji danych w bazie i odświeżeniu danych w tabeli przestawnej dane nie ulegną modyfikacji. Żeby dane były poprawne trzeba zaktualizować dane które posłużyły do wygenerowania tabeli przestawnej a dopiero po tym odświeżamy tabelę przestawną.

Dane pobrane wprost do arkusza są swego rodzaju dynamiczną tabelą posiadającą szereg funkcjonalności. do ciekawszych należą między innymi możliwość wypełniania formuł przylegających do obszaru pobranych danych lub zachowanie formatowania i kopiowanie do nowych komórek zajętych przez dane.

PivotTable Report

Po wybraniu tej opcji zostanie utworzony nowy obiekt tabeli przestawnej

Rysunek 28

Jeżeli wybierzemy jakąś komórkę wewnątrz utworzonego obszaru pojawią się dynamiczne wstążki z grupy PivotTable Tools. Umożliwiają one pełną konfigurację oraz dostosowanie szaty graficznej nowo utworzonej tabeli przestawnej. Z przydatnych funkcji mogę wymienić: Options -> Data -> Change Data Source – umożliwiające dynamiczną zmianę źródła danych.

PivotChart And PivotTable Report

Ta opcja różni się od poprzedniej tylko tym, że jest tworzony wykres bazujący na nowo utworzonej tabeli przestawnej.

Idea wykresu przestawnego jest taka, że wykres jest bezpośrednim odwzorowaniem tabeli przestawnej w postaci graficznej. Jakakolwiek zmiana w ustawieniu tabeli (grupowanie, funkcje agregujące, filtry) wpływa bezpośrednio na wykres.

Programowanie w VBA

Rozszerzenie funkcjonalności aplikacji z pakietu Office jest możliwe na kilka sposobów. Jednym z nich jest pisanie makr w języku VBA (Visual Basic For Application). Jest to zubożona wersja języka programowania VB 6.0. W dzisiejszych czasach język ten jest wręcz archaiczny. Celem tego podrozdziału jest przedstawienie możliwości wykorzystania VBA wraz z MSSQL 2008 Express, a nie nauka od podstaw. Zakładam, więc że czytelnik posiada podstawową wiedzę na temat pisania makr.

Na początku trzeba przedstawić kilka pojęć, które przewiną się w tym tekście:

ODBC – Open DataBase Connectivity – Jest to interface programistyczny do łączenia z bazami danych.

OLEDB – Object Linking and Embedding, Database – Podobnie jak ODBC, jest to interface programistyczny do łączenia z bazami danycch. W odróżnieniu od ODBC potrafi się łączyć nie tylko z bazami SQL ale również z plikami.

ADO – ActiveX Data Object – Interface pozwalający wykorzystać dane pobrane z zewnętrznego źródła w aplikacji.

Connection String – ciąg tekstowy posiadający elementy umożliwiające połączenie z bazą danych

Obiekt Connection – Obiekt ADO odpowiedzialny za połączenie z bazą danych

Obiekt Command – Obiekt ADO będący pośrednikiem między programem a procedurą. Umożliw stworzenie sparametryzowanego zapytania.

Obiekt Recordset – Obiekt ADO zawierający wynik zapytania w postaci recordsetu.

Naszą pracę z VBA powinniśmy rozpocząć od schematu blokowego postępowania z bazami danych.

Uruchomienie edytora VBA: Alt + F11 Dodanie referencji do projektu (tylko raz podczas tworzenia projektu): Tools ->

References

Rysunek 29

Deklaracja zmiennych Otwarcie połączenia (obiekt Connection) Stworzenie obiektu Command Dodanie lub ustawienie parametrów

Wykonanie zapytania (i ewentualne zwrócenie obiektu Recordset) Praca z obiektem Recordset Zamknięcie połączenia z bazą Wyczyszczenie obiektów

Dodanie referencji do projektu jest konieczne, gdy chcemy skorzystać z pełni funkcjonalności edytora VBE oraz gdy jawnie deklarujemy typy zmiennych. W pewnych sytuacjach dodanie referencji przeszkadza bardziej niż pomaga, np. gdy próbujemy uruchomić makro na innej maszynie z niedoinstalowanymi komponentami. Taka sytuacja spowoduje błąd i makro się nie uruchomi. Swego rodzaju zabezpieczeniem jest korzystanie ze zmiennych obiektowych i tworzenie obiektu za pomocą instrukcji CreateObject. Poniżej przykład jawnego deklarowania zmiennych i niejawnego z wykorzystaniem Zmiennych obiektowych.

Sub test_1() Dim aConn As ADODB.Connection Set aConn = New ADODB.Connection ' dalszy kod aplikacji Set aConn = NothingEnd Sub

Sub test_2() Dim aConn As Object Set aConn = CreateObject("ADODB.Connection") ' dalszy kod aplikacji Set aConn = NothingEnd Sub

Sporym minusem drugiego sposobu jest brak InteliSense w edytorze, niewątpliwą zaletą brak konieczności tworzenia referencji do biblioteki źródłowej.

Otwarcie połączenia jest pierwszym etapem pracy z samą bazą. Żeby tego dokonać trzeba zbudować odpowiedni Connection String dostosowany do ustawień samej bazy danych. Przykładowe ciągi znajdują się poniżej:

Dla połączenia z jawnie deklarowanym hasłem i nazwą użytkownika:

aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;Uid=Użytkownik;Pwd=Hasło;”

Dla połączenia z wykorzystaniem zintegrowanej metody logowania:

aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;Trusted_Connection=yes;”

W przypadku zaś, gdy chcemy zapytać o login I hasło lub wybrać metodę zintegrowaną trzeba posłużyć się pewnym trikiem z ustawieniem jednego parametru połączenia

aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;"aConn.Properties("Prompt") = adPromptAlways

Przykładowy kod z wykorzystaniem ostatniej metody:

Sub test_3() Dim aConn As ADODB.Connection Set aConn = New ADODB.Connection aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" aConn.Properties("Prompt") = adPromptAlways aConn.Open MsgBox aConn.State aConn.Close Set aComm = Nothing Set aConn = NothingEnd Sub

Komentarza wymaga tylko aConn.State – Właściwość ta mówi nam, że połączenie jest otwarte (wartość 1), zamknięte (wartość 0) lub jest w trakcie nawiązywania połączenia (wartość 2). Jest to również najszybsza i najpewniejsza metoda sprawdzenia czy rzeczywiście nawiązaliśmy połączenia z bazą danych.

Konstrukcja ADO pozwala pomijać pewne kroki w określonych sytuacjach np. dozwolone jest stworzenie Recordset-u wprost z połączenia gdyż obiekt Connection zawiera metodę Execute. Wiąże się to jednak z wieloma ograniczeniami i jest przydatne tylko do bardzo prostych zapytań.

Sub test_4() Dim aConn As ADODB.Connection Set aConn = New ADODB.Connection aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" aConn.Properties("Prompt") = adPromptAlways aConn.Open aConn.Execute "DELETE FROM TABELA", False aConn.Close Set aComm = Nothing Set aConn = NothingEnd Sub

Dlatego też często korzysta się z obiektu Command pozwalającego na bardziej zaawansowaną pracę z zapytaniami między innymi natywnie wspiera parametry oraz umożliwia parametryzowanie procedur oraz prostych zapytań SQl-owych. Przykład kodu parametryzującego kod SQL i wykonujący go dwukrotnie:

Sub test_5() Dim aConn As ADODB.Connection Dim aComm As ADODB.Command Set aComm = New ADODB.Command Set aConn = New ADODB.Connection aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" aConn.Properties("Prompt") = adPromptAlways aConn.Open Set aComm.ActiveConnection = aConn aComm.CommandType = adCmdText

aComm.CommandText = "DELETE FROM TABELA WHERE ID = ?" aComm.Parameters.Append aComm.CreateParameter(, adInteger, adParamInput) aComm.Parameters(1).Value = 1 aComm.Execute False aComm.Parameters(1).Value = 2 aComm.Execute False aConn.Close Set aComm = Nothing Set aConn = NothingEnd Sub

UWAGA: Parametryzacja zapytania to zastąpienie fragmentu SQL-a za pomocą znaku zapytania. Pod taki znak następnie jest podstawiany parametr utworzony za pomocą kodu VBA lub też pobrany bezpośrednio z tablicy.

aComm.Execute False,array(2)

Gdzie Array(2) to tablica zawierająca parametry podstawiane w kolejności pojawienia się w tablicy do kolejnych znaków zapytania.

Obiekt Command automatycznie pobiera listę parametrów dostępną dla procedury składowanej. Dzięki temu faktowi nie musimy tworzyć kolekcji parametrów samodzielnie.

Sub test_6() Dim aConn As ADODB.Connection Dim aComm As ADODB.Command Set aComm = New ADODB.Command Set aConn = New ADODB.Connection aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" aConn.Properties("Prompt") = adPromptAlways aConn.Open Set aComm.ActiveConnection = aConn aComm.CommandType = adCmdStoredProc aComm.CommandText = "dbo.usp_procedura" aComm.Parameters("@parametr").Value = 1 aComm.Execute False aConn.Close Set aComm = Nothing Set aConn = NothingEnd Sub

W przypadku gdyby wystąpiły jakieś problemy z pobraniem tej kolekcji trzeba sprawdzić czy użytkownik w kontekście, którego pracujemy ma uprawnienia Execute do wybranych obiektów:

W przypadku gdyby się okazało, że nie posiadamy stosownych uprawnień możemy je nadać za pomocą T-SQL-a

GRANT EXECUTE ON [dbo].[sp_ddopen] TO [guest]

GRANT EXECUTE ON [dbo].[sp_sproc_columns] TO [guest] GO

W sytuacji podbramkowej możemy samodzielnie stworzyć kolekcję parametrów na podstawie następującego zapytania SQL:

Sub test_7() Dim aConn As ADODB.Connection Dim aComm As ADODB.Command Set aComm = New ADODB.Command Set aConn = New ADODB.Connection aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" aConn.Properties("Prompt") = adPromptAlways aConn.Open Set aComm.ActiveConnection = aConn aComm.CommandType = adCmdStoredProc aComm.CommandText = "dbo.usp_procedura" aComm.Parameters.Append aComm.CreateParameter("@parametr", adInteger, adParamInput, 1) aComm.Parameters("@parametr").Value = 1 aComm.Execute False aConn.Close Set aComm = Nothing Set aConn = NothingEnd Sub

Przydatna może tu być tablica translacji miedzy typami danych VBA a SQL1

DataType Enum Value

Access SQLServer

adBigInt 20   BigInt (SQL Server 2000 +)

adBinary 128   Binary

TimeStamp

adBoolean 11 YesNo Bit

adChar 129   Char

adCurrency 6 Currency Money

SmallMoney

adDate 7 Date DateTime

adDBTimeStamp 135 DateTime (Access 97 (ODBC)) DateTime

SmallDateTime

adDecimal 14    

adDouble 5 Double Float

adGUID 72 ReplicationID (Access 97 (OLEDB)), (Access 2000 (OLEDB))

UniqueIdentifier (SQL Server 7.0 +)

adIDispatch 9    

adInteger 3 AutoNumber Identity (SQL Server 6.5)

Integer Int

Long  

adLongVarBinary 205 OLEObject Image

1 http://www.w3schools.com/ADO/ado_datatypes.asp

adLongVarChar 201 Memo (Access 97) Text

Hyperlink (Access 97)

adLongVarWChar 203 Memo (Access 2000 (OLEDB)) NText (SQL Server 7.0 +)

Hyperlink (Access 2000 (OLEDB))

adNumeric 131 Decimal (Access 2000 (OLEDB)) Decimal

Numeric

 

 

adSingle 4 Single Real

adSmallInt 2 Integer SmallInt

adUnsignedTinyInt 17 Byte TinyInt

adVarBinary 204 ReplicationID (Access 97) VarBinary

adVarChar 200 Text (Access 97) VarChar

adVariant 12   Sql_Variant (SQL Server 2000 +)

adVarWChar 202 Text (Access 2000 (OLEDB)) NVarChar (SQL Server 7.0 +)

adWChar 130   NChar (SQL Server 7.0 +)

Obiekt wyjściowy Recordset, który otrzymaliśmy po wykonaniu Execute staje się doskonałym półproduktem, z którym możemy zrobić wiele ciekawych rzeczy. W przypadku wykorzystania w Excelu uzyskujemy możliwość:

Wypełnienia obiektu typu QueryTable (kwerenda).

UWAGA: Odwołanie do obiektu Me oznacza, że kod znajduje się w skoroszycie a nie module

Wypełnienia obiektu PivotCache (tabela przestawna).

Sub test_8() Dim aConn As ADODB.Connection Dim aComm As ADODB.Command Dim aRs As ADODB.Recordset Set aComm = New ADODB.Command Set aConn = New ADODB.Connection aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" aConn.Properties("Prompt") = adPromptAlways aConn.Open Set aComm.ActiveConnection = aConn aComm.CommandType = adCmdStoredProc aComm.CommandText = "dbo.usp_procedura" aComm.Parameters("@parametr").Value = 1 Set aRs = aComm.Execute With .PivotTables("PivotTable1").PivotCache Set .Recordset = aRs .Refresh End With aConn.Close Set aComm = Nothing

Set aConn = NothingEnd Sub

Dane tabeli przestawnej są trzymane w obiekcie PivotCache. Obiekt ten może zawierać dane dla kilku różnych tabel przestawnych i wykresów przestawnych (kopii orginału), przez co ładowanie danych może się odbyć tylko raz.

UWAGA: Obiekty przed wypełnieniem musi istnieć w arkuszu

Bezpośredniego wklejenia danych do arkusza.

Sub test_10() Dim aConn As ADODB.Connection Dim aComm As ADODB.Command Dim aRs As ADODB.Recordset Dim rTmp As Range Dim x As Integer Set aComm = New ADODB.Command Set aConn = New ADODB.Connection Set rTmp = Me.Range("A1") aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" aConn.Properties("Prompt") = adPromptAlways aConn.Open Set aComm.ActiveConnection = aConn aComm.CommandType = adCmdText aComm.CommandText = "select * from sys.tables" Set aRs = aComm.Execute For x = 0 To aRs.Fields.Count - 1 With rTmp.Offset(0, x) .Value = aRs.Fields(x).Name .Interior.ColorIndex = 15 .Interior.Pattern = xlSolid End With Next ' rTmp.Offset(1).CopyFromRecordset aRs aConn.Close Set aComm = Nothing Set aConn = NothingEnd Sub

Metoda .CopyFromRecordset wkleja dane bez nagłówków. Dlatego też jest obecna pętla tworząca prosty nagłówek na podstawie kolekcji z nazwami kolumn.

Pracy z poszczególnymi rekordami i np. wypełnienie listbox-a elementami.

Sub test_11() Dim aConn As ADODB.Connection Dim aComm As ADODB.Command Dim aRs As ADODB.Recordset Dim x As Integer

Set aComm = New ADODB.Command Set aConn = New ADODB.Connection x = 0 With Me.ListBox1 .Clear .ColumnCount = 3 End With aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" aConn.Properties("Prompt") = adPromptAlways aConn.Open Set aComm.ActiveConnection = aConn aComm.CommandType = adCmdText aComm.CommandText = "select * from sys.tables" Set aRs = aComm.Execute While Not aRs.EOF With Me.ListBox1 .AddItem aRs.Fields("object_id").Value .List(x, 1) = aRs.Fields("name").Value .List(x, 2) = aRs.Fields("type").Value x = x + 1 aRs.MoveNext End With Wend aConn.Close Set aComm = Nothing Set aConn = NothingEnd Sub

Wyjaśnienia wymaga tylko Not aRs.EOF- jest to warunek zwracający logiczną prawdę w momencie dotarcia do końca zbioru rekordów. Przesuwanie jest natomiast realizowane za pomocą MoveNext.

W przypadku zaś, gdy pracujemy z Accessem możemy wykonywać następujące operacje:

Wykonać kod SQL bezpośrednio na serwerze bez konieczności przebudowy kwerendy przekazującej. Kod programu jst zupełnie analogony jak przykład 4 i 5. Przykład 11 zaś może służyć za wzór postępowania w przypadku pracy krokowej z Recordsetem.

Programowo wypełnić kontroli Recordset-em – nie trzeba do niczego się odwoływać

Sub test_12() Dim aConn As ADODB.Connection Dim aComm As ADODB.Command Dim aRs As ADODB.Recordset Set aComm = New ADODB.Command Set aConn = New ADODB.Connection aConn.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" aConn.Properties("Prompt") = adPromptAlways aConn.Open

Set aComm.ActiveConnection = aConn aComm.CommandType = adCmdStoredProc aComm.CommandText = "dbo.usp_procedura" aComm.Parameters("@parametr").Value = 1 Set aRs = aComm.Execute Set Me.Combo0.Recordset = aRs

Set aComm = Nothing Set aConn = NothingEnd Sub

Programowo wypełnić formularz danymi.

Private aConn As ADODB.ConnectionPrivate aRs As ADODB.Recordset

Sub test_13() Set aConn = New ADODB.Connection With aConn .ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=Baza;" .Properties("Prompt") = adPromptAlways .CursorLocation = adUseClient .Open End With Set aRs = New ADODB.Recordset

With aRs .Open "TABELA", aConn, adOpenKeyset, adLockPessimistic End With Set Me.Recordset = aRs End Sub

Private Sub Form_Load() Call test_13End Sub

Private Sub Form_Unload(Cancel As Integer) aConn.Close aRs.Close Set aConn = Nothing Set aRs = NothingEnd Sub

W tym przypadku problematyczne może być zachowanie edytowalności danych. Efekt ten uzyskamy poprzez zmianę sposobu pobierania danych. Zamiast przedstawionego obiektu Command skorzystamy w tym przypadku z możliwości obiektu Recordset. W przykładzie tym widzimy także dwie dodatkowe procedury. Są one wykonywane podczas ładowania formularza (Form_Load) oraz zamykania (Form_Unload). Dzięki tym procedurom formularz automatycznie się wypełni danymi na starcie oraz zamknie połączenie z bazą na końcu pracy.

UWAGA: Formularz wypełniony programowo i zapisany może zachować dziwny obiekt w DATASOURCE, co powoduje, że występują błędy podczas ponownego uruchamiania formularza. Rozwiązaniem jest wyczyszczenie tej właściwości formularza w trybie edycji i ponowny zapis.

Czego nie możemy zaś zrobić to programowo wypełnić raportu za pomocą Recordset-u

UWAGA: kontrolki i formularze po wypełnieniu programowym mają brzydką tendencję do wyświetlania komunikatu o braku możliwości połączenia z bazą danych. Można łatwo to ominąć ustawiając obiekt Connection takiego Recordset-u na Nothing. W przypadku wypełnionego formularza po takiej operacji tracimy możliwość edycji rekordów w bazie.

Set aTmp = aRsSet aTmp.ActiveConnection = NothingSet oObject.Recordset = aTmp

W przypadku Access-a, niewątpliwą zaletą korzystania z rozwiązań stricte programowych jest brak konieczności definiowania linków tabel w pliku. Zmniejsza to ogólną ilość obiektów w projekcie oraz zwiększa bezpieczeństwo samej aplikacji.

Dobrą praktyką jest przechowywanie obiektu połączenia w zmiennej i wielokrotne wykorzystanie. Zyskujemy dzięki temu czas, który byłby poświęcany na każdorazowe otwieranie połączenia na serwerze. Doskonale do tego nadają się zmienne globalne zdefiniowane w module:

Public aRs As ADODB.RecordsetPublic aConn As ADODB.ConnectionPublic aComm As ADODB.Command

Kolejną dobrą praktyką jest dzielenie kodu na mniejsze fragmenty odpowiedzialne za poszczególne operacje. Problemem dla niektórych może być przekazywanie parametrów niebędących standardowymi zmiennymi. Zasadniczo przekazywanie obiektu, jako zmiennej do funkcji lub pobranie obiektu z funkcji nie różni się od podstawienia zwykłej zmiennej np. liczbowej. Trzeba tylko pamiętać o tym, że jak podstawiamy do jakiejś zmiennej obiekt nieważne czy z funkcji czy też z innego obiektu musimy skorzystać ze słowa kluczowego SET.

Sub test_14() Dim aConn As ADODB.Connection Dim aRs As ADODB.Recordset Set aConn = New ADODB.Connection With aConn .ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=rar;" .Properties("Prompt") = adPromptAlways .CursorLocation = adUseClient .Open Set Me.Combo0.Recordset = fGetRecordset(aConn, "SELECT * FROM sys.TABLES") .Close End With

Set aConn = NothingEnd Sub

Function fGetRecordset(oConn As ADODB.Connection, sSql As String) As ADODB.Recordset Dim aComm As ADODB.Command Set aComm = New ADODB.Command With aComm Set .ActiveConnection = oConn .CommandType = adCmdText .CommandText = sSql Set fGetRecordset = .Execute End With Set aComm = NothingEnd Function

Private Sub Form_Load() test_14End Sub

Widzimy tu praktycznie wykorzystanie funkcji zwracającej Recordset po przekazaniu dwu parametrów: obiektu połączenia i ciągu tekstowego z zapytaniem SQL.

Ostatnią kwestią, którą można poruszyć jest wykorzystanie Eventów związanych z obiektami ADO. Możemy z nich korzystać tylko i wyłącznie w momencie, gdy deklarujemy zmienną ze specjalnym słowem kluczowym WithEvents oraz gdy deklaracja taka odbywa się w klasie lub innym obiekcie.

UWAGA: Eventy nie są dostępne w modułach.

Po takim zabiegu w edytorze będą dostępne dodatkowe obiekty dostępne w rozwijanej liście. Zaś po wybraniu jakiegoś obiektu na liście po prawej stronie dostępne będą wszystkie zdarzenia, jakie generuje dany obiekt.

UWAGA: Event – jest to zdarzenie, które może uruchomić procedurę. Gdy zajdą okoliczności, w których dane zdarzenie ma zadziałać to podejmowana jest próba uruchomienia procedury obsługującej takie zdarzenie, zaś program główny jest wstrzymany aż do momentu zakończenia działania procedury obsługującej.

Obiekty Recordset i Command posiadają szereg Eventów pozwalających przechwytywać zdarzenia związane z korzystaniem z bazy danych. Do ciekawszych i bardziej przydatnych zdarzeń należą te związane z połączeniem z bazą, wykonaniem zapytania, transakcjami. Dzięki nim możemy w znacznym stopniu poszerzyć funkcjonalność naszych aplikacji.

Private WithEvents aConn As ADODB.ConnectionPrivate WithEvents aRs As ADODB.RecordsetPrivate aComm As ADODB.Command

Sub test_15() Set aConn = New ADODB.Connection With aConn

.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=rar;" .Properties("Prompt") = adPromptAlways .CursorLocation = adUseClient .Open Set Me.Combo0.Recordset = fGetRecordset(aConn, "SELECT * FROM sys.TABLES") .Close End With Set aConn = NothingEnd Sub

Function fGetRecordset(oConn As ADODB.Connection, sSql As String) As ADODB.Recordset Set aComm = New ADODB.Command With aComm Set .ActiveConnection = oConn .CommandType = adCmdText .CommandText = sSql Set fGetRecordset = .Execute End With Set aComm = NothingEnd Function

Private Sub aConn_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) Debug.Print adStatusEnd Sub

Private Sub aConn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection) Debug.Print adStatusEnd Sub

Private Sub Form_Load() test_15End Sub

Niezwykle użyteczne Eventy zaprezentowane w tym przykładzie umożliwiają odczytanie statusu serwera po połączeniu się z serwerem - aConn_ConnectComplete, oraz odczyt statusu po wykonaniu jakiegokolwiek zapytania - aConn_ExecuteComplete. Dodatkowo Eventy te dostarczają szereg obiektów wykorzystywanych w trakcie trwania zapytania. Do najbardziej użytecznych zaliczymy pError gdyż w przypadku wystapienia błędu pomoże ona nam uzyskać pełną I wyczerpującą informację o zaistniałym błędzie.

MSSQL

MSSQL 2008 Express udostępnia mechanizmy pozwalające bezpośrednio wykorzystywać dane zawarte w plikach Excel-a i Access-a. Do tego celu możemy wykorzystać aż trzy mechanizmy różniące się zasadą działania oraz poziomem uprawnień koniecznych do skorzystania z danego mechanizmu.

Do najpopularniejszego mechanizmu możemy zaliczy Linked Servers. Mechanizm ten wymaga zdefiniowania specjalnego obiektu nazywanego linkiem do bazy danych. Jest to zdefiniowana i przechowywana w bazie systemowej definicja połączenia do zdalnego źródła danych. Połączenie takie daje nam możliwość wykonywania zdalnych zapytań SQL i przekazania wyniku bezpośrednio do zapytania MSSQL. Do utworzenia takiego obiektu potrzebne są uprawnienia administratora serwera, gdyż z tak utworzonego obiektu będziemy mogli korzystać w każdej bazie danych. Do stworzenia obiektu możemy wykorzystać SQL SERVER MENAGMENT STUDIO:

Rysunek 30

Lub też możemy skorzystać z T-SQL-a.

EXEC master.dbo.sp_addlinkedserver @server = 'baza_xls', @srvproduct = 'Jet 4.0', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'c:\test\test.xls', @provstr = 'Excel 8.0'

GO

Tak utworzony obiekt możemy później wykorzystać w zapytaniu SQL

SELECT * FROM baza_xls...[Arkusz1$]

UWAGA: wykorzystujemy tu czteroczłonową nazwę tabeli. Pierwszy człon jest to nazwa linku reprezentującego zdalne źródło danych

Dodatkową zaletą jest możliwość wykorzystania polecenia OPENQUERY. Polecenie to tworzy swego rodzaju wirtualną tabelę, której danymi są rekordy zwrócone przez zapytanie wewnętrzne. Przykładowe zapytanie:

SELECT * FROM OPENQUERY (baza_xls, 'SELECT * FROM [Arkusz1$]') as t

Lub

SELECT * FROM OPENQUERY (baza_xls, 'SELECT * FROM [obszar_nazwany]') as t

Analogicznie możemy korzystać z plików Access-a

EXEC sp_addlinkedserver @server = 'baza_access', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:\test\baza.mdb'

SELECT * FROM baza_access...tabela

Lub

SELECT * FROM OPENQUERY (baza_access,'SELECT * FROM TABELA') as t

UWAGA: wadą stosowania OPENQUERY jest brak możliwości definiowania wewnętrznego zapytania w zmiennej. Zmusza to osobę korzystającą do parametryzowania całego zapytania po stronie SQL i wykonanie go za pomocą EXECUTE lub też sp_executesql.Niedogodność tą można ominąć wykorzystując składnię EXECUTE (…) AT LinkedServerZ kolei to rozwiązanie wymaga specyficznej konfiguracji samej definicji linku do źródła danych.

Zamieszczone przykłady mają zastosowanie do plików Access-a i Excel-a do wersji 2003 włącznie. Pliki od wersji 2007 potrzebują nowszego sterownika oraz modyfikacji connection string-a podczas dodawania linków. Stosowne pliki znajdują się na stronie Microsoft-u pod nazwą: „2007 Office System Driver: Data Connectivity Components”. Wcześniejsze przykłady po modyfikacji wyglądałyby następująco:

Dla Excel-a

EXEC sp_addlinkedserver @server = 'baza_xls',

@srvproduct= 'ExcelData', @provider= 'Microsoft.ACE.OLEDB.12.0', @datasrc= 'C:\test\test.xlsx',@provstr= 'EXCEL 12.0' ;

Oraz dla Access-a

EXEC sp_addlinkedserver @server = 'baza_accdb', @provider = 'Microsoft.ACE.OLEDB.12.0', @srvproduct = 'OLE DB Provider for ACE', @datasrc = 'C:\test\baza.accdb'

Wykonanie zapytania bezpośrednio poprawiającego dane w Accessie. Pierwszym krokiem jest uruchomienie RPC (Remote Procedure Call) dla określonego linku:

EXEC master.dbo.sp_serveroption @server=N'baza_access', @optname=N'rpc out', @optvalue=N'true'GO

Przykładowe zapytanie SQL w dialekcie JET wykonane na MSSQL 2008

exec ('DELETE TableOut.[Employee ID], TableOut.[Employee Number]FROM TableOutWHERE (((TableOut.[Employee ID]) Is Null) AND ((TableOut.[Employee Number]) Is Null));') at baza_access

Kolejnym niezwykle użytecznym mechanizmem jest możliwość parametryzowania zapytań. Przykład takiej parametryzacji:

exec ('UPDATE TableOut SET TableOut.[Employee ID] = ? WHERE (((TableOut.[Employee ID]) Is Null));',3) at baza_access

Parametrów może być więcej, i będą one podawane w kolejności umieszczenia. Kolejne parametry są rozdzielane przecinkiem.

Ostatnim aspektem zdalnego wykonywania zapytań w bazie Accessowej jest uruchamianie kwerend parametrycznych z poziomu MSSQL:

exec ('exec kw_update @nr = ? ',3) at baza_access

Istnieje również możliwość wykonywania operacji na danych za pomocą T-SQL-a. Jest to jednak w pewnych sytuacjach wolniejsze i mniej wygodne. Powodem tego jest konieczność otwarcia zestawu danych za pomocą operacji SELECT. Przykładowe zapytanie przedstawiam poniżej:

UPDATE OPENQUERY(baza_access, 'select * from TableOut')SET [Employee ID] = 4WHERE ([Employee ID] = 3)

Kolejne dwie metody podłączenia do zewnętrznego źródła danych to w zasadzie pewna wariacja już wspomnianych polegająca na tym, że nie ma konieczności definiowania linku w

bazie danych, dlatego są określane mianem połączeń AD-HOC. Definicja połączenia jest zawarta w parametrach polecenia OPENDATASOURCE lub OPENROWSET.

Uwaga: zew względów bezpieczeństwa połączenia AD-HOC są domyślnie wyłączone. Można je włączyć za pomocą SQL-a

sp_configure 'show advanced options',1reconfiguregosp_configure 'Ad Hoc Distributed Queries',1reconfigurego

Ponadto polecenia OPENDATASOURCE i OPENROWSET wymagają ponadto uprawnień administratora, co może być kłopotliwe w pewnych sytuacjach.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\test\xl..xls;Extended Properties=Excel 8.0')...[Arkusz1$]

Lub

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\xl..xls', [Arkusz1$]) as t

Wszystkie wymienione metody umożliwiają dwustronną komunikację ze źródłem danych o ile źródło to na to pozwoli. Do komunikacji tak jak można było już zauważyć wykorzystywane są standardowe sterowniki OLEDB dostępne w systemie. Problematyczne może być podpinanie się do danych zapisanych w nowym Excel-u lub Accessie, bez instalacji tego pakietu na maszynie z SQL serwerem. Można to rozwiązać poprzez doinstalowanie stosowych sterowników OLEDB w systemie.

Istnieje również możliwość umieszczania danych w plikach Excel-a z poziomu MSSQL-a bez wykorzystywania dodatkowych narzędzi czy też jakiegoś oprogramowania niestandardowego. Przykładowa konstrukcja dołącza dane do podlinkowanego wcześniej pliku:

INSERT INTO openquery(baza_xls, 'SELECT Name, Date FROM [Sheet1$]') SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs

Lub do wskazanego pliku.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=e:\test\1202_testing.xls;', 'SELECT Name, Date FROM [Sheet1$]') SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs

UWAGA: Plik Excel-a musi istnieć przed rozpoczęciem eksportu

W przypadku, gdy korzystamy z podlinkowanego pliku Excel-a oraz uruchomimy w tym linku obsługę RPC jesteśmy wstanie tworzyć oraz modyfikować strukturę danych na wzór

normalnej bazy danych za pomocą instrukcji SQL. Przykład tworzenia nowej zakładki będącej swego rodzaju tabelą:

exec ('CREATE TABLE Datads(Staff_Nox NUMBER, Salx CURRENCY, Namex TEXT, Boolyx BIT, Regionx NUMBER, Datex DATETIME)

') at baza_xls

Lub też modyfikacja Tabeli:

exec ('ALTER TABLE Datads ADD COLUMN UnitPrice CURRENCY') at baza_xls

Możemy tu wykorzystać większość instrukcji z dialektu JET SQL o ile są wspierane przez Excel-a. Próba wykonania tego typu komendy skończy się wygenerowaniem błędu. Przykładowe zapytanie:

exec ('ALTER TABLE Datads ADD COLUMN Item TEXT CONSTRAINT UniqueConstraint UNIQUE') at baza_xls

Wygeneruje błąd:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "baza_xls" returned message "Operation is not supported for this type of object.".Msg 7215, Level 17, State 1, Line 1

Could not execute statement on remote server 'baza_xls'.

Import i Export danych kreatorem: Import and Export Data (32-bit)

Kreator ten instalowany wraz z instancją MSSQL 2008 Express umożliwia łaty transfer danych zarówno z jak i do serwer-a. Sam kreator jest bardzo intuicyjny i wymaga jedynie wskazania źródła danych np. pliku Excel oraz celu np. nazwanej instancji SQL Serwera za pośrednictwem SQL Server Native Client 10.

Rysunek 31

Z dostępnych dodatkowych opcji mamy tu możliwość utworzenia nowej bazy danych lub też wybrania istniejącej

Rysunek 32

Kolejnym krokiem jest wybranie opcji kopiowania wskazanych skoroszytów lub też mamy możliwość samodzielnego napisania zapytania SQL importującego dane

Rysunek 33

W kolejnym kroku mamy możliwość podglądu danych oraz modyfikacji mapowań nazw kolumn oraz typów danych, jaka nastąpi w procesie przenoszenia danych między źródłem a bazą docelową

Rysunek 34

Ostatni krok pozwala uruchomić przenoszenie danych.

Rysunek 35

Końcowa informacja prze uruchomieniem transferu danych:

Rysunek 36