ADO, DB-Programmierung

Beschreibung  

ADO ist der Nachfolger von DAO. Über ADO lassen sich fast alle geläufigen Datenbanken zugreifen, egal ob Access, SQL-Server, Oracle (hier ist der Oracle wesendlich stärker als der von Microsoft), Informix oder sonst ein Datenbanksystem. Auch ODBC-Datenquellen, die durch ADO auch hinfällig werden, lassen sich problemlos ansteuern.

Um ADO nutzen zu können muß im Menü unter Projekt-Verweise ein Verweis auf die "Microsoft ActiveX Data Objects 2.x Library" gesetzt werden. Ein ADO-Datenmodell besteht im allgemeinen aus drei Komponenten.

Erstens, die Verbindung (Connection), sie definiert welche Datenbank an welchem Ort angesprochen wird. Hier werden grundliegende Parameter für den späteren Einsatz festgelegt wie CursorLokation, Mode, ... festgelegt, auf diese Punkte wird weiter unten eingegangen. Die Connection kann von mehreren untergeordneten Objekten gleichzeitig angesprochen werden und muß somit im allgemeinen pro Anwendung nur einmal geladen werden.

Zweitens dem Datensatz (Recordset), dieses Objekt gibt Daten in Form von Tabellen oder Auswahlabfragen/Ansichten zurück. Auch beim Recordset kann man bestimmte Beschränkungen oder Zugriffsparameter festlegen, maximaler Zugriff ist jedoch der von der Connection vorgegebene, da der Recordset hier eine untergeordnete Rolle übernimmt.

Das dritte Objekt ist das Befehls-Objekt (Command), hiermit kann man Aktions-Abfragen, ähnlich "Stored Prozedures" auf dem SQL-Server, definieren und ausführen. Anders als Recordsets muß ein Command nicht auf eine Connection zugreifen, sondern kann eine eigene Verbindung zur Datenbank (DB) erstellen. Natürlich ist es auch möglich mit einem Command eine bestehende Connection anzusteuern, diese muß dann aber die ausreichenden Rechte für die Aktion zur Verfügung stellen.

ADODB: Verbindung [Connection]  

Wie bereits erwähnt ist die Connection der grundliegende Baustein zwischen der Datenbank und dem Frontend (der Anwendung). Eine Connection benötigt grundliegend zwei Parameter, den Provider und den Connectionstring.

Der Provider gibt der Connection an was für eine Art von Datenquelle angesteuert werden soll und welche Version der Datenquelle vorliegt. Der Provider wird über einen String definiert.

Beispiel für SQL-Server:

Option Explicit
Dim Cn As New ADODB.Connection

Private Sub Form_Load()
    Cn.Provider = "SQLOLEDB.1"
End Sub

Der Connectionstring beinhaltet alle Informationen die an die Datenbank zum Verbindungsaufbau weitergeleitet werden müssen. Man kann z. B. auch den Provider und vieles andere in den Connectionstring packen. Nachteil ist die zunehmende Unübersichtlichkeit, je mehr man hineinpackt. Daten die auf jeden Fall in den Connectionstring müssen sind Pfad, UserId und Passwort für die Verbindung, wobei UserId und Passwort optional sind.

Beispiel für Access2000 ohne Passwort:

Option Explicit
Dim Cn As New ADODB.Connection

Private Sub Form_Load()
    Cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    Cn.ConnectionString = "Data Source=C:\Temp\Test.mdb"
End Sub

Beispiel für SQL-Server mit Passwort und UserId

Dim Cn As New ADODB.Connection

Private Sub Form_Load()
    Cn.Provider = "SQLOLEDB.1"
    Cn.ConnectionString = "Password=MyPwd;" & _
    "Persist Security Info=True;" & _
    "User ID=MyUserId;" & _
    "Initial Catalog=MyDB;" & _
    "Data Source=MyServer"
End Sub

Optionale Parameter für die Connection sind z. B. die Position des Cursors oder der Zugriffsmodus .

Über den Cursor lege ich fest, ob in der Anwendung oder direkt auf dem Server gearbeitet wird. Im Normalfall wird der Cursor jedoch fast immer auf dem Client positioniert. Eine Positionierung auf dem Server ist nur sinnvoll, wenn für besonders große (!) Datenoperationen (z. B. Update für mehrere tausend Datensätze), dann werden keine Daten über das Netzwerk geschickt sondern direkt auf dem Server der, wesendlich schneller ist, bearbeitet. Konstanten sind hierfür sind:

Über den Modus wird bestimmt, welche Zugriffsrechte für die Anwendung vergeben werden, bzw. wie sich die Berechtigungen im Multiuser-Modus verhalten.

Beispiel für eine komplette Connection

Option Explicit
Dim Cn As New ADODB.Connection

Private Sub Form_Load()
    With Cn
    'Meistens Cursor auf Client außer für SEHR große Arbeiten
        .CursorLocation = adUseClient

    ' Sperrungen (hier keine bei Multiuser)
        .Mode = adModeShareDenyNone

    ' DB-Provider (hier Access 2000)
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=C:\Temp\Test.mdb"
        .Open
    End With
End Sub

ADODB: Recordset  

So wie die Connection die Basis für die Kommunikation zwischen DB und Client ist ist der Recordset das wohl wichtigste Objekt für die Anwendung. Mit ihm werden Daten aus der Datenbank ausgelesen und können dann vom Anwender bearbeitet werden. Wichtige Parameter für Recordsets sind ActiveConnection, Source, LockType, CursorType.

ActiveConnection gibt an auf welche (aktive) Connection sich der Recordset beziehen soll.

Source gibt an welche Daten angezeigt werden sollen. Source kann sich auf eine Tabelle, eine Abfrage oder eine View beziehen oder man gibt ein eigenes SQL-Statement an.

LockType gibt die Sicherheitsstufe des Recordsets an:

CursorType gibt an wie der Cursor sich verhält:

Beispiel für Abfrage einer Datenbank: Tabellen & Abfragen

Private Sub Form_Load()
Dim Rs As New ADODB.Recordset

    With Rs
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open "tblTest", Cn
    End With
End Sub

Beispiel für Abfrage einer Datenbank: Bei SQL-Statements

Private Sub Form_Load()
Dim Rs As New ADODB.Recordset

With Rs
    .CursorType = adOpenKeyset
    .LockType = adLockPessimistic
    .Open "SELECT Nachname, Vorname FROM tblTest " & _
    "WHERE Alte > 12", Cn
End With
End Sub

Dynamische Recordsets erstellen.
Es ist auch möglich eigene Recordsets zu erstellen, welche entweder nur temporär genutzt werden oder später an eine Datenquelle übergeben werden:

Private Sub Form_Load()
Dim Rs As New ADODB.Recordset

With Rs
    .Fields.Append "Spalte1", adInteger
    .Fields.Append "Spalte2", adChar, 255
    .Open
End With
End Sub

Offline und Lokale Recordsets
Recordsets können auch offline bearbeitet werden bzw. lokal zwischengespeichert werden.

Über die Methode UpdateBatch können auf einmal alle durchgeführten Änderungen an die Datenbank übergeben werden. Diese Methode kann, richtig eingesetzt, den Datenverkehr im Netzwerk erheblich verringern.

Beispiel für Offline Recordsets

Option Explicit

Dim Rs As New ADODB.Recordset

Private Sub Form_Load()
  cnSet

  With Rs
    .ActiveConnection = cnT
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockBatchOptimistic
    .Open "tblTest"
    .MoveFirst
  End With
End Sub

Private Sub cmdAendern_Click()
  Dim i As Integer

    'Datensätze ändern
    For i = 0 To Rs.RecordCount - 1
      Rs!Index = i
      Rs.MoveNext
    Next i
End Sub

Private Sub cmdUpdateAlle_Click()
  'Alle Änderungen an DB übergeben
  Rs.UpdateBatch adAffectAllChapters
End Sub

Es ist ebenfalls möglich Daten komplett von der Datenquelle zu entfernen und lokal zu speichern.

Beispiel für locale Datensätze

Option Explicit

Dim Rs As New ADODB.Recordset

Private Sub Form_Load()
  With Rs
    .ActiveConnection = Cn
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockBatchOptimistic
    .Open "tblTest"
    .MoveFirst
  End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
  'File löschen
  Kill "C:\temp\rstest.dat"
End Sub

Private Sub cmdSpeicherLokal_Click()
  'File lokal speichern
  Rs.Save "C:\temp\RsTest.dat"

  'Recordset schließen und Connection auf Null setzen
  Rs.Close
  Rs.ActiveConnection = Nothing
End Sub

Private Sub cmdOeffneLokal_Click()
  'Lokales File öffnen
  Rs.Open "C:\temp\RsTest.dat"
End Sub

Mit Recordsets arbeiten
Über die Methode Sort kann man die Datensätze in einem Recordset nach einem bestimmten Feld entweder aufsteigend (ASC) oder absteigend (DESC) sortieren:

'Aufsteigend nach [Nachname]
Rs.Sort = "Nachname ASC"

Mit den Methoden Find oder Filter kann ich bestimmte Daten in meinem Recordset suchen ohne den DataSource neu zu definieren. Beide Methoden lassen aber nur ein Suchkriterium zu. (ACHTUNG: Find liefert nur den ersten gefundenen Datensatz mit dem angegebenen Kriterium!)

Um einen bestimmten Datensatz zu suchen:

Rs.Find "Nachname = 'Müller'"

Um mehrere Datensätze zu erhalten über die Filter-Methode:

Rs.Filter = "Alter > 20"

Um Objekte mit Recordsets zu füllen muß man erst den DataSource bestimmen und dann das DataField bestimmen:

Set Me.Text1.DataSource = Rs
Me.Text1.DataField = "Nachname"

Sonstiges
Wenn man nur einmal Daten aus einer Datenbank ziehen und an ein Objekt übergeben möchte kann man das auch ohne Recordset-Objekt und kann stattdessen über die Connection ein Execute schicken.
z. B. um ein DataGrid einmalig mit Daten zu füllen:

Set Me.DataGrid1.DataSource = Cn.Execute("tblTest")

ADODB: Command  

Das Command-Objekt ist das dritte wichtige ADO-Objekt. Mit diesem kann man alle Arten "Aktions-SQL-Statements" ausführen [z.B. UPDATE, CREATE TABLE, INSTERT INTO, ...]. Im Gegensatz zu dem Recordset-Objekt kann man das Command optional entweder an eine Connection anbinden oder die Verbindung selbst erstellen. Auch Abfragen mit Parameterübergaben können über ein Command realisiert werden, aber dies wird im unteren Bereich ADOX bei Stored Procedures behandelt.

Erstellen mit bestehender Connection

Private Sub Command1_Click()
  Dim Cmd As New ADODB.Command

    Cmd.ActiveConnection = cnT
    Cmd.CommandText = "UPDATE tblTest SET " & _
    "Alter = Alter + 1"
End Sub

Erstellen ohne Connection

Private Sub Command1_Click()
  Dim Cmd As New ADODB.Command

    With Cmd
      .ActiveConnection = "Provider=SQLOLEDB.1" & _
    ";Password=myPwd;" & _
    "Persist Security Info=" & _
    "True;" & _
    "User ID=MyUserId;" & _
    "Initial Catalog=MyDB;" & _
    "Data Source=MyServer"
    .CommandText = "UPDATE tblTest SET " & _
    "Alte = Alter + 1"
    End With
End Sub

Auch das Command-Objekt kann man, bei nur einmaligen Gebrauch direkt über die Connection mit Execute ausführen:

Cn.Execute "UPDATE tblTest SET Alte = Alter + 1"

ADOX: Beschreibung  

ADOX ist eine Zusatzkomponente zu ADO. Sie ist für das programmieren mit ADO nicht zwingend notwendig, bietet jedoch gewaltige Vorteile und Zusatzfunktionen. Für ADOX muß ein zusätzlicher Verweis auf die "ADO Ext. 2.5 for DLL and Security" gesetzt werden. ADOX ist eine Erweiterung an Objekten, Methoden, ... von ADO.

Mit ADOX lassen sich beispielsweise neue Tabellen, Stored Procedures über Objekte und Eigenschaften an Hand von Objekten erstellen. Die DB wird hier als Catalog bezeichnet der alle enthaltenen Objekte in einer Collection untergestellt sind. ADOX ist vor allem für die die von DAO auf ADO umsteigen sehr nützlich, da das Standard-ADO viele Vorzüge von DAO nicht bietet.

ADOX: Catalog  

Der Catalog ist das Hauptobjekt in Ihm sind alle Procedures, Views, Groups, ... enthalten. Der Catalog stellt eine DB als Objekt im ganzen dar. Ein Catalog kann an eine bestehende Connection angebunden werden oder wie ein Command über die Eigenschaft ActiveConnection eine eigene Verbindung zur DB erstellen. Ihr sind alle anderen ADOX-Objekte wie Tables, Users, ... unterstellt.

Beispiel eine Access2000 DB mit Passwort erstellen Beim erstellen der DB muß festgelegt welcher ADO-Treiber (siehe Connection) verwendet werden soll bzw. ob ein Passwort erstellt werden soll. Alle Parameter werden hierbei in einem String übergeben.

Private Sub fCreateDB()
  Dim strCat As String
  Dim Cat As New ADOX.Catalog

    'DB-Provider festlegen
    strCat = "Provider=Microsoft.Jet.OLEDB.4.0;"

    'DB-Pfad übergeben
    strCat = strCat & "Data Source=" & App.Path & _
           "\TestDB.mdb;"

    'DB-Passwort anhängen
    strCat = strCat & "Jet OLEDB:Database Password=testPwd;"

    'DB erzeugen
    Cat.Create strCat
End Sub

Über den Catalog alle Tabellen (ohne Systemtabellen) einer DB anzeigen lassen.

Private Sub Command1_Click()
  Dim strTables As String
  Dim Cat As ADOX.Catalog
  Dim Tbl As ADOX.Table

    Set Cat = New ADOX.Catalog
    Cat.ActiveConnection = Cn

    For Each Tbl In Cat.Tables
      'Keine Systemtabellen anzeigen lassen
      If Tbl.Type <> "SYSTEM TABLE" Then
        strTables = strTables & vbCrLf & Tbl.Name
      End If
    Next Tbl

    MsgBox strTables
End Sub

ADOX: Table  

Das Table-Objekt stellt eine Tabelle einer angebundenen Datenbank dar. Mit diesem Objekt lassen sich die einzelnen Columns (Spalten), Indizes, Keys, Properties der Tabelle ansprechen.

Beispiel zum erstellen einer Tabelle über ADOX. (Die in diesem Beispiel erstellte Tabelle kann jedoch erst angesprochen werden wenn Spalten angefügt wurden, siehe unten)

Private Sub Command1_Click()
  Dim ADOtbl As New ADOX.Table
  Dim strCnn As String

    'Neue Tabelle benennen
    ADOtbl.Name = "tblADOx"

    'Neue Tabelle in DB einfügen
    Cat.Tables.Append ADOtbl
End Sub

Beispiel für die Auflistung aller Properties einer Tabelle:

Private Sub Command1_Click()
  Dim Tbl As ADOX.Table
  Dim strProp As String
  Dim Prop As ADOX.Property

    Set Cat = New ADOX.Catalog
    Set Tbl = Cat.Tables("tblADOX")

    For Each Prop In Tbl.Properties
      strProp = strProp & vbCrLf & Prop.Name
    Next Prop

    MsgBox strProp
End Sub

ADOX: Column  

Das Column-Objekt ist ein Unterobjekt des Table-Objekts. Hier können alle Eigenschaften der Spalte eingestellt und abgefragt werden. Auch neue Spalten lassen sich über die Column erstellen.

Beispiel zum erstellen einer neuen Spalte:

Private Sub Command1_Click()
  Dim ADOCol As New ADOX.Column

    With ADOCol
      'ParentCatolog ist zum Setzen der
      '.Properties notwendig
      Set .ParentCatalog = Cat

      'Name der Spalte übergeben
      .Name = "NeueSpalte"

      'Typ der Spalte übergeben (hier ein Textfeld)
      .Type = adVarWChar

      'Feldgrößendefinitionen sind nur bei
      'Textformat zulässig
      .DefinedSize = 255

      'Standardwert
      If Not IsMissing(Default) Then
        .Properties("Default") = "Text eingeben"
      End If

      'Eine leere Zeichenfolge ist erlaubt
      .Properties("Jet OLEDB:Allow Zero Length") = True

      'Aufsteigende Sortierung
      .SortOrder = adSortAscending
    End With

    'Neue Spalte an Tabelle übergeben
    Cat.Tables("MeineTabelle").Columns.Append ADOCol
End Sub

ADOX: View  

Views (oder Abfragen) sind SELECT-SQL-Statements, sie werden definiert um entweder bestimmte Teile einer Tabelle zu schützen (z. B. in der Tabelle Personal die Spalte Gehalt) oder bestimmte nicht benötigte Teile einer Tabelle im Voraus herauszufiltern (z. B. wenn nicht alle Spalten benötigt werden). Das View-Objekt ermöglicht die Abfrage aller Eigenschaften einer Abfrage. ACHTUNG: Microsoft Bug, über ADOX angelegte Views werden in Access2000 nicht angezeigt.

Beispiel zum erstellen einer View:

Private Sub Command1_Click()
  Dim Cat As New ADOX.Catalog
  Dim Cmd As New ADODB.Command
  Dim V As ADOX.View

    Set Cmd.ActiveConnection = cnADO
    Set Cat.ActiveConnection = cnADO

    Cmd.CommandText = "SELECT ReportId FROM Grunddaten"
    Cat.Views.Append "vTest", Cmd
End Sub

ADOX: Procedures  

Procedures [oder auch "Stored Procedures"] sind Aktions-SQL-Statements die auf dem Server abgelegt werden können. Da sie nicht auf Client sondern auf dem Server ausgeführt werden und nur die fertig aufbereiteten Daten übermittelt sind haben Sie zwei große Vorteile:

• Der DB-Server ist im Normalfall wesendlich performanter als der Client.
• Es werden nur noch so viele Daten wie nötig über das Netz geschickt und der Netzwerkverkehr entscheidend verringert.

Nachteil von Stored Procedures ist, daß bei einem DB-Schwenk oder einer Übergabe die Zusammenhänge sehr schwer erkennbar sind.

Wer auf dem SQL-Server arbeitet kennt sich mit Stored Procedures mit Sicherheit aus, was aber vielen neu sein wird ist, daß es auch bei Access möglich ist Stored Procedures zu platzieren. Da dieses Feature jedoch nicht von Microsoft vorgesehen war gibt es kleine Unterscheidungen.

Beispiel zum erstellen einer Procedure auf einer Access-DB.

ACHTUNG: Microsoft Bug, über ADOX angelegte Stored Procedures werden in Access2000 nicht angezeigt!

Private Sub Command1_Click()
  Dim Cat As New ADOX.Catalog
  Dim Cmd As New ADODB.Command

    Set Cmd.ActiveConnection = Cn
    Set Cat.ActiveConnection = Cn

    Cmd.CommandText = "PARAMETERS " & _
    "@RepVal INTEGER, " & _
    "@IdxVal INTEGER; " & _
    "SELECT * " & _
    "FROM tblTest " & _
    "WHERE ReportId > " & _
    "@RepVal AND [FldIndex]" & _
    " = @IdxVal"
    Cat.Procedures.Append "spTest", Cmd
End Sub

Beispiel zum Aufrufen einer Stored Procedure von einem SQL-Server:

Private Sub Command1_Click()
  Dim Cmd As New ADODB.Command
  Dim rsT As New ADODB.Recordset

    Set Cmd.ActiveConnection = Cn

    Cmd.CommandType = adCmdStoredProc
    Cmd.CommandText = "spTest"

    Cmd.Parameters("@RepVal") = 120
    Cmd.Parameters("@IdxVal") = 1
    Set rsT = Cmd.Execute
    rsT.MoveFirst
    Debug.Print rsT!Titel
End Sub

Beispiel zum Aufrufen einer Stored Procedure von einer Access-DB. Hier wird der Name der Parameter nicht erkannt und kann nicht wie sonst angefügt werden sondern wird über einen Variant-Array übergeben:

Private Sub Command1_Click()
  Dim Cmd As New ADODB.Command
  Dim rsT As ADODB.Recordset
  Dim v(1) As Variant

    Set Cmd.ActiveConnection = cnADO

    v(0) = 80
    v(1) = 1

    Cmd.CommandType = adCmdStoredProc
    Cmd.CommandText = "spTest"

    Set rsT = New ADODB.Recordset
    ' Die Parameter werden übergeben
    Set rsT = Cmd.Execute(, v())
    rsT.MoveFirst

    Debug.Print rsT!Titel
End Sub

Beispielprojekt als Download [7460 Bytes]

Ihre Meinung  

Falls Sie Fragen zu diesem Tutorial haben oder Ihre Erfahrung mit anderen Nutzern austauschen möchten, dann teilen Sie uns diese bitte in einem der unten vorhandenen Themen oder über einen neuen Beitrag mit. Hierzu können sie einfach einen Beitrag in einem zum Thema passenden Forum anlegen, welcher automatisch mit dieser Seite verknüpft wird.