1 ADO - Access Entwicklerbuch

Zusatzkapitel zum Buch
Access 2010 - Das Grundlagenbuch für Entwickler
www.access-entwicklerbuch.de/2010
1
ADO
Im vorherigen Kapitel haben Sie bereits erfahren, dass DAO die bevorzugte DatenzugriffsBibliothek ist, wenn es um die Entwicklung reiner Access-An­wen­dun­gen geht. ADO
(ActiveX Data Objects) war von Microsoft als Nach­folger von DAO geplant.
Es eröffnet wesentlich mehr Mög­lich­keiten, die sich aber vor allem dann bemerkbar
machen, wenn Sie ein alternatives Backend wie beispielsweise Microsoft SQL Server
oder MySQL verwenden.
Der Fokus dieses Buchs richtet sich auf die Entwicklung reiner Access-Daten­bank­an­
wen­dungen. Daher sollen die Mög­lichkeiten von Access in Zusammenarbeit mit dem
SQL Server hier nicht betrachtet werden.
Zusatzkapitel ADO
Ent­wick­ler, die jetzt noch reine Access-Anwendungen ­pro­grammieren, aber diese im
Hinblick auf einen spä­teren Wechsel auf den SQL Server direkt auf dieses Backend
­vorbereiten möchten, sollen natürlich nicht außen vor bleiben – zumal ADO auch für
reine Access-An­wendungen einige Features bereithält, die DAO nicht bietet.
BEISPIELDATENBANK
Die Beispieldatei zu diesem Kapitel finden Sie unter dem Namen ADO.accdb auf
www.acciu.de/aeb2010.
Wegen des gegenüber DAO wesentlich größeren Funk­tions­umfangs könnte über ADO
ein eigenes Buch geschrie­
ben werden. Aus Platzgründen wird das Thema hier je­
doch ein wenig eingeschränkt – und zwar so, dass Sie die Techniken, die Sie im vorherigen Kapitel über DAO ken­nen gelernt haben, auch mit ADO einsetzen können. Na­
türlich soll auch die eine oder andere Spezialität von ADO nicht unerwähnt bleiben.
Viele bereits in »DAO« ab Seite 493 enthaltene Informationen gelten auch für den
Um­gang mit ADO. Dies bezieht sich vor allem auf formale Techniken wie den Umgang
mit Auflistungen, die Verwendung von Punkt oder Ausrufezeichen für den Bezug auf
Elemente und Eigenschaften oder das De­klarieren und Instanzieren von Objekten. Wenn
Sie Informationen zu diesen Themen be­nötigen, schlagen Sie am besten im oben ge­
nann­ten Kapitel nach.
ADO-Neuigkeiten
Mit Access 2010 kommt nach Access 2007 wiederum keine neue ADO-Version. Der
mit Access 2007 eingeführte JET-OLE­DB-Provider namens Microsoft Office 12.0 Access
Database Engine OLE DB Provider (Micro­soft.ACE.OLEDB.12.0) findet weiterhin Verwendung.
Im Gegensatz zu DAO mit den Objekten Recordset2 und Field2 gibt es unter ADO keine
Möglichkeit, die in Attachment- oder mehrwertigen Feldern gespeicherten Daten über
ein zusätzliches Recordset auszulesen. Daher kann man nur über die »Unterfelder« auf
die in den verknüpften, verborgenen Tabellen enthaltenen Daten zugreifen. Wie dies
funktioniert, erfahren Sie unter »Daten eines Recordsets mit mehrwertigen Feldern
ausgeben« auf Seite 992 und »Daten eines Recordsets mit Attachment-Feldern ausgeben« auf Seite 993.
1.1 Zugriff auf eine Datenquelle herstellen
Obgleich Sie im Folgenden feststellen werden, dass viele Vorgehensweisen unter DAO
und ADO gleich ablaufen, unterscheidet sich das Objektmodell von ADO in einigen Punk­
978
Zugriff auf eine Datenquelle herstellen
ten vom DAO-Objektmodell. Das macht sich beim Zugriff auf die gewünschte Da­ten­bank
sofort bemerkbar, wie der folgende Abschnitt zeigen wird.
Connection und ConnectionString
Das beginnt damit, dass nicht das Database-Objekt, sondern das Connection-Objekt Ur­
sprung aller lesenden, schreibenden und sonstigen Zugriffe auf die Tabellen der Da­ten­
bank ist. Das Connection-Objekt enthält immer einen ConnectionString, der Informationen
über die Verbindung zur gewünschten Datenbank enthält. Diesen ConnectionString stellt
man entweder selbst zusammen oder man lässt sich dabei unterstützen. Die einfachste
Methode dazu ergibt sich beim Zugriff auf die aktuelle Datenbank. In diesem Fall brauchen Sie einfach nur auf das Connection-Objekt des bestehenden CurrentProject-Objekts
zuzugreifen.
Public Sub Verbindung()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
With cnn
Debug.Print cnn.ConnectionString
End With
End Sub
Listing 1.1: Verweisen auf eine Verbindung zur aktuellen Datenbank und Ausgabe der Connection­
String-Eigenschaft
Die Verbindungszeichenfolge für die aktuelle Datenbank sieht etwa so aus – gut, dass
Sie diesen Ausdruck nicht selbst zusammenstellen müssen:
Provider=Microsoft.ACE.OLEDB.12.0Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=E:\
ADO.accdbmdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\
Dokumente und Einstellungen\Administrator\Anwendungsdaten\Microsoft\Access\System.
mdw;Jet OLEDB:Registry Path= Software\Microsoft\Office\12.0\Access\Access Connectivity
EngineSoftware\Microsoft\Office\11.0\Access\Jet\4.0;Jet OLEDB:Database Password="";Jet
OLEDB:Engine Type=65;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't
Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False;Jet OLEDB:Support Complex Data=True
Was die Parameter im Einzelnen bedeuten, soll hier gar nicht aufgeschlüsselt werden.
Für die Verwendung der Datenbank, zu der auch das VBA-Projekt gehört, reicht die
Kenntnis, dass CurrentProject.Connection die richtige Verbindung liefert.
Für den Fall, dass Sie einmal eine Verbindung zu einer externen Datenquelle herstellen
möchten, brauchen Sie den ConnectionString auch nicht unbedingt manuell zusammen-
979
Zusatzkapitel ADO
zustellen. Zur Ermittlung der passenden Parameter können Sie auch einen Dialog verwenden, der bei der Festlegung der Parameter behilflich ist.
Die folgende Routine erzeugt eine Instanz des DataLinks-Objekts und zeigt mit der
Methode PromptNew den Dialog zum Anlegen einer neuen Verbindungszeichenfolge an
(siehe Abbildung 1.1).
Public Function ConnectionStringErmitteln()
ConnectionStringErmitteln = CreateObject("DataLinks").PromptNew
End Function
Listing 1.2: Aufruf des Dialogs zum Ermitteln einer Verbindungszeichenfolge
Mit dem gleichen Dialog können Sie nicht nur die Eigenschaften neuer Verbindungen
auswählen und zurückgeben lassen, sondern auch die Eigenschaften von CurrentProject.
Connection etwas übersichtlicher ausgeben (siehe Abbildung 1.2). Dazu müssen Sie die
aufrufende Routine geringfügig abändern:
Public Function ConnectionStringBearbeiten()
ConnectionStringBearbeiten = _
CreateObject("DataLinks").PromptEdit(CurrentProject.Connection)
End Function
Listing 1.3: Aufrufen des Dialogs Datenverknüpfungseigenschaften für eine bestehende Ver­
bindungs­zeichenfolge
Abbildung 1.1: Anlegen einer neuen Verbindung
980
Manipulation des Datenmodells
Abbildung 1.2: Bearbeiten von CurrentProject.Connection
1.2 Manipulation des Datenmodells
Die Manipulation von Tabellen, Feldern und anderen Datenbankobjekten erfolgt nicht
über ADO selbst, sondern über die Objekte, Methoden und Eigenschaften der Bibliothek
ADOX. Die entsprechende Bibliothek im Verweise-Dialog heißt Microsoft ADO Ext. 2.8 for
DDL and Security (oder älter).
1.2.1 Anlegen einer Tabelle
Zum Anlegen einer Tabelle mit reinen ADO-Mitteln benötigen Sie das Catalog-Objekt der
ADOX-Bibliothek (es ginge ja auch mit CurrentProject.Execute "CREATE TABLE..."). Dieses
wird mit der Eigenschaft ActiveConnection auf die aktuelle Datenbank eingestellt. Zum
Löschen einer eventuell schon vorhandenen gleichnamigen Tabelle stellt das CatalogObjekt die Delete-Methode der Tables-Auflistung zur Verfügung.
Anschließend wird ein neues Table-Objekt erstellt und mit dem Namen tblUnternehmen
versehen. Bevor die Tabelle an die Tables-Auflistung angehängt und damit verfügbar gemacht wird, fügen Sie zwei Felder an: UnternehmenID und Unternehmen. Dabei werden
zwei unterschiedliche Vorgehensweisen verwendet: Die erste instanziert zunächst ein
neues Column-Objekt, füllt dessen Eigenschaften mit den entsprechenden Daten und
fügt es dann an die Columns-Auflistung des Table-Objekts an. Die zweite übergibt die
benötigten Informationen direkt beim Anfügen eines neuen Feldes an das Table-Objekt.
Nach dem Anlegen werden die Tables-Auflistung und der Navigationsbereich aktualisiert.
981
Zusatzkapitel ADO
Public Sub TabelleAnlegen_Unternehmen()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Bestehende Tabelle löschen
cat.Tables.Delete "tblUnternehmen"
'Verweis auf neues Table-Objekt
Set tbl = New ADOX.Table
'Name der Tabelle zuweisen
tbl.Name = "tblUnternehmen"
'Feld neu erstellen und per Objektvariable referenzieren
Set col = New ADOX.Column
col.Name = "UnternehmenID"
col.Type = adInteger
tbl.Columns.Append col
'Noch ein Feld erstellen, kurze Fassung
tbl.Columns.Append "Unternehmen", adVarWChar, 30
'Tabelle anhängen und Katalog aktualisieren
With cat.Tables
.Append tbl
.Refresh
End With
'Navigationsbereich aktualisieren
Application.RefreshDatabaseWindow
Set tbl = Nothing
Set cat = Nothing
End Sub
Listing 1.4: Anlegen einer Tabelle mit ADOX
Konstanten für Datentypen unter ADO und ADOX
Tabelle 1.1 zeigt die häufigsten ADOX-Konstanten für die unterschiedlichen Daten­typen.
Konstante
Datentyp
adBigInt
Big Integer
adBinary
Binary
adBoolean
Boolean
adUnsignedTinyInt
Byte
adChar
Char
982
Manipulation des Datenmodells
Konstante
Datentyp
adCurrency
Currency
adDate
Date/Time
adNumeric
Decimal
adDouble
Double
adGUID
GUID
adSmallInt
Integer
adInteger
Long
adLongVarBinary
Long Binary (OLE Object), Attachment.FileData
adLongVarWChar
Memo
adNumeric
Numeric
adSingle
Single
adWChar, adVarWChar
Text (Unicode)
adDBTime
Time
adIDispatch
Attachment, ComplexTypes
adDBTimeStamp
Time Stamp
Tabelle 1.1: Konstanten für den Datentyp
1.2.2 Autowert anlegen
Wenn Sie das Feld UnternehmenID als Autowert festlegen möchten, müssen Sie in obiger Routine noch einige Zeilen hinter dem Anlegen des Feldes hinzufügen:
'Anlegen eines Autowertes
With tbl.Columns("UnternehmenID")
.ParentCatalog = cat
.Properties("AutoIncrement") = True
End With
Die Eigenschaft AutoIncrement gilt nur für Access-Datenbanken.
1.2.3 Löschen einer Tabelle
Das Löschen einer Tabelle erfolgt über die Delete-Methode der Tables-Auflistung. Die folgende Routine löscht die soeben erstellte Tabelle und aktualisiert die Tables-Auflistung
sowie den Navigationsbereich.
Public Sub TabelleLoeschen_Unternehmen()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
983
Zusatzkapitel ADO
cat.ActiveConnection = CurrentProject.Connection
'Bestehende Tabelle löschen
On Error Resume Next
cat.Tables.Delete "tblUnternehmen"
On Error GoTo 0
'Katalog aktualisieren
cat.Tables.Refresh
'Navigationsbereich aktualisieren
Application.RefreshDatabaseWindow
Set cat = Nothing
End Sub
Listing 1.5: Löschen einer Tabelle mit ADOX
1.2.4 Erstellen eines Indexes
Mit der folgenden Routine fügen Sie der Tabelle tblUnternehmen einen Primärindex auf
dem Feld UnternehmenID hinzu.
Public Sub IndexErstellen()
Dim cat As ADOX.Catalog
Dim idx As ADOX.Index
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim idxs As ADOX.Indexes
'Catalog instanzieren und auf aktuelle Datenbank einstellen
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Tabelle festlegen
Set tbl = cat.Tables("tblUnternehmen")
'Verweis auf Indexes-Auflistung erstellen
Set idxs = tbl.Indexes
'Neues Index-Objekt erstellen
Set idx = New ADOX.Index
With idx
'Index-Objekt mit Eigenschaften ausstatten
.Name = "PrimaryKey"
.PrimaryKey = True
.Unique = True
'Column-Objekt mit zu indizierendem Feld erzeugen
'und zur Auflistung der indizierten Columns hinzufügen
Set col = New ADOX.Column
984
Manipulation des Datenmodells
col.Name = "UnternehmenID"
.Columns.Append col
End With
'Index an die Auslistung Indexes anfügen
idxs.Append idx
Set col = Nothing
Set idx = Nothing
Set idxs = Nothing
Set tbl = Nothing
Set cat = Nothing
End Sub
Listing 1.6: Anlegen eines Index mit ADOX
1.2.5 Löschen eines Indexes
Zum Entfernen eines Indexes verwenden Sie die folgende Routine. Sie setzt die DeleteMethode der Indexes-Auflistung zum Entfernen des Indexes ein.
Public Sub IndexLoeschen()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim idxs As ADOX.Indexes
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables("tblUnternehmen")
Set idxs = tbl.Indexes
'Index aus der Auflistung löschen
idxs.Delete "PrimaryKey"
Set tbl = Nothing
Set idxs = Nothing
Set cat = Nothing
End Sub
Listing 1.7: Löschen eines Indexes aus der Auflistung der Indizes einer Tabelle
1.2.6 Erstellen einer Beziehung
Um eine Beziehung zwischen zwei Tabellen herzustellen, verwenden Sie die folgende
Routine.
Voraussetzung ist, dass das Fremdschlüsselfeld der Detailtabelle den gleichen Daten­
typ wie das Primärschlüsselfeld der Mastertabelle hat. Außerdem muss der Primär­
985
Zusatzkapitel ADO
schlüssel der Mastertabelle eindeutig sein. Sind die Voraussetzungen erfüllt (und
die angegebenen Tabellen beziehungsweise Felder vorhanden), legt die Routine die
Beziehung aus Abbildung 1.3 an.
Wenn Sie zusätzlich Löschweitergabe oder Aktualisierungsweitergaben definieren
möchten, müssen Sie die Eigenschaften DeleteRule und UpdateRule des key-Objekts mit
den entsprechenden Werten bestücken. Mit der Konstanten adRICascade sorgen Sie für
die Weitergabe der jeweiligen Aktion:
Public Sub BeziehungErstellen()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim key As ADOX.key
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Tabelle mit dem Fremdschlüsselfeld festlegen
Set tbl = cat.Tables("tblMitarbeiter")
'Neuen Key instanzieren und Eigenschaften zuweisen
Set key = New ADOX.key
key.Name = "ForeignKey"
key.Type = adKeyForeign
'Tabelle mit Primärschlüssel festlegen
key.RelatedTable = "tblUnternehmen"
'Verknüpfungsfeld der Detailtabelle angeben
key.Columns.Append "UnternehmenID"
'Optional: Lösch- oder Aktualisierungsweitergabe
key.DeleteRule = adRICascade
key.UpdateRule = adRICascade
'Verknüpfungsfeld der Mastertabelle angeben
key.Columns("UnternehmenID").RelatedColumn = "UnternehmenID"
'Key an die Keys-Auflistung anhängen
tbl.keys.Append key
Set key = Nothing
Set tbl = Nothing
Set cat = Nothing
End Sub
Listing 1.8: Anlegen einer Beziehung zwischen zwei Tabellen
986
Zugriff auf Tabellen, Abfragen und die darin enthaltenen Daten
Abbildung 1.3: Mit ADOX erstellte Beziehung
1.2.7 Löschen einer Beziehung
Die Beziehung beziehungsweise den Fremdschlüssel löschen Sie mit der folgenden
Routine:
Public Sub BeziehungLoeschen()
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Beziehung in Form des Fremdschlüssels löschen
cat.Tables("tblMitarbeiter").keys.Delete "ForeignKey"
Set cat = Nothing
End Sub
Listing 1.9: Löschen einer Beziehung
1.3 Zugriff auf Tabellen, Abfragen und die
darin enthaltenen Daten
In den folgenden Abschnitten erfahren Sie, wie Sie auf die Daten der Datenbank zugreifen können.
1.3.1 Ausgeben aller Tabellen
Im vorigen Kapitel zum Thema DAO haben Sie erfahren, wie Sie mit DAO alle Tabellen
ausgeben oder prüfen, ob eine Tabelle vorhanden ist. Unter ADO erledigen Sie dies wie
in folgendem Beispiel:
Sub DBInfoADO()
Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
Set rs = CurrentProject.Connection.OpenSchema(adSchemaTables)
Do While Not rs.EOF
987
Zusatzkapitel ADO
For Each fld In rs.Fields
Debug.Print , fld.Name, "=", fld.Value
Next fld
rs.MoveNext
Debug.Print
Loop
rs.Close
End Sub
Das liefert beispielsweise solche Informationen:
TABLE_CATALOG =
Null
TABLE_SCHEMA
=
Null
TABLE_NAME
=
tblDateien
TABLE_TYPE
=
TABLE
TABLE_GUID
=
Null
DESCRIPTION
=
Null
TABLE_PROPID
=
Null
DATE_CREATED
=
21.01.2007 14:52:08
DATE_MODIFIED =
21.01.2007 14:52:11
Wenn Sie nur die benutzerdefinierten Tabellen ausgeben möchten, erledigen Sie das
etwa so:
Sub DBInfoADO()
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Set rst = CurrentProject.Connection.OpenSchema(adSchemaTables)
Debug.Print "Tabellen:"
Do While Not rst.EOF
For Each fld In rst.Fields
If fld.Name = "TABLE_NAME" And rst("TABLE_TYPE") = "TABLE" Then
Debug.Print fld.Value
End If
Next fld
rst.MoveNext
Loop
rst.Close
End Sub
Es gibt noch einige weitere Konstanten, die Sie der OpenSchema-Methode übergeben
können, zum Beispiel die Folgenden – experimentieren Sie einfach mal damit:
»» adSchemaColumns
988
Zugriff auf Tabellen, Abfragen und die darin enthaltenen Daten
»» adSchemaConstraintColumnUsage
»» adSchemaForeignKeys
»» adSchemaIndexes
»» adSchemaKeyColumnUsage
»» adSchemaPrimaryKeys
»» adSchemaProcedures und adSchemaViews (Abfragen)
»» adSchemaProviderTypes
»» adSchemaReferentialConstraints
»» adSchemaStatistics (Beziehungen)
»» adSchemaTableConstraints
»» adSchemaTrustees (DB-Benutzer)
1.3.2 Prüfen, ob eine Tabelle vorhanden ist
Auch hier brauchen Sie nicht mit ADO zu arbeiten, sondern können die AllTablesAuflistung verwenden:
Public Function IstTabelleVorhanden_ADO(strTabellenname As String) _
As Boolean
Dim objTable As AccessObject
On Error Resume Next
Set objTable = CurrentData.AllTables (strTabellenname)
IstTabelleVorhanden_ADO = Not objTable Is Nothing
End Function
Listing 1.10: Prüfen des Vorhandenseins einer Tabelle
1.3.3 Datensatzgruppe auf Basis einer Tabelle öffnen
Das Öffnen einer Datensatzgruppe erfolgt anders als unter DAO. Die Methode zum
Öffnen ist keine Methode des übergeordneten Objekts (bei DAO das Database-Objekt),
sondern eine Methode des ADO-Recordset-Objekts selbst. Die Open-Methode erwartet den Namen der zu öffnenden Tabelle oder Abfrage beziehungsweise einen SQLAusdruck, den Connection-String sowie zwei Parameter zur Angabe des Cursor-Typs
und des Sperrmechanismus. Die Beschreibung der verschiedenen Möglichkeiten finden Sie im Anschluss an die Routine zum Öffnen einer Datensatzgruppe. Hier tritt offen
zu Tage, weshalb es sich lohnt, bei der Variablendeklaration explizit die Bibliothek mit
989
Zusatzkapitel ADO
anzugeben, aus der die Objekte stammen: Wenn Sie das versäumt haben und die DAOBibliothek ist in der Liste der Verweise oberhalb der ADO-Bibliothek angeordnet, sucht
die folgende Routine vergeblich die Open-Methode des Recordset-Objekts.
Public Sub DatensatzgruppeOeffnen()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "tblUnternehmen", cnn, adOpenDynamic, adLockOptimistic
With rst
'etwas mit der Datenatzgruppe machen
End With
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Listing 1.11: Öffnen einer Datensatzgruppe mit ADO
Das eigentliche Öffnen der Datensatzgruppe kann auch so erfolgen, wobei zunächst die
Eigenschaften festgelegt werden und erst dann die Da­ten­satz­grup­pe geöffnet wird:
rst.ActiveConnection = cnn
rst.LockType = adLockOptimistic
rst.CursorType = adOpenKeyset
rst.Open "tblUnternehmen"
1.3.4 Cursor-Typen
Beim Öffnen einer Datensatzgruppe unter ADO können Sie folgende Cursor-Typen für
die Eigenschaft CursorType verwenden:
»» a
dOpenDynamic: Liefert eine Gruppe von Datensätzen und zeigt Daten­satz­än­de­run­
gen an­derer Benutzer an (entspricht dbOpenDynaset unter DAO).
»» a
dOpenForwardOnly: Liefert einen Snapshot der gewünschten Datensätze zum Zeit­
punkt des Öffnens des Recordset-Objekts, kann nur vorwärts durchlaufen werden
(ent­spricht dbOpenForwardOnly unter DAO).
»» a
dOpenKeyset: Liefert Verweise auf die Datensätze der zugrunde liegenden Tabellen
(ent­spricht keiner DAO-Konstante genau, ist aber fast äquivalent zu dbOpenDynaset
und etwas schneller als adOpenDynamic).
990
Zugriff auf Tabellen, Abfragen und die darin enthaltenen Daten
»» a
dOpenStatic: Liefert einen Snapshot der gewünschten Datensätze zum Zeitpunkt
des Öffnens des Recordset-Objekts (entspricht dbOpenSnapshot unter DAO).
Achtung: Bei Recordsets, die mit Connections geöffnet werden, die nicht CurrentProject
entsprechen, müssen Sie zusätzlich die CursorLocation auf den Wert adUseClient festlegen:
cnn.CursorLocation = adUseClient
1.3.5 Sperrung von Daten
Mit dem Parameter LockType legen Sie fest, wie die Daten beim Schreiben gesperrt
werden sollen:
»» adLockReadonly: Öffnet ein schreibgeschütztes Recordset.
»» a
dLockPessimistic: Sperrt die komplette Speicherseite, in der sich der von einer Än­
de­rung betroffene Datensatz befindet, sobald die Bearbeitung beginnt.
»» a
dLockOptimistic: Sperrt die komplette Speicherseite, in der sich der von einer Än­de­
rung betroffene Datensatz befindet, erst, wenn der Datensatz aktualisiert wird.
»» adLockBatchOptimistic: Wie adLockOptimistic, aber für die UpdateBatch-Methode.
1.3.6 Datensätze eines Recordsets durchlaufen
Zum Durchlaufen der Datensätze eines Recordsets verwenden Sie beispielsweise die
Do While-Schleife, in der Sie nach dem Durchführen der gewünschten Aktion jeweils
mit der MoveNext-Methode einen Datensatz weiter springen. Als Abbruchbedingung
dient die EOF-Eigenschaft der Datensatzgruppe, die den Wert True erhält, wenn der
Datensatzzeiger über den letzten Datensatz hinaus verschoben wurde.
Public Sub DatensaetzeDurchlaufen()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "tblUnternehmen", cnn, adOpenKeyset, adLockOptimistic
Do While Not rst.EOF
With rst
'etwas mit dem aktuellen Datensatz tun
End With
rst.MoveNext
Loop
rst.Close
991
Zusatzkapitel ADO
Set rst = Nothing
Set cnn = Nothing
End Sub
Listing 1.12: Datensatzgruppe durchlaufen
Das Pendant zur EOF-Eigenschaft ist die BOF-Eigenschaft. Sie erhält den Wert True,
wenn der Datensatzzeiger sich vor dem ersten Datensatz der Datensatzgruppe befindet. Neben der MoveNext-Methode gibt es noch die Methoden MoveFirst, MoveLast und
MovePrevious zum Bewegen innerhalb der Datensatzgruppe.
1.3.7 Daten eines Recordsets mit mehrwertigen Feldern
ausgeben
ADO bietet keine besondere Möglichkeit, um auf die Inhalte mehrwertiger Felder zuzugreifen – es gibt also kein Recordset2- oder Field2-Objekt wie unter DAO. Daher können
Sie nur auf die in solchen Feldern enthaltenen Daten zugreifen, wenn Sie diese in der
Datenherkunft über die »Unterfelder« referenzieren, wie das folgende Beispiel zeigt:
Public Sub DatensaetzeDurchlaufen_MehrwertigesFeld()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT MehrwertigesFeld.Value " _
& "FROM tblMehrwertigesFeld", _
cnn, adOpenKeyset, adLockOptimistic
Do While Not rst.EOF
With rst
Debug.Print rst.Fields("MehrwertigesFeld.Value")
End With
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Listing 1.13: Zugriff auf mehrwertige Felder mit einem ADO-Recordset
992
Zugriff auf Tabellen, Abfragen und die darin enthaltenen Daten
1.3.8 Daten eines Recordsets mit Attachment-Feldern
ausgeben
Der Umgang mit den in Attachment-Feldern gespeicherten Daten sieht prinzipiell wie
bei den mehrwertigen Feldern aus:
Public Sub DatensaetzeDurchlaufen_Attachment()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT DateiID, Datei.FileType, " _
& "Datei.Filename FROM tblDateien", _
cnn, adOpenKeyset, adLockOptimistic
Do While Not rst.EOF
With rst
Debug.Print rst!DateiID, rst.Fields("Datei.FileType"), _
rst.Fields("Datei.Filename")
End With
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Listing 1.14: Zugriff auf Attachment-Felder
1.3.9 Anzahl der Datensätze in einer Datensatzgruppe
ermitteln
Um die Anzahl der Datensätze zu ermitteln, dürfen Sie nicht die Konstante adOpenFor­
ward­Only für die Eigenschaft CursorType einsetzen. Außerdem müssen Sie für die Ei­gen­
schaft CursorLocation den Parameter adUseClient verwenden, da serverseitige Da­ten­­
satz-Cur­sor unter ACE das Zählen von Datensätzen nicht unterstützen.
Public Sub Datensatzanzahl()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
993
Zusatzkapitel ADO
rst.CursorType = adOpenForwardOnly
rst.LockType = adLockOptimistic
rst.ActiveConnection = cnn
rst.Open "tblUnternehmen"
Debug.Print rst.RecordCount
End Sub
Listing 1.15: Ermitteln der Datensatzanzahl eines Recordsets
1.3.10 Prüfen, ob eine Datensatzgruppe leer ist
Eine einfache Möglichkeit, um herauszufinden, ob eine Datensatzgruppe leer ist, besteht im Prüfen der EOF- und der BOF-Eigenschaften der Datensatzgruppe:
Public Sub LeereDatensatzgruppe()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "tblMitarbeiter", cnn, adOpenForwardOnly, adLockOptimistic
If rst.BOF And rst.EOF Then
MsgBox "Die Datensatzgruppe ist leer."
End If
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Listing 1.16: Prüfen, ob eine Datensatzgruppe leer ist
1.3.11 Ausgabe des Inhalts eines Recordsets
Manchmal möchte man den Inhalt eines Recordsets auf die Schnelle betrachten oder
etwa in einer Textdatei speichern. Dabei leistet die GetString-Methode gute Dienste.
Diese Methode ist sehr flexibel und kann, mit zusätzlichen Parametern ausgestattet, die
Spalten auch als formatierten Text zurückgeben.
Informationen zu den (optionalen) Parametern finden Sie unter http://msdn.microsoft.
com/en-us/library/ms676975%28v=vs.85%29.aspx.
Public Sub DatensatzgruppeAusgeben()
…
rst.Open "tblUnternehmen", cnn, adOpenForwardOnly, adLockOptimistic
994
Zugriff auf Tabellen, Abfragen und die darin enthaltenen Daten
Debug.Print rst.GetString
…
End Sub
Listing 1.17: Ausgeben der Daten einer Datensatzgruppe
1.3.12 Speichern der Daten in einem Array
Wenn Sie die Daten einer Datensatzgruppe in einem Array weiter verarbeiten möchten, können Sie mit der GetRows-Methode ein zweidimensionales Array mit den in der
Datensatzgruppe enthaltenen Daten füllen.
Die GetRows-Methode hat drei Parameter: Rows gibt an, wie viele Datensätze eingelesen werden sollen, Start enthält ein Bookmark auf den ersten einzulesenden Datensatz
und Fields die Position oder den Namen des einzulesenden Feldes beziehungsweise ein
Array mit den Namen der einzulesenden Felder. Ohne die Angabe von Parametern liest
GetRows alle Datensätze mit allen Feldern ein.
Public Sub DatensatzgruppeInArray()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim varRecordset() As Variant
Dim i As Integer
Dim j As Integer
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "tblUnternehmen", cnn, adOpenForwardOnly, adLockOptimistic
varRecordset = rst.GetRows()
For i = 0 To UBound(varRecordset, 2)
For j = 0 To UBound(varRecordset, 1)
Debug.Print varRecordset(j, i)
Next j
Next i
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Listing 1.18: Einlesen der Daten einer Datensatzgruppe in ein Array
995
Zusatzkapitel ADO
1.3.13 Abfragen mit Parametern verwenden
Während Sie unter DAO das QueryDef-Objekt und die Parameters-Auflistung verwendet
haben, um Abfragen mit Parametern zu handhaben, stellt ADO für diesen Zweck das
Command-Objekt zur Verfügung.
Das folgende Beispiel zeigt, wie Sie ein Recordset auf Basis einer mit Parametern versehenen Abfrage öffnen. Sie verwenden hier nicht wie bei DAO eine Parameters-Auflistung,
sondern geben den Wert eines einzelnen Parameters als String und mehrere Werte in
der richtigen Reihenfolge als String-Array an – bei anderen Datentypen für die Parameter
verwenden Sie dann eher ein Variant-Array.
Public Sub Parameterabfrage()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim lngRecordsAffected As Long
Set cnn = CurrentProject.Connection
'Command-Objekt instanzieren
Set cmd = New ADODB.Command
'Aktuelle Verbindung zuweisen
cmd.ActiveConnection = cnn
'auszuführende Abfrage angeben
cmd.CommandText = "qryMitarbeiter"
'Typ des Commands festlegen
cmd.CommandType = adCmdTable
'Command-Objekt ausführen. Der erste Parameter wird nur für
'Aktionsabfragen benötigt, der zweite enthält die Werte für die
'Abfrageparameter. Ein Parameter wird als String, mehrere als
'Array übergeben.
Set rst = cmd.Execute(, "Minhorst")
Do While Not rst.EOF
Debug.Print rst!Vorname, rst!Nachname
rst.MoveNext
Loop
Set rst = Nothing
Set cnn = Nothing
End Sub
Listing 1.19: Erzeugen eines Recordsets auf Basis einer Parameterabfrage
996
Datensätze suchen
1.4 Datensätze suchen
Zur Suche von Datensätzen gibt es mehrere Möglichkeiten. Wie unter DAO können Sie
indizierte Felder mit der Seek-Methode durchsuchen, anderenfalls hilft die Find-Me­
thode weiter.
Diese ist allerdings nicht so flexibel wie die Find…-Methoden von DAO, wie Sie nachfolgend lesen können. Am einfachsten ist es jedoch, die gesuchten Da­ten­sätze direkt in der
Datenherkunft des Recordset-Objekts einzugrenzen.
1.4.1 Gesuchte Datensätze per Source-Eigenschaft des
Recordsets ermitteln
Die Source-Eigenschaft eines Recordset-Objekts enthält die dem Recordset-Objekt zugrunde liegende Tabelle oder Abfrage. Sie können hier auf drei Arten bereits mit dem
Öffnen des Recordset-Objekts die gewünschten Daten ausfindig machen:
»» Direkte Angabe einer SELECT-Anweisung (jeweils in einer Zeile):
rst.Open "SELECT * FROM Artikel WHERE Artikelname LIKE 'A%'", cnn,
adOpenKeyset, adLockOptimistic
»» Angabe einer gespeicherten Abfrage:
rst.Open "qryArtikelMitPreisGroesser50", cnn, adOpenKeyset,
adLockOptimistic
»» Kombination aus SELECT-Anweisung und gespeicherter Abfrage:
rst.Open "SELECT * FROM qryArtikelMitPreisGroesser50 WHERE Artikelname LIKE 'T%'",
cnn, adOpenKeyset, adLockOptimisticJoker in Zeichenketten unter ADO und SQL
Wenn Sie wie in den obigen Beispielen Vergleichsausdrücke mit Platzhaltern verwenden möchten, müssen Sie die SQL Server-Syntax verwenden. Dabei entspricht der
Platzhalter für beliebig viele Zeichen dem Prozentzeichen (%) und nicht wie in VBA oder
Abfragen, die Sie über die Abfrageentwurfsansicht erstellen (siehe Abbildung 1.4), dem
Sternchen (*). Der Platzhalter für ein einzelnes Zeichen entspricht dem Unterstrich (_)
und nicht wie in VBA oder Abfragen dem Fragezeichen (?).
997
Zusatzkapitel ADO
Abbildung 1.4: Joker für beliebig viele Zeichen in Abfragen
1.4.2 Seek
Wenn Sie mit der Seek-Methode nach Daten suchen möchten, müssen zwei Bedingungen
erfüllt sein: Das zu durchsuchende Feld muss indiziert sein und Sie müssen die
Konstante adCmdTableDirect als Option beim Öffnen der Datensatzgruppe festlegen.
Ersteres prüfen Sie ganz einfach, indem Sie in der Entwurfsansicht einer Tabelle den
Indizes-Dialog einblenden (Ribbon-Eintrag Entwurf|Einblenden/Ausblenden|Indizes, siehe Abbildung 1.5). Letzteres impliziert, dass Sie nur auf einzelne Tabellen, aber nicht auf
Abfragen oder verknüpfte Tabellen zugreifen können.
Abbildung 1.5: Anzeigen der Indizes einer Tabelle
998
Datensätze suchen
Die Option adcmdTableDirect verwenden Sie mit der Open-Methode des RecordsetObjekts. Die folgende Routine zeigt, wie Sie mit der Seek-Methode einen bestimmten
Datensatz einer Tabelle finden und den Wert seines Primärschlüsselfeldes ausgeben.
Im Gegensatz zur weiter unten vorgestellten Find-Methode enthält das Recordset-Objekt
nicht alle Datensätze, die dem Suchkriterium entsprechen, sondern es wird lediglich
der Datensatzzeiger auf einem Datensatz platziert, der den mit der Seek-Methode übergebenen Parametern entspricht.
Dabei gibt es verschiedene Varianten, die Sie mit dem Parameter SeekOption übergeben.
Die wichtigsten sind folgende:
»» a
dSeekFirstEQ: Setzt den Datensatzzeiger auf den ersten Datensatz mit dem angegebenen Wert.
»» a
dSeekLastEQ: Setzt den Datensatzzeiger auf den letzten Datensatz mit dem angegebenen Wert.
Seek ist unter den gegebenen Bedingungen die schnellste Möglichkeit, um auf einen
bestimmten Datensatz zuzugreifen.
Public Sub SuchenMitSeek()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
'Recordset mit direktem Zugriff auf die Tabelle öffnen
rst.Open "Artikel", cnn, adOpenKeyset, adLockOptimistic, _
adCmdTableDirect
'Index festlegen: Achtung, Indexname und nicht den Feldnamen verwenden!
rst.Index = "Artikelname"
'Suche starten
rst.Seek "Chocolade", adSeekFirstEQ
'Aktuellen Datensatz anzeigen
If Not rst.EOF Then
Debug.Print rst![Artikel-Nr]
End If
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Listing 1.20: Suche mit der Seek-Methode
999
Zusatzkapitel ADO
1.4.3 Find
Die Find-Methode ist weniger flexibel als die Find…-Methoden von DAO. Sie fasst zwar
die Funktion der Find…-Methoden von DAO zusammen, beschränkt allerdings beispielsweise die möglichen Kriterien auf ein einziges.
Die Find-Methode bietet vier Parameter, von denen nur die Angabe des Suchkriteriums
Pflicht ist. Als Suchkriterium dient ein aus Feldname, Vergleichsoperator und Ver­
gleichs­
wert zusammengesetzter Ausdruck, wobei die gleichen Regeln wie für die
WHERE-Klau­sel von SQL-Abfragen gelten (siehe auch »Access-SQL« ab Seite 453). Die
übrigen Pa­rameter:
»» S
kipRecords legt fest, wie viele Datensätze von der aktuellen Position aus übersprungen werden sollen, und ist beispielsweise wichtig, wenn Sie bereits einen Datensatz
ge­funden haben und bei der Suche des nächsten Datensatzes nicht auf dem aktuellen Datensatz stehen bleiben möchten.
»» S
earchDirection erwartet eine der Konstanten adSearchForward oder adSearchBack­
ward und sucht in der entsprechenden Richtung.
»» S
tart erwartet die Position des Datensatzzeigers für einen vom ersten Datensatz abweichenden Startpunkt.
Wenn die Find-Methode einen Datensatz findet, positioniert sie den Datensatzzeiger auf
dem gefundenen Datensatz. Sollen weitere Datensätze gefunden werden, wie etwa im
folgenden Beispiel, verwenden Sie innerhalb einer Do While-Schleife erneut die FindMe­thode – mit dem gleichen Kriterium, aber dem Wert 1 für den Parameter SkipRecords.
Dies hat den Grund, dass die Find-Methode mit der Suche immer in dem Datensatz be­
ginnt, auf dem sich aktuell der Datensatzzeiger befindet.
Eine solche Suche bedingt immer die Prüfung auf die Eigenschaft EOF oder BOF der
Da­tensatzgruppe (je nach Suchrichtung), da dies die Position des Datensatzzeigers ist,
wenn kein Datensatz gefunden wurde. Das entspricht dann der Eigenschaft Recordset.
No­Match unter DAO.
Public Sub SuchenMitFind()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strKriterium As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "Artikel", cnn, adOpenKeyset, adLockOptimistic
strKriterium = "[Artikelname] LIKE 'A*'"
rst.Find strKriterium
Do While Not rst.EOF
1000
Datensätze suchen
Debug.Print rst!Artikelname
rst.Find strKriterium, 1
Loop
…
End Sub
Listing 1.21: Suche nach allen Datensätzen, deren Artikelname mit A beginnt
1.4.4 Filtern
Genau wie DAO enthält das Recordset-Objekt auch unter ADO eine Eigenschaft namens
Filter. Unter DAO weist man einem Recordset-Objekt den Filter zu und erhält das gefilterte Ergebnis, wenn man ein weiteres Recordset-Objekt auf Basis des ersten öffnet.
Dies ist unter ADO einfacher: Hier weisen Sie einfach das gewünschte Filterkriterium zu
und können direkt im gleichen Recordset auf das gefilterte Ergebnis zugreifen.
Public Sub FilternEinesRecordset()
…
rst.Open "Artikel", cnn, adOpenKeyset, adLockOptimistic
rst.Filter = "Artikelname LIKE 'A*'"
Do While Not rst.EOF
Debug.Print rst!Artikelname
rst.MoveNext
Loop
…
End Sub
Listing 1.22: Filtern aller Datensätze, deren Artikelname mit A beginnt
Um den Filter zu entfernen, verwenden Sie die Konstante adFilterNone an Stelle eines
Parameters:
rst.Filter = adFilterNone
Filter unterliegen unter ADO einigen Einschränkungen. Mehr dazu unter folgendem
Link: http://msdn.microsoft.com/en-us/library/ms676691%28v=VS.85%29.aspx
1.4.5 Sortieren
Die Datensätze eines Recordsets können Sie entweder bereits über die Datenherkunft
oder durch nachträgliches Hinzufügen eines Sortierkriteriums sortieren. In der Da­ten­
her­kunft gibt es zwei Möglichkeiten:
»» S
ie öffnen die Datensatzgruppe mit der Option adTableDirect. In diesem Fall kommt
die im Dialog Indizes angegebene Sortierung zur Anwendung.
1001
Zusatzkapitel ADO
»» S
ie öffnen eine gespeicherte Abfrage mit voreingestellter Sortierreihenfolge oder
geben einen SQL-Ausdruck mit einem entsprechenden Sortierkriterium an.
Sortieren nach dem Öffnen des Recordsets
Wie in DAO bietet auch ADO eine Eigenschaft zum Angeben eines Sortierkriteriums.
Voraussetzung für seinen Einsatz ist, dass das Recordset-Objekt einen clientseitigen
Cur­sor verwendet. Wie auch die Filter-Eigenschaft wirkt sich die Sort-Eigenschaft unmittelbar auf die aktuelle Datensatzgruppe aus.
Nach dem Öffnen des Recordsets können Sie eine Sortierung mit der Sort-Eigenschaft
vornehmen:
Public Sub SortierenEinesRecordset()
…
rst.CursorLocation = adUseClient
rst.Open "Artikel", cnn, adOpenDynamic, adLockOptimistic
rst.Sort = "Artikelname ASC"
Do While Not rst.EOF
Debug.Print rst!Artikelname
rst.MoveNext
Loop
…
End Sub
Listing 1.23: Nachträgliches Sortieren einer Datensatzgruppe
1.4.6 Lesezeichen
Auch ADO-Recordsets bieten eine Bookmark-Eigenschaft. Bookmarks dienen dazu, sich
die Position eines Datensatzes zu merken und auf einen gemerkten Datensatz zurückzuspringen. Daher ist diese Eigenschaft les- und schreibbar. Unter Verwendung eines
Bookmarks können Sie beispielsweise von einem bestimmten Datensatz an das Ende
der Datensatzgruppe und anschließend wieder zurück zum ursprünglichen Datensatz
springen.
Public Sub SpringenMitBookmark()
…
Dim varLesezeichen As Variant
…
rst.Open "Artikel", cnn, adOpenKeyset, adLockOptimistic
rst.Find "Artikelname = 'Chai'"
Debug.Print rst!Artikelname
1002
Datensätze bearbeiten
varLesezeichen = rst.Bookmark
rst.MoveLast
Debug.Print rst!Artikelname
rst.Bookmark = varLesezeichen
Debug.Print rst!Artikelname
…
End Sub
Listing 1.24: Hin- und herspringen mit Bookmarks
1.5 Datensätze bearbeiten
Daten lassen sich mit ADO auf verschiedene Art manipulieren. Nachfolgend erfahren
Sie, wie Sie Daten mit Aktionsabfragen und mit den Methoden des Recordset-Objekts
bearbeiten.
1.5.1 Datensatz anlegen
Zum Anlegen eines neuen Datensatzes verwenden Sie AddNew- und die UpdateMethode. Dazwischen stellen Sie die Felder der Datensatzgruppe auf die gewünschten
Werte ein.
Public Sub DatensatzAnlegen()
…
rst.Open "tblUnternehmen", cnn, adOpenKeyset, adLockOptimistic
rst.AddNew
rst!Unternehmen = "Pearson Education Deutschland GmbH"
rst.Update
…
End Sub
Listing 1.25: Neuen Datensatz anlegen
Sie brauchen im Unterschied zu DAO die Update-Methode nicht auszuführen, wenn Sie
den Datensatz wechseln, bevor Sie das Recordset-Objekt schließen. Wenn Sie nach dem
An­legen beispielsweise sofort noch einen weiteren Datensatz anlegen möchten, brauchen Sie die Update-Anweisung nur nach dem Anlegen des zweiten Datensatzes und vor
dem Schließen der Datensatzgruppe aufzurufen:
rst.AddNew
rst!Unternehmen = "Pearson Education Deutschland GmbH"
rst.AddNew
1003
Zusatzkapitel ADO
rst!Unternehmen = "amisoft"
rst.Update
Wenn Sie mit AddNew das Anlegen eines neuen Datensatzes starten und das RecordsetOb­jekt schließen, bevor Sie die Update-Methode ausgeführt haben, lösen Sie einen Lauf­
zeit­fehler aus:
rst.AddNew
rst!Unternehmen = "Pearson Education Deutschland GmbH"
'Schließen ohne Update löst Laufzeitfehler aus
rst.Close
1.5.2 Datensatz bearbeiten
Während Sie unter DAO vor dem Bearbeiten eines Datensatzes die Edit-Methode aufrufen mussten, können Sie unter ADO Änderungen am aktuellen Datensatz direkt vornehmen. Die Änderungen übernehmen Sie schließlich mit Update.
Public Sub DatensatzAendern()
…
rst.Open "tblUnternehmen", cnn, adOpenKeyset, adLockOptimistic
rst.Find "Unternehmen = 'Pearson Education Deutschland GmbH'"
rst!Unternehmen = "Addison Wesley"
rst.Update
…
End Sub
Listing 1.26: Datensatz ändern
1.5.3 Datensatz löschen
Zum Löschen eines Datensatzes verschieben Sie den Datensatzzeiger auf den zu löschenden Datensatz und entfernen diesen mit der Delete-Methode.
Public Sub DatensatzLoeschen()
…
rst.Open "tblUnternehmen", cnn, adOpenKeyset, adLockOptimistic
rst.Find "Unternehmen = 'Pearson Education Deutschland GmbH'"
rst.Delete
…
End Sub
Listing 1.27: Löschen eines Datensatzes
1004
Transaktionen
1.5.4 Aktionsabfragen ausführen
Aktionsabfragen führen Sie unter ADO mit der Execute-Methode des Connection-Objekts
aus. Diese Methode erwartet als ersten Parameter den auszuführenden SQL-Ausdruck.
Als zweiten Parameter können Sie eine Variable angeben, in der die Anzahl der durch
die Aktionsabfrage betroffenen Datensätze gespeichert wird. Diese können Sie nachher
weiter verwenden.
Public Sub AktionsabfrageAusfuehren()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim lngRecordsAffected
Set cnn = CurrentProject.Connection
cnn.Execute "INSERT INTO tblUnternehmen(Unternehmen) " _
& "VALUES('Addison-Wesley')", lngRecordsAffected
Debug.Print lngRecordsAffected
Set cnn = Nothing
End Sub
Listing 1.28: Ausführen einer Aktionsabfrage
1.6 Transaktionen
Transaktionen funktionieren unter ADO prinzipiell wie unter DAO. Der wichtigste
Unterschied ist, dass die Bezeichnungen der drei Methoden zum Durchführen von
Transaktionen vereinheitlicht wurden. Diese heißen jetzt:
»» BeginTrans
»» CommitTrans
»» RollbackTrans
Außerdem gehören die Methoden zum Connection-Objekt – unter DAO war es das Work­
space-Objekt. Weitere Informationen zu Transaktionen finden Sie in »Transaktionen« auf
Seite 553.
1.7 Besonderheiten von ADO gegenüber DAO
ADO bietet einige Besonderheiten gegenüber DAO. So ist es möglich, eine Da­ten­satz­
grup­pe zu speichern, Recordset-Objekte ohne Datenherkunft in Form einer Tabelle oder
1005
Zusatzkapitel ADO
Ab­frage zu verwenden oder Recordsets von der Datenherkunft zu trennen und anschließend wieder zu verbinden.
1.7.1 Datensatzgruppe speichern
Sie können eine Datensatzgruppe in einem Microsoft-eigenen Format oder im XMLFor­mat speichern, solange diese keine Anlagen oder mehrwertigen Felder enthält. Die
folgende Routine speichert den Inhalt der Tabelle Personal in der Da­tei Personal.xml im
Verzeichnis der Datenbank.
Public Sub DatensatzgruppeSpeichern()
…
rst.Open "Personal", cnn, adOpenStatic, adLockOptimistic
rst.Save CurrentProject.Path & "\Personal.xml", adPersistXML
…
End Sub
Listing 1.29: Speichern einer Datensatzgruppe
1.7.2 Datensatzgruppe laden
Um die so gespeicherte Datensatzgruppe wieder verfügbar zu machen, verwenden Sie
die Open-Methode des Recordset-Objekts. Allerdings geben Sie statt eines Tabellenoder Abfragenamens den Namen der Datei an.
Public Sub DatensatzgruppeEinlesen()
Dim rst As New ADODB.Recordset
rst.Open CurrentProject.Path & "\Personal.xml", , adOpenStatic, _
adLockOptimistic, adCmdFile
Debug.Print rst.RecordCount
Set rst = Nothing
End Sub
Listing 1.30: Einlesen einer Datensatzgruppe aus einer XML-Datei
1.7.3 Ungebundene Recordsets/temporäre Datensatzgruppen
verwenden
Unter ADO lassen sich Recordsets ohne Angabe einer Datenherkunft anlegen und zum
Speichern von Daten verwenden – also ohne ein Connection-Objekt. Da ein ungebundenes Recordset keine Datenherkunft hat, besitzt es natürlich auch noch keine Felder.
Diese können Sie ganz einfach mit der Append-Methode der Fields-Auflistung des
Recordset-Objekts hinzufügen.
1006
Besonderheiten von ADO gegenüber DAO
Anschließend können Sie das Recordset ganz normal verwenden. Ein ungebundenes Recordset ist beispielsweise sehr nützlich, wenn Sie größere Datenmengen in
Kombinationsfeldern, Listenfeldern oder sogar Datenblättern anzeigen möchten, diese
aber nicht in einer Tabelle gespeichert werden sollen.
Die Routine aus dem folgenden Beispiel legt eine Datensatzgruppe mit den beiden
Feldern ModulID und Modulname an und fügt alle Module der aktuellen Datenbank hinzu.
Anschließend gibt sie alle Einträge der Tabelle im Direktfenster aus.
Public Sub UngebundeneDatensatzgruppe()
Dim rst As ADODB.Recordset
Dim objModul As AccessObject
Dim i As Integer
Set rst = New ADODB.Recordset
rst.Fields.Append "ModulID", adInteger
rst.Fields.Append "Modulname", adVarWChar, 255
rst.Open
For Each objModul In CurrentProject.AllModules
rst.AddNew
rst!ModulID = i
rst!Modulname = objModul.Name
rst.Update
Next objModul
rst.Update
rst.MoveFirst
Do While Not rst.EOF
Debug.Print rst!ModulID, rst!Modulname
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub
Listing 1.31: Anlegen einer ungebundenen Datensatzgruppe
1.7.4 Disconnected Recordsets
Ein Vorteil von ADO gegenüber DAO ist, dass man ADO-Recordsets auf Basis einer
Tabelle erstellen und diese dann »disconnecten« kann – was nichts anderes heißt, als
dass man ihre Eigenschaft ActiveConnection auf den Wert Nothing setzt.
Die Verbindung zur Tabelle ist damit unterbrochen, Änderungen am Recordset werden
nicht direkt in die Tabelle übertragen.
1007
Zusatzkapitel ADO
Wichtig ist bei dieser Technik, dass Sie das Recordset-Objekt so deklarieren, dass es
auch nach dem Einlesen der Daten noch verfügbar ist – also nicht innerhalb der Routine,
die das Recordset füllt. Am einfachsten erledigen Sie dies mit einer globalen Variablen,
die Sie in einem Standardmodul deklarieren:
Public rstDisconnected As ADODB.Recordset
Es gibt im Wesentlichen zwei Einsatzgebiete für Disconnected Recordsets: nur lesende
sowie schreibende und lesende. Bei den nur lesenden Einsätzen gilt es, oft benötigte
und nicht zu ändernde Daten nur einmal einzulesen und immer wieder auf diese kopierten Werte zuzugreifen. Interessant ist dies vor allem, wenn die Daten über das Netzwerk
oder gar das Internet beschafft werden müssen.
Beispiele für solche Einsatzmöglichkeiten sind Konfigurationsdaten, Texte für Mel­
dungs­fenster oder Fehlermeldungen oder VBA- oder SQL-Ausdrücke wie Funk­tio­nen
und Abfragen.
Der zweite Grund für den Einsatz von Disconnected Recordsets sind Daten, die man
vor einer (zwangsweisen) Trennung von der Datenherkunft einliest, bearbeitet und anschließend wieder speichert.
Da Verbindungen teuer sind, was die Performance angeht, kann es durchaus Sinn machen, diese bei Bedarf zu trennen, um Ressourcen freizugeben.
Disconnected Recordset zum Lesen öffnen
Das erste Beispiel zeigt das Einlesen von Daten, auf die nur lesender Zugriff erfolgt:
Public rstDisconnectedRead As ADODB.Recordset
Public Sub DisconnectedRecordsetsRead()
Set rstDisconnectedRead = New ADODB.Recordset
With rstDisconnectedRead
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Source = "SELECT * FROM tblMitarbeiter"
.Open , , , , adCmdText
If Not rstDisconnectedRead Is Nothing Then
Set .ActiveConnection = Nothing
End If
End With
End Sub
Listing 1.32: Einlesen von Daten in ein Disconnected Recordset
1008
Besonderheiten von ADO gegenüber DAO
Aus performancetechnischen Gründen können Sie hier den CursorType auf adOpenStatic
und den LockType auf adLockReadOnly einstellen.
Der Zugriff auf die im Recordset rstDisconnectedRead enthaltenen Daten erfolgt genau
wie zu Beginn dieses Kapitels beschrieben.
Disconnected Recordset einlesen, ändern und zurückschreiben
Das zweite Beispiel zeigt, wie Sie ein Recordset öffnen, es von der Datenherkunft trennen, den Inhalt ändern und das Recordset wieder in die ursprüngliche Tabelle zurückschreiben.
Mit der folgenden einfachen Routine füllen Sie das Recordset und trennen es von der
Datenherkunft:
Public Sub DisconnectedRecordsets()
Set rstDisconnected = New ADODB.Recordset
With rstDisconnected
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Source = "SELECT * FROM tblMitarbeiter"
.Open , , , , adCmdText
If Not rstDisconnected Is Nothing Then
Set .ActiveConnection = Nothing
End If
End With
End Sub
Listing 1.33: Füllen und »disconnecten« eines Recordset-Objekts
Das Recordset ist nun »disconnected«; das heißt, dass Sie unabhängig von der Da­ten­
her­kunft Änderungen darin vornehmen können und diese nicht direkt dort übernommen
werden. Eine einfache Änderung nehmen Sie mit folgender Routine vor:
Public Sub DisconnectedRecordsetUpdaten()
If Not rstDisconnected Is Nothing Then
With rstDisconnected
.Find "Nachname = 'Minhorst'"
If Not .EOF = True Then
!Vorname = "Andree"
.Update
End If
End With
1009
Zusatzkapitel ADO
End If
End Sub
Listing 1.34: Ändern des Inhalts eines Disconnected Recordsets
Schauen Sie in der zugrunde liegenden Tabelle nach – der im Recordset geänderte Da­ten­satz enthält immer noch denselben Wert wie zuvor. Das ändern Sie nun, indem Sie das Re­­cordset wieder »connecten«. Wichtig ist dabei der Aufruf der Methode
UpdateBatch zum Schreiben der Änderungen in die Tabelle – damit speichern Sie alle
Änderungen an den enthaltenen Datensätzen:
Public Sub DisconnectedRecordsetWiederVerbinden()
If Not rstDisconnected Is Nothing Then
With rstDisconnected
.ActiveConnection = CurrentProject.Connection
.UpdateBatch
End With
End If
rstDisconnected.Close
Set rstDisconnected = Nothing
End Sub
Listing 1.35: Verbinden des Disconnected Recordsets mit der zugrunde liegenden Datenbank und
durchführen der Änderungen
1.7.5 Ereignisse von Datensatzgruppen
Ein sehr interessantes Feature von ADO sind die Ereignisse der einzelnen Objekte wie
Connection- oder Recordset-Objekt (siehe Abbildung 1.6). Diese sind gerade in Zu­sam­
men­hang mit der Programmierung mehrschichtiger Anwendungen interessant.
1010
Besonderheiten von ADO gegenüber DAO
Abbildung 1.6: Ereigniseigenschaften von Recordset-Objekten
Sie können die Ereignisse des Connection- und des Recordset-Objekts in einer eigenen
Klas­se kapseln und entsprechende Prozeduren hinzufügen, die durch die jeweiligen Er­
eignisse ausgelöst werden.
Die komplette Kapselklasse soll hier nicht abgedruckt werden, Sie finden diese aber in
der Beispieldatenbank ADO.accdb im Klassenmodul clsADORS.
Die folgende Routine instanziert diese Klasse und löst durch einige Datensatzoperationen
verschiedene Ereignisse des Connection- und des Recordset-Objekts aus.
Public Sub TestADOEvents()
Dim cADO As New clsADORS
cADO.SetSQL "SELECT * FROM Personal"
DoEvents
cADO.MoveRS 2
DoEvents
cADO.SetSQL "SELECT * FROM Lieferanten"
DoEvents
cADO.MoveRS 7
DoEvents
Set cADO = Nothing
End Sub
Listing 1.36: Instanzieren und Verwenden einer Klasse, die Connection- und Recordset-Ereig­
nisse auslöst
Dies jedoch nur als Hinweis darauf, dass ADO durchaus Eigenschaften besitzt, die es von
DAO positiv abheben.
1011
Zusatzkapitel ADO
In der Beispieldatenbank ADOEvents.accdb finden Sie ein an ein ADO-Recordset gebundenes Formular, das die bei der Arbeit mit dem Recordset im Formular zusätzlich verfügbaren Ereignisse des Con­nec­tion- und des Recordset-Objekts erkennen lässt.
BEISPIELDATENBANK
Die Beispieldateien zu diesem Abschnitt finden Sie unter dem Namen ADOEvents.
accdb auf www.acciu.de/aeb2010.
1012