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. :)

  1. czemu DAO a nie ADO?

    tam jest o wiele prościej wykonać kwerendę parametryczną

  2. Wykorzystałem tutaj DAO ponieważ wyniki zwrócone z bazy miały zostac podpięte pod raporty, a RecordSet-y z ADO nie są akceptowane przez raporty. W tym przypadku otrzymany RecordSet mogłem bezpośrednio wpiąc w utworzony raport.

  1. Na razie brak trackbacków