Access – jako klient do wyświetlania i modyfikacji danych SQL Server

W ostatnim czasie miałem okazję pracować przy dosyć ciekawym przedsięwzięciu – chodziło mianowicie o to aby użytkownicy mogli w szybki i bezbolesny sposób modyfikować dane zawarte w SQL Server. Na myśl przychodzi nam język TSQL i Management Studio – jednakże w tym przypadku takie połączenie nie wchodziło w grę ze względu na to, że użytkownicy którzy takich zmian mieli dokonywać nie byli osobami technicznymi. Mieliśmy kilka dostępnych opcji aby osiągnąć pożądane rezultaty jednakże klient bardzo chciał aby można było to robić przy użyciu narzędzi zawartych w Microsoft Office. Tak właśnie zrodził się pomysł użycia Access’a czyli nieco zapomnianej już funkcjonalności lokalnej bazy danych zawartej we wspomnianym pakiecie biurowym. W ramach niniejszego artykułu powiemy sobie jak użyć Accessa jak narzędzia klienckiego dla tabel zawartych w bazie SQL Server.

Pierwszym krokiem przedstawienia tej prostej funkcjonalności będzie stworzenie obiektów bazodanowych na których będziemy działać. Składają się na nie przykładowa baza danych o nazwie AccessClientDemo oraz zawarta w niej tabela Users składająca się z identyfikatora, imienia, nazwiska oraz daty urodzenia:

CREATE DATABASE AccessClientDemo
GO

USE AccessClientDemo
GO

CREATE TABLE dbo.Users
(
id int identity,
name varchar(50),
surname varchar(50),
birthdate date
)
GO

Mając już gotowe obiekty możemy otworzyć Accessa – naszym oczom powinien ukazać się obraz podobny do poniższego gdzie dla przykładu stworzymy sobie nową bazę danych (Blank desktop database):

W następnym kroku możemy wybrać lokalizację i nazwę naszej lokalnej bazy danych:

Stworzona zostanie dla nas baza danych i automatycznie pusta tabela – możemy ją usunąć. Naszym celem jest to aby połączyć się do bazy SQL Server więc możemy wybrać na wstążce External Data, a następnie ODBC Database.

W tym miejscu pojawi się okno połączeniowe do bazy danych. Kluczowe jest to aby wybrać drugą opcję tj. Link to the data source by creating a linked table – tzn nasza aplikacja będzie działać jako klient i nie będzie pobierała żadnych danych (do tego służy opcja numer 1). Po wybraniu tej opcji zatwierdzamy OK:

Dalej musimy stworzyć źródło danych ODBC – jeśli chcemy stworzyć źródło danych dla bieżącego użytkownika możemy to zrobić w ten właśnie sposób – w innym przypadku tj. gdy chcemy stworzyć źródło dla lokalnego komputera to musimy zrobić to uruchamiając aplet dodawania źródła ODBC jako administrator. Na ten moment stwórzmy ODBC dla użytkownika klikając przycisk Nowe:

Definiowanie nowego połączenia nie powinno przysparzać problemów i w pierwszym kroku sprowadza się do wybrania odpowiedniego sterownika:

This slideshow requires JavaScript.

a następnie do zbudowania łańcucha połączeniowego poprzez podanie nazwy serwera, sposobu uwierzytelnienia itp:

This slideshow requires JavaScript.

Po prawidłowym stworzeniu źródła można wybrać tabelę do, której będziemy się łączyć – wybierzmy ją z listy  zatwierdzając przyciskiem OK. Kolejną rzeczą jest wybranie kolumn, które chcemy mieć widoczne w Access:

This slideshow requires JavaScript.

Po tym jak wyświetli się nasza tabela możemy dodawać, modyfikować i usuwać rekordy w standardowy sposób:

Ciekawie przedstawiają sie zapytania podejrzane przez SQL Server profiler jakie Access wysyła do SQL Server – np. poniżej zapytanie wstawiające dane do tabeli:

exec sp_executesql N'INSERT INTO  "dbo"."Users"  
("name","surname","birthdate") VALUES (@P1,@P2,@P3)',
N'@P1 varchar(50),@P2 varchar(50),@P3 date',
'Jan','Nowak','1980-07-09'

Access wysyła zapytania z wykorzystaniem składni sp_executesql. Co natomiast gdy wielu użytkowników modyfikuje tą samą tabelę? Oczywiście transakcyjność jest obsługiwana po stronie serwera SQL – jeśli chodzi natomiast o podgląd to sprawdźmy to wstawiając do tabeli dane od strony Management Studio (wykorzystamy składnię wygenerowaną przez Access natomiast możemy to równie dobrze zrobić w jakikolwiek inny sposób):

exec sp_executesql N'INSERT INTO  "dbo"."Users"  
("name","surname","birthdate") VALUES (@P1,@P2,@P3)',
N'@P1 varchar(50),@P2 varchar(50),@P3 date',
'Piotr','Janik','1985-07-09'

W Access nasz podgląd nie jest na bieżąco odświeżany – ale po zamknięciu i otwarciu naszej tabeli wszystkie rekordy są na swoim miejscu ( po drodze usunąłem kilka rekordów):

Oczywiście same zapytania nie zawsze są optymalne – jak wszystko co jest wygenerowane automatycznie -jednakże jest to całkiem dobry interfejs dla niektórych zastosowań. Pamiętajmy, że na takiej tabeli mozemy stworzyć w Access odpowiedni formularz, kwerendę itp co daje nam całkiem spore możliwości. Ponadto warto pamiętać, że Access będzie walidował np. typy danych co jest dużą zaletą szczególnie, że nie wiąże się to z żadną dodatkową pracą deweloperską. Myślę, że ten krótki artykuł/tutorial w dobry sposób pokazuje, że nawet starsze narzędzia mogą znaleźć zastosowanie i nie zawsze należy wszystko wymyślać koło od nowa.