Zewnętrzne źródło danych w raportach MS Access
Podczas praktyk studenckich które odbyłem kilka tygodni temu zapoznałem się z możliwościami tworzenia raportów w Microsoft Access. Pracowałem nad rozwoje oprogramowania raportującego. Jednym z postawionych przede mną zadań było stworzenie mechanizmu umożliwiającego wywoływanie procedur składowanych z MS SQL Server i podpięcie otrzymanych wyników do utworzonych wcześniej raportów. Uzyskane wyniki miałem „zapakować” w Visual Basic-owy twór zwany RecordSet-em i podpiąć go bezpośrednio do raportu.
Uruchomienie procedury składowanej z poziomu MS Access utworzonej np. w MS SQL Server umożliwia tzw. kwerenda przekazująca. Cały ciężar związaną z wykonaniem zapytania czy też wspomnianej już procedury składowanej spada na „barki” silnika bazodanowego do którego zostanie wysłane polecenie. Mamy proste narzędzia do tworzenia raportów w MS Access – natomiast filtrowanie i przechowywanie ogromnych ilości danych przed udostępnienem ich w raporcie pozostawiamy większemu silnikowi bazodanowemu. – I to wszystko dzięki magicznej kwerendzie przekazującej. :)
Kod tworzący tymczasowo taką kwerende i jej wywołanie przedstawia się następująco:
Funkcja tworząca i wykonująca zapytanie SQL lub procedure składowaną.
'qSQL = Zapytanie do bazy danych
'return RecordSet
Public Function ExecutePassThruQuery(qSQL As String) As Recordset
Dim db As DAO.Database
Dim qryDef As DAO.QueryDef
Dim recSet As DAO.Recordset
Dim queryName As String
On Error GoTo ErrorHandling:
'Obiekt bazy danych
Set db = CurrentDb
'Nazwa tymczasowej kwerendy przekazujacej
queryName = "tempQuery"
'Usuniecie kwerendy jesli istnieje
RemovePassThruQueryIfExists (queryName)
'Utworzenie nowej kwerendy przekazujacej
Set qryDef = db.CreateQueryDef(queryName)
qryDef.ReturnsRecords = True
'polaczenie z baza danych
qryDef.Connect = connectionString
qryDef.SQL = qSQL
'Zwraca obiekt Recordset-u z danymi, w przeciwnym wypadku zwraca Nothing
Set ExecutePassThruQuery = qryDef.OpenRecordset(dbOpenSnapshot)
Set db = Nothing
Set qryDef = Nothing
Set recSet = Nothing
Exit Function
ErrorHandling:
MsgBox Err.Description
End Function
Funkcja wywołująca pracedure składowaną (wykorzystuje powyższą funkcję do utworzenia i wywołania bezpośrednio na bazie danych procedury) – wzasadzie tworzy specyficzny string w postaci np. „nazwa_procedury parametr1, parametr2, parametr3″.
'params = kolekcja parametrow procedury skladowanej jesli jakies przyjmuje
'procName = nazwa procedury skladowanej
'return DAO.RecordSet
Public Function CallStoredProcedure(procName As String, params As Collection) As Recordset
Dim recSet As DAO.Recordset
Dim queryName As String
Dim counter As Integer
On Error GoTo ErrorHandling:
queryName = "tempQuery"
'Dolaczam parametry do procedury
If Not params Is Nothing Then
If params.Count > 0 Then
procName = procName & " " & CStr(params(1))
For counter = 2 To params.Count
procName = procName & ", " & CStr(params.Item(counter))
Next counter
End If
End If
'Wywoluje Funkcje zwracajaca rezultat wykonanej procedury skladowanej
Set recSet = ExecutePassThruQuery(procName)
Set CallStoredProcedure = recSet
Set recSet = Nothing
Exit Function
ErrorHandling:
MsgBox Err.Description
End Function
Dodatkowo utworzyłem procedurę usuwającą tymczasową kwerende przekazującą.
'Usuwa tymczasowa kwerende
'queryName = nazwa kwerendy do usuniecia
Private Sub RemovePassThruQueryIfExists(queryName)
Dim qryDef As QueryDef
Dim db As Database
On Error GoTo ErrorHandling
Set db = CurrentDb
For Each qryDef In db.QueryDefs
If qryDef.Name = queryName Then
db.QueryDefs.Delete (qryDef.Name)
Exit For
End If
Next
Set qryDef = Nothing
Set db = Nothing
Exit Sub
ErrorHandling:
MsgBox Err.Description
End Sub
Załóżmy, że wywoływana procedura składowana w MS SQL Server ma nazwę „mojaProcedura” i pobiera dwa parametry typu VARCHAR. Wartości parametrów które chcemy przekazac do procedury są następujące: param1 = „ala”, param2 = „ola”. Kwerenda przekazująca w swoim wnętrzu miała by string w takiej oto postaci: „mojaProcedura ala, ola”.
Wywołanie:
Dim recSet As DAO.Recordset Dim col As New Collection col.Add "ala" col.Add "ola" Set recSet = CallStoredProcedure "mojaProcedura", col
W zasadzie to wszystko – tyle wystarczy, aby otrzymac obiekt RecordSet-u który następnie możemy bez żadnych przeszkód podpiąć do utworzonego wcześniej w MS Access raportu. :)