Der große ADO-Kurs - Seite 5
von Florian Reischl
Recordset
Das Recordset dient zur Anzeige und Navigation durch Datensätzen (Tabellen und Abfragen). Über Recordsets können Daten auch geändert, gelöscht oder angefügt werden. Für Frontend-Anwendungen sind Recordsets meist eines der wichtigsten Objekte. Die Eigenschaft CursorLocation ist wichtig, wird jedoch hier ausgelassen, da die Beschreibung bereits bei der Connection weiter oben zu finden ist.
4.1 ActiveConnection
Das Recordset kann nicht alleinstehend genutzt werden. Diese Eigenschaft bindet das Recordset an die Connection an. Es gibt ebenfalls die Eigenschaft ActiveCommand welche eine ähnliche Funktionalität über ein Command-Objekt bietet, jedoch mit beschnittener Funktionalität. Die Funktionalität des Recordsets beschränkt sich auf die maximal von der Connection erlaubten, es sind z.B. keine prinzipiell Änderungen möglich, wenn das die Connection nicht erlaubt.
4.2 Source
Source stellt ein SQL-Statement dar, welches angibt welche Daten abgefragt werden sollen. Beispiel:
Private Sub Form_Load() RsT.ActiveConnection = Cn RsT.Source = "SELECT * FROM tblName" End Sub
4.3 AbsolutePosition
Diese Eigenschaft gibt die aktuelle Position in einem Recordset an. Der Wert ist ein Long-Wert. Dieser Wert kann sowohl abgefragt als auch gesetzt werden um durch das Recordset zu navigieren. Bei einem serverseitigem Cursor kann diese Eigenschaft nicht verwendet werden.
4.4 MoveFirst, MovePrevious, MoveNext, MoveLast, Move
Es ist zwar, wie beschrieben, auch mit der Eigenschaft AbsolutePosition möglich durch Datensätze zu navigieren, aber die eigentlichen Methoden hierfür sind die MoveX-Methoden.
MoveFirst, MovePrevious, MoveNext und MoveLast sollten sich selber erklären.
Move ist dafür gedacht, in Größeren Schritten zu navigieren. Hier kann man einen positiven oder negativen Long-Wert angeben um sich von einer aktuellen Position an um x Stellen nach vorne oder nach hinten zu bewegen. Der zweite Parameter der Move-Methode gibt an ab welcher Position gestartet werden soll.
Konstante | Beschreibung |
---|---|
adBookmarkCurrent | Default. Startet an der aktuellen Stelle. |
adBookmarkFirst | Startet an der ersten Position im Recordset |
adBookmarkLast | Startet an der letzten Position im Recordset |
4.5 RecordCount
Der RecordCount gibt an wie viele Datensätze an den Client zurückgeliefert wurden. Diese Eigenschaft ist praktisch wenn man z.B. alle Datensätze mit einer Schleife durchlaufen möchten. Bei einem serverseitigen Cursor kann diese Eigenschaft nicht verwendet werden.
Kleines Beispiel um einfach mal die aktuellen Positionen auszugeben:
Private Sub Form_Load() Dim i As Long Dim RsT As New ADODB.Recordset With RsT .ActiveConnection = CnT .CursorLocation = adUseClient .Source = "SELECT * FROM tblName" .Open For i = 0 To .RecordCount - 1 Debug.Print .AbsolutePosition Next i End With End Sub
4.6 CursorType
Der CursorType gibt die Funktionalität und Dynamik des Datensatz-Cursors. Der Cursor kann als Datensatzzeiger beschrieben werden. Er hat nichts mit dem Mauszeiger zu tun! Er gibt jedoch an wie flexibel durch die Datensätze navigiert werden kann.
CursorTypeEnum
Konstante | Beschreibung |
---|---|
adOpenForwardOnly | Dieser Cursor ist der schnellste. Er verbraucht am wenigsten Speicher und Netzresourcen. Cursor kann jedoch nur vorwärts bewegt werden. Änderungen sind Zeilenweise möglich, jedoch nicht für andere User sichtbar. |
adOpenStatic | Ruft eine Kopie der abgefragten Daten ab. Daten können nicht geändert werden. Cursor in alle Richtungen beweglich. |
adOpenKeyset | Dieser Cursor erstellt für jeden Datensatz einen Schlüssel. Er ist schneller als der Static- und der Dynamic-Cursor, da er nicht die ganzen Datensätze lädt sondern nur einen Schlüssel auf den Datensatz. Änderungen und löschen sind möglich und werden anderen Usern angezeigt. Einzig anfügen wird anderen Usern nicht angezeigt. |
adOpenDynamic | Dieser Cursor ist der flexibelste, er ermöglicht alle Bewegungen. Alle Änderungen werden allen angezeigt. Er ist aber auch der ressourcenbeanspruchenste. |
Der adOpenForwardOnly-Cursor ist zwar mit Abstand der schnellste, er ist aber nicht in der Lage zu einem hinter der aktuellen Position liegenden Datensatz zu springen. Er ist somit nur ratsam wenn nur Daten angefügt werden sollen, aber keine geändert oder angezeigt werden sollen.
Der statische Cursor bringt eine sehr große Funktionalität, ist aber der langsamste und lässt keine Änderungen zu, weil die Daten in einer Art Screenshot an den Client geliefert werden. Somit sind alle Daten auf dem Client, haben aber eigentlich keine Verbindung mehr mit der DB.
Der dynamische Cursor ist der funktionellste Cursor. Es werden alle Navigationsmöglichkeiten gestellt, Änderungen sind möglich und bei einem aktualisieren des Recordsets werden auch die Änderungen anderer User angezeigt. Dieser Cursor ist für SQL-Server nicht möglich, wenn er hier gesetzt wird löst ADO leider keinen Fehler aus sondern wählt selbst einen anderen Cursor (meist den statischen). Aus diesem Grund ist eher der nächste Cursor ratsam.
Der Keyset-Cursor ist der zweitschnellste Cursor und der mit der zweitgrößten Funktionalität. Er lässt jegliche Änderungen zu und zeigt auch die anderer User an. Einzig neu angefügte Datensätze von anderen Usern werden durch ein normales aktualisieren des Recordsets nicht angezeigt. Auf Grund der ersten genannten Beschaffenheiten bietet er sich am besten an im Gebrauch mit professionellen Datenbankanwendungen.
4.7 LockType
Gibt an wie die Datensatzsicherheit und die Änderungsberechtigungen des Recordsets zu setzten sind. Die höchste Zugriffsberechtigung des Recordsets orientiert sich jedoch an den eingestellten Berechtigungen der zugrundeliegenden Connection.
LockTypeEnum
Konstante | Beschreibung |
---|---|
adLockReadOnly | Nur Lesen, Daten können nicht verändert werden (Default) |
adLockPessimistic | Daten werden beim Öffnen des Recordsets für andere User gesperrt. |
adLockOptimistic | Daten werden erst beim Update gesperrt. |
adLockBatchOptimistic | Dieser Modus wird nur für Stapelaktuallisierung benützt. |
ReadOnly sollte sich selber beschreiben.
Pessimistischer LockType bedeutet, daß die Daten vom ersten User der sie aufruft für weitere Änderungen gesperrt werden. Die Daten können jedoch auch von anderen Usern angezeigt, jedoch nicht mehr geändert werden. Er ist ratsam wenn die Datenbankstruktur das Ändern von einzelnen Fällen vorsieht. Für Auflistungen ist er jedoch nicht empfehlenswert, da alle angezeigten Daten für nachfolgende User nicht mehr Änderbar sind.
Optimistisch bedeutet, daß die Daten von allen Usern mit vollem Zugriff benutzt werden können. Wenn ein aktuell geänderter Datensatz jedoch bereits von einem anderem User seit dem anzeigen geändert wurde kann ein Fehler auftreten.
Der adLockBatchOptimistic-Cursor ist für den Umgang mit Stapelverarbeitungen zu setzen. Hier werden bei Offline-Recordsets die geänderten Datensätze markiert und können in Nachhinein auf einmal alle mit der Datenbank abgeglichen werden. Er kann eine Datenbank sehr entlasten. Eine genauere Beschreibung des Einsatzes wird noch später beschrieben.
4.8 Fields
Diese Collection ist für Datenbearbeitung wohl die wichtigste des Recordsets. Über die Fields können erst die einzelnen Spalten und damit auch die Werte des Recordsets angesprochen werden.
Es gibt vier Möglichkeiten die Spalten des Recordsets anzusprechen:
MsgBox Rs.Fields("SpaltenName") MsgBox Rs("SpaltenName") MsgBox Rs.Fields(0) MsgBox Rs!SpaltenName
Die letzte Schreibweise ist aber sehr unsauber und nicht ratsam! Da die Fields-Collection die Default-Eigenschaft des Recordsets ist kann man sich das Wort Fields sparen und die Schreibweise zwei nutzen. Bei der dritten Methode wird nicht der Name der Spalte angegeben sondern der Index in der Collection, sie bietet sich an um Beispielsweise über eine Schleife alle Spaltennamen zu erfahren.
Diese Collection bietet eine Unmenge an eigenen Methoden, Eigenschaften und auch Properties, wir möchten hier nur ein paar beschreiben:
4.8.1 Name
Gibt den Namen der Spalte an.
4.8.2 Count
Gibt die Anzahl der vorhandenen Spalten an. Hier ein kleines Beispiel um alle Spaltennamen des Recordsets in eine ListBox einzutragen:
Private Sub Form_Load() Dim i As Long With RsT .ActiveConnection = CnT .CursorLocation = adUseClient .Source = "SELECT * FROM TabellenName" .Open For i = 0 To .Fields.Count - 1 List1.AddItem .Fields(i).Name Next i End With End Sub
4.8.3 Type
Diese Eigenschaft gibt an welches Spaltenformat für die aktuelle Spalte vorliegt. Diese Eigenschaft liefert einen Long-Wert zurück welcher eine Konstante des DataTypeEnums (Achtung, dieses Enum wird auch von DAO genutzt, daher Deklaration von Variablen immer über ADODB.DataTypeEnum!) zurückliefert.
Konstante | Beschreibung | VB-Typ |
---|---|---|
adBoolean | Ja/Nein. Der Wert ist 0 für „nein“ und –1 für „ja“. | Boolean |
adChar | Text mit bis 255 Zeichen mit fester Länge (ASCII) | String * 255 |
adCurrency | Währung | Currency |
adDate | Datums und Zeitwert | Date |
adDouble | Fließkommazahl | Double |
adInteger | Große Nichtkommazahl | Long |
adLongVarBinary | Großes Binärfeld | Alle Binärformate |
adLongVarChar | Großer Text mit nichtfester Länge (ASCII) | String |
adLongVarWChar | Großer Text mit nichtfester Länge (UNICODE) | String |
adSingle | Kommazahl | Single |
adSmallInt | „Normale“ Nichtkommazahl | Integer |
adTinyInt | Kleine Nichtkommazahl | Byte |
adVarChar | Kleiner Text, bis 255 Zeichen mit nicht fester Länge (ASCII) | String |
adVarWChar | Kleiner Text, bis 255 Zeichen mit nicht fester Länge (UNICODE) | String |
adWChar | Text mit bis 255 Zeichen mit fester Länge (UNICODE) | String * 255 |
4.8.4 DefinedSize
Eigenschaft für Textfelder. Gibt die maximale Länge des Textes an. ActualSize gibt die aktuelle Länge an.
4.8.5 OriginalValue
Gibt nach dem Ändern und vor dem Speichern des Feldes den Originalwert des Feldes an welcher bislang in dem Feld steht.
4.8.6 Value
Gibt den Wert des Feldes an. Diese Eigenschaft ist die Default-Eigenschaft der Fields und kann somit bei der Schreibweise weggelassen werden:
MsgBox Rs.Fields("SpaltenName")
' Entspricht
MsgBox Rs("SpaltenName")
4.9 BOF, EOF
Diese Eigenschaften geben einen Boolean-Wert zurück, welcher auf True steht, wenn entweder BOF (BeginnOfFile) oder EOF (EndOfFile) erreicht wurde. Die Werte sind erst dann True wenn über die erste/letzte Position hinaus navigiert wurde.
4.10 Bookmark
Bookmark ist ein Lesezeichen welches vom aktuellen Datensatz abgefragt werden kann oder zur Navigation gesetzt werden kann. Das Format ist Variant. Der Sinn liegt darin, daß man auch nach einem Filtern von Daten (weiter unten) zu der vorherigen Position springen möchte, da die AbsolutePosition nach einem Filter nicht mehr stimmt.
4.11 Cancel
Wie bei der Connection beschrieben hat diese Methode mit asynchronen Arbeiten zutun, Beispiele später.
4.12 CancelUpdate
Diese Eigenschaft setzt alle Originalwerte der Felder zurück bevor diese gespeichert wurden. CancelBatch entspricht dieser Funktion für Stapelverarbeitungen, hiermit können alle Änderungen wieder zurück gesetzt werden.
4.13 Clone
Die Clone-Metode ist sehr praktisch um einen zweiten, eigenständigen, Recordset mit den gleichen Attributen wie die des zugrundezulegenden zu erstellen.
Mit:
Set RsNeu = Rs
Würde kein neuer Recordset erstellt werden sondern nur ein Zeiger auf den Basis-Recordset. Somit würden sich alle Navigationen und anderen Aktionen auf Rs beziehen.
Daher:
Set RsNeu = Rs.Clone
So kann RsNeu eigenständig als Recordset genutzt werden.
4.14 EditMode
Der EditMode gibt an in welchem Status sich ein Recordset derzeit befindet. Hierfür gibt es eigentlich nicht viel zu tun. Der Mode bleibt bestehen bis die Änderungen durch Update oder UpdateBatch vollendet werden. Es gibt 4 verschiedene Stati welche die durch das EditModeEnum definiert werden:
EditModeEnum
Konstante | Beschreibung |
---|---|
adEditNone | Es liegt derzeit kein besonderer Mode vor. |
adEditInProcess | Es werden gerade Änderungen an einem bestehenden Datensatz durchgeführt. |
adEditAdd | Es wird gerade ein neuer Datensatz angelegt. |
adEditDelete | Es wird gerade ein Datensatz gelöscht. |
4.15 Filter
Diese Eigenschaft lässt kleine SQL-ähnliche Filter auf ein offenes Recordset zu ohne das Recordset zu schließen und neu öffnen zu müssen. Hier ein paar Beispiele:
Rs.Filter = "ReportId < 10 AND Titel LIKE 'E%'" Rs.Filter = "Titel LIKE 'E%'" Rs.Filter = "Vorname = 'Stefan' AND Nachname LIKE 'B%'"
Gelöscht wird der Filter mit:
Rs.Filter = adFilterNone
4.16 Find
Die Find-Methode ist schneller als die Filter-Eigenschaft, und lässt auch Suchbegriffe wie "LIKE" zu. Es wird aber nur ein Datensatz, der erste passende, zurückgeliefert. Da es sich bei Find um eine Methode und nicht eine Eigenschaft handelt wird ohne „=“ gearbeitet:
Rs.Find "ReportId = 10"
'--- oder ---
Rs.Find "TEILENUMMER LIKE '%" & txtSuchen.Text & "%'", 1, adSearchForward
Zurückgesetzt wird die Find-Methode auch mit Rs.Filter = adFilterNone.
4.17 GetRows
Diese Methode kann genutzt werden um alle Werte des Recordsets in einen zweidimensionalen Varianten zu schreiben. Alle Änderungen können jedoch nicht mehr über eine ADO-Methode in das Recordset und somit nicht in die Datenbank zurückgegeben werden. Einsatz:
Private Sub Form_Load() Dim VRs As Variant With RsT .ActiveConnection = CnT .CursorLocation = adUseClient .Source = "SELECT * FROM Grunddaten" .Open End With VRs = RsT.GetRows MsgBox VRs(0, 1) End Sub
Bei dem Array gilt:
MsgBox VRs(SpaltenIndex, ZeilenIndex)
4.18 NextRecordset
Mit ADO ist es möglich in ein Recordset-Objekt mehrere Tabellen oder Abfragen gleichzeitig zu übergeben. Die einzelnen SQL-Selects müssen durch ein Semikolon „;“ getrennt werden. Bei Access ist dieses Verfahren nicht möglich. Hier ein Beispiel um die Daten aus zwei Tabellen gleichzeitig in ein Recordset zu holen und diese dann an ein anderes Recordset zu übergeben:
Private Sub Form_Load() Dim RsT As New ADODB.Recordset Dim Rs As ADODB.Recordset cnSet RsT.ActiveConnection = CnT RsT.CursorLocation = adUseClient RsT.Source = "SELECT * FROM tblTest1;SELECT * FROM tblTest2" RsT.Open Set Rs = RsT MsgBox Rs.RecordCount Set Rs = RsT.NextRecordset MsgBox Rs.RecordCount End Sub
4.19 AbsolutePage, PageCount, PageSize
Was haben Recordsets mit Pages (Seiten) zu tun? Man kann bei ADO, z.B. bei Ausdrucken, das Recordset in gewünschte Partitionen aufgliedern.
PageSize gibt an oder setzt die Größe der Seiten welche definiert werden sollen. Default ist 10.
PageCount gibt an wie viele Seiten auf Grundlage der PageSize und des RecordCounts vorhanden sind.
AbsolutePage gibt an oder setzt die aktuelle Seite im Recordset.
4.20 Requery
Fragt alle Daten des Recordsets komplett neu ab.
Rs.Requery ' Enspricht: Rs.Close Rs.Open
Wenn die Daten gerade geändert werden wird ein Fehler ausgelöst.
4.21 Resync
Diese Methode ist ähnlich der Requery-Methode. Auch hier werden die Daten mit denen der Datenbank neu abgeglichen. Sie ist vor allem im Gebrauch mit statischen Cursorn ist diese Methode recht hilfreich. Resync führt nicht Close-Open aus, damit ist es schneller als die Requery-Methode, dadurch werden aber keine neu angefügten Daten von anderen Usern angezeigt.
4.22 Save
Bei ADO ist es möglich mit sogenannten Offline-Recordsets zu arbeiten. Mit Save können Daten eines Recordsets auf dem Client physisch gespeichert. Der optionale Parameter PresistFormat gibt an in welchem Format die Daten gespeichert werden sollen. Wenn für das Recordset derzeit ein Filter besteht werden nur die aktuell gezeigten Daten gespeichert.
PersistFormatEnum
Konstante | Beschreibung |
---|---|
adPersistADTG | Default. Speichert die Daten im ADTG-Format (Advanced Data Tablegram). Dies ist ein optimiertes Format |
adPersistXML | Speichert die Daten im XML-Format |
4.23 Sort
Diese Eigenschaft gibt an oder setzt ob und wie ein Recordset sortiert werden soll. Die Übergabe erlaubt mehrere Sortierungen gleichzeitig. Sortiert wer den kann entweder aufsteigend (ASC, Default) oder absteigend (DESC).
Wenn man z.B. im Recordset nach der Spalte Nachnamen sortieren will, jedoch bei gleichen Nachnamen den Datensatz des ältesten oben haben möchte, dann könnte die Sort z.B. so genutzt werden:
RsT.Sort = "Nachname ASC, Geburtsdatum DESC"
4.24 Update
Mit Update können die geänderten Daten in die Datenbank gespeichert werden. Wenn Daten geändert werden und dann navigiert wird (z.B. MoveNext) wird automatisch Update ausgeführt. UpdateBatch ist ähnlich aber für Stapelverarbeitungen gedacht.
4.25 AddNew
Über AddNew wird ein neuer Datensatz initialisiert. Übergeben werden die Daten mit Update:
Option Explicit Dim RsT As New ADODB.Recordset Private Sub Form_Load() With RsT .ActiveConnection = CnT .CursorLocation = adUseClient .CursorType = adOpenKeyset .LockType = adLockOptimistic .Source = "SELECT * FROM tblTest" .Open .AddNew .Fields("Spalte1") = "Hallo" .Fields("Spalte2") = 123 .Update End With End Sub
4.26 Offline-Recordsets
Offline-Recordsets sind eine neue Möglichkeit der Datenverarbeitung welche von ADO zur Verfügung gestellt wird.
4.26.1 Recordsets aus dem nichts
Es ist möglich ein Recordset komplett aus dem nichts zu erstellen und dieses dem Client z.B. als neue und extrem leistungsfähige Version von Collections und Arrays zu nutzen. (Dies darf nicht mit dem erstellen einer Tabelle auf einer Datenbank verwechselt werden, diese Recordsets sind rein auf dem Client vorhanden.)
Die Daten können nicht nur mit fast allen Methoden des Recordset-Objekts auf dem Client genutzt werden sondern auch physisch auf dem Client gespeichert und später wieder geöffnet werden.
Hier ein kleines Beispiel wie man eine Tabelle für eine kleine Personenverwaltung erstellt:
Option Explicit Dim RsT As New ADODB.Recordset Private Sub Form_Load() With RsT .Fields.Append "Nachname", adVarChar, 255 .Fields.Append "Vorname", adVarChar, 255 .Fields.Append "Alter", adSmallInt .Open .AddNew .Fields("Nachname") = "Mustermann" .Fields("Vorname") = "Manfred" .Fields("Alter") = 25 .Update End With End Sub
Diese Daten dann auf dem Client in eine Datei speichern:
RsT.Save "C:\Temp\Personen.dat"
Zu einem späterem Zeitpunkt können die Daten wieder über Open abgerufen werden (hierbei muß ActiveConnection = Nothing gesetzt sein und das Recordset natürlich geschlossen sein!):
Option Explicit Dim RsT As New ADODB.Recordset Private Sub Form_Load() If RsT.State = adStateOpen Then RsT.Close If Not RsT.ActiveConnection Is Nothing Then Set RsT.ActiveConnection = Nothing End If RsT.Open "E:\Temp\Personen.dat" MsgBox RsT("Nachname") End Sub
4.26.2 Stapelverarbeitung
Serverressourcen sind gerade bei sehr großen Anwendungen knapp. Es gibt bei ADO die sehr effiziente Möglichkeit der Stapelverarbeitung, das heißt die Connection wird hierfür vom Recordset getrennt und evtl. auch geschlossen. Es ist nun möglich nach belieben Änderungen an den Daten vorzunehmen und erst später auf einmal alle Daten mit der Datenbank abzugleichen. Hierfür werden die Batch-Attribute von ADO genutzt.
Beim Verbinden wird hier der LockType auf adLockBatchOptimistic gesetzt um dem Recordset mitzuteilen, daß die Daten beim Ändern durch ein Flag als „dirty“ markiert werden sollen, über dieses Flag weiß das Recordset später welche Daten geändert wurden und welche mit der Datenbank abzugleichen sind.
Öffnen eines Recordsets für Stapelverarbeitung und nachfolgendes schließen der gesamten Verbindungen zwischen Recordset, Connection und Server. Nach dieser Prozedur ist das Recordset komplett für den Client verfügbar die Verbindung zur Datenbank ist jedoch komplett geschlossen und somit ein User weniger der von der Datenbank aktuell verwaltet werden muß:
Option Explicit Dim Rs As New ADODB.Recordset Dim Cn As New ADODB.Connection Private Sub Form_Load() With Cn .Mode = adModeShareDenyNone .Provider = "Microsoft.Jet.OLEDB.4.0" .Properties("Data Source") = "F:\Test_DBs\Access\db3.mdb" .Open End With With Rs .ActiveConnection = Cn .CursorLocation = adUseClient .LockType = adLockBatchOptimistic .Source = "SELECT * FROM tblName" .Open ' Connection entfernen Set .ActiveConnection = Nothing End With ' Connection zum Server schließen Cn.Close End Sub
Nachdem die Daten nun beliebig geändert, gelöscht und neu angelegt worden sind können alle Daten auf einmal mit der Datenbank abgeglichen werden:
' Die Verbindung zum Server wird neu hergestellt ' (Achtung bei UserId/-passwort muß je nach der Einstellung ' von "Presists Security Info" neu gesetzt werden) Cn.Open ' Recordset wird wieder verbunden Set Rs.ActiveConnection = Cn ' Die Änderungen werden auf einmal an die DB übergeben Rs.UpdateBatch
Um alle Änderungen rückgängig zu machen kann man einfach ein Stapel-Cancel ausführen:
Rs.CancelBatch