Adatbázis kapcsolatok kezelése (1. rész)

Aki nagyobb mennyiségű adattal dolgozik, valószínűleg használ valamilyen adatbázist adatainak megfelelő tárolására. Gyakran felmerülő igény, hogy az adatbázisban tárolt adatokkal (azok egy részével) Excelben (vagy más Office alkalmazásban) dolgozzunk tovább: készíthetünk belőle kimutatást, felhasználhatjuk további kalkulációkhoz, modellekhez.
A VBA nyelv az ADO (ActiveX Data Objects) API-n keresztül kiváló lehetőséget biztosít az adatbázisunkkal való kommunikációra. Aki használta már ezt az API-t, tudhatja, hogy odafigyelést igényel a kapcsolatok kezelése, nem elég létrehozni az adatbázis kapcsolatot és lefuttatni a programunkat, hanem a végén minden kapcsolatot zárni is kell: hiába futott le a programunk, a kapcsolatok az adatbázis szemszögéből nyitva maradnak, és bizonyos esetekben ez problémákat generálhat (például zárolhatjuk táblákhoz vagy akár az egész adatbázishoz való hozzáférést). A másik problémakör, amibe belefuthatunk, hogy hiába van a programunk hibátlanul megírva, futásidőben belefuthatunk számtalan hibába, amit az adatbázis kapcsolatunk generál és kezelnünk kellene (például zárolva van a tábla, amit szeretnénk lekérdezni, vagy valaki megváltoztatta a struktúráját, ezért a korábban rendben működő SQL lekérdezésünk most hibát eredményez). Mivel ezek minden adatbázis műveletnél kezelést igényelnek, érdemes lehet ehhez univerzálisan használható osztályokat készíteni, amiket minden projektünkbe beimportálhatunk. Ehhez szeretnék segítséget nyújtani a mostani és következő bejegyzésekben.

Előkészületek

Ahhoz, hogy az API-t használatba vehessük és a kódszerkesztő IntelliSense segítsége is rendelkezésre álljon, ne felejtsük beállítani a megfelelő referenciát az ADO könyvtárhoz (Tools / References… / Microsoft Data Objects x.x Library). Szükséges lehet egy, az adatbázisunknak megfelelő driver installálására is; a példánkban Access adatbázishoz fogunk kapcsolódni, ahhoz a szükséges driver a legtöbb Office disztribúciónak részét képzi.

A kapcsolat

Az ADO kapcsolatunk kezelésére létrehozunk egy külön osztályt. Ez lesz a felelős

• az adatbázis kapcsolat (Connection objektum) létrehozásáért,
• a kapcsolat megnyitásáért,
• a kapcsolat automatikus lezárásáért,
• a hibák riportálásáért.

A példában egy osztály egy adatbázishoz kapcsolódik (vagyis az osztályban definiáljuk az adatbázis elérhetőségét, pontosabban a Connection String-et), de kis átalakítással készíthető még univerzálisabb osztály, ami a Connection String-et paraméterként kapja.

Legyen az új osztályunk neve ‘AdoConnection’.

Mezők

Egy konstansunk és három változónk lesz:

• CONNECTION_STRING – a kapcsolat létrehozásához szükséges Connection String;
• Connection – ez az ADO ‘Connection’ típusú objektuma;
• Error – boolean típusú változó, jelzi, ha hiba történt végrehajtáskor;
• LastError – hiba esetén ez tartalmazza szövegként a hiba leírását.

A mezőket privátként definiáljuk a külvilággal való kapcsolatot a megfelelő jellemzők (Property-k) biztosítják. A kódrészlet:

Private Const CONNECTION_STRING As String = "MegfeleloConnectionString"
Private Type TAdoConnection
    Connection As ADODB.Connection
    Error      As Boolean
    LastError  As String
End Type
Private this As TAdoConnection

Public Property Get Connection () As ADODB.Connection
    Set Connection = this.Connection
End Property

Public Property Get Error() As Boolean
    Error = this.Error
End Property

Public Property Get LastError() As String
    LastError = this.LastError
End Property

Eljárások

Két eljárást hozunk létre:

• OpenAdoConnection – ezzel hozzuk létre a kapcsolatot;
• CloseAdoConnection – ezzel zárjuk a kapcsolatot.

A megnyitásra vonatkozó eljárás tartalmazni fog egy hibakezelő részt.
Ezeken kívül szükségünk lesz az osztály konstruktor (Class_Initiate) és destruktor (Class_Terminate) eljárásaira.

Lássuk először az adatbázis kapcsolat megnyitásáért felelős metódust, a hibakezelő eljárásával együtt:

Private Sub OpenAdoConnection()

    On Error GoTo ADOError
    this.Error = False

    'kapcsolat létrehozása
    set this.Connection = New ADODB.Connection 
    this.Connection.Open CONNECTION_STRING
    'kapcsolat ellenőrzése
    If Not this.Connection.State = adStateOpen Then
        this.Error = True
        this.LastError = "A kapcsolat létrehozása nem sikerült"
    Else
        Debug.Print "A kapcsolat létrejött" ' teszteléshez
    End if

CleanExit:
    Exit Sub
ADOError:
    this.Error = True
    Dim strErr As String

    ' VB által generált hibaüzenet
    strErr = Application.WorksheetFunction.Concat( _
                strErr, vbCrLf, _
                "VB Error # ", Str(Err.Number), vbCrLf, _
                "   Generated by ", Err.Source, vbCrLf, _
                "   Description  ", Err.Description _
    )
    
    ' ADO API által generált hibaüzenetek
    Dim AdoErrors As ADODB.Errors
    Set AdoErrors = this.Connection.Errors
    Dim AdoError  As ADODB.Error
    For Each AdoError In AdoErrors
        With AdoError
            strErr = application.WorksheetFunction.Concat( _
                strErr, vbCrLf, _
                "   ADO Error   #", .Number, vbCrLf, _
                "   Description  ", .Description, bCrLf, _
                "   Source       ", .Source _
            )
        End With
    Next AdoError
    this.LastError = strErr
    Err.Clear
    Resume CleanExit
End Sub

A fenti eljárást az osztály konstruktora hívja meg, vagyis amint létrehozunk egy AdoConnection objektumot, az adatbázis kapcsolat automatikusan létrejön (legalábbis megpróbáljuk létrehozni):

Private Sub Class_Initialize()
    OpenAdoConnection
End Sub

A kapcsolat lezárását kezelő eljárás így néz ki:

Private Sub CloseAdoConnection()
    If Not this.Connection Is Nothing Then
        If this.Connection.State = adStateOpen Then this.Connection.Close
        Debug.Print "A kapcsolatot lezártuk" ' teszteléshez
    Else
        Debug.Print "Nincs lezárandó kapcsolat" ' teszteléshez
    End If
    Set this.Connection = Nothing
End Sub

Ezt az eljárást az osztály destruktora hívja meg, vagyis, amikor az AdoConnection objektumra való összes hivatkozás hatályon kívül kerül (például ha mi magunk helyezzük hatályon kívül a Set [Objektum] = Nothing utasítással, de akkor is, ha az objektumot egy eljárásban csak lokálisan deklaráltuk és az eljárás végére értünk úgy, hogy nem keletkezett további referencia az objektumra.

Private Sub Class_Terminate()
    CloseAdoConnection
End Sub

Az osztály kész van, teszteljük le. Egy standard modulban hozzunk ehhez létre egy eljárást:

Public Sub KapcsolatTeszt
    Dim Conn As AdoConnection
    Set Conn = New AdoConnection
    If Conn.Error Then
        Debug.Print Conn.LastError
    End If
    Set Conn = Nothing 'ezt akár ki is törölhetjük
End Sub

Ha lefuttatjuk, az Azonnal ablakban (Immediate Window) láthatjuk, hogy a kapcsolat megnyílt, majd lezáródott – feltéve, hogy nem történt hiba, ez esetben pedig egy hibaüzenetet. Mindezt úgy, hogy a teszt eljárásban nem foglalkoztunk a kapcsolat megnyitásával, lezárásával, ezeket az osztály maga végezte el.