Der große ADO-Kurs - Seite 4
von Florian Reischl
Properties der Connection
Eine vielleicht etwas ausführlichere aber dafür wesentlich übersichtlicher und auch leichter veränderbare Methode zur Übergabe der Connection-Informationen liegt darin die Properties-Collection zu nutzen. Alle Parameter des ConnectionStrings sind ebenfalls in dieser vorhanden.
Aufruf ist:
Cn.Properties("PropertyName") = "PropertyWert"
Hier ein paar der wichtigsten Properties mit Erläuterung. Es sind nur ein Bruchteil der Properties (beim SQL-Server sind es 93 Properties!):
3.1 Data Source
Diese Property findet man eigentlich bei fast allen DB-Treibern wieder. Somit hat sie auch dementsprechend verschiedene Hintergründe. Bei Access beschreibt der DataSource den Pfad zu der physischen MDB-Datei. Beim MS-SQL-Server beschreibt der DataSource den Namen des Servers (nicht die Datenbank!). Bei Informix z.B. beschreibt diese Eigenschaft wieder den Server und den Namen der Datenbank. Hier zwei Beispiele:
Für Access:
Cn.Properties("Data Source") = "F:\Test_DBs\Access\db3.mdb"
Für SQL-Server:
Cn.Properties("Data Source") = "FLO_MAIN"
3.2 Initial Catalog
Diese Eigenschaft wird vom OLEDB-Treiber des SQL-Servers bestimmt. Diese Eigenschaft gibt den Namen der Datenbank auf welche zugegriffen werden soll an.
3.3 Integrated Security
Ebenfalls eine Eigenschaft des SQL-Servers. Sie gibt an ob eine User-ID und ein Passwort von Client übergeben werden müssen oder ob der SQL-Server auf die NT-Authentifikation zurückgreift. Soll von ID und Passwort abgesehen werden muß hier "SSPI" übergeben werden:
Option Explicit Dim Cn As ADODB.Connection Private Sub Form_Load() Set Cn = New ADODB.Connection Cn.Provider = "SQLOLEDB.1" Cn.Properties("Data Source") = "FLO_Main" Cn.Properties("Initial Catalog") = "db_Test" Cn.Properties("Integrated Security") = "SSPI" Cn.Open End Sub
3.4 Jet OLEDB:Database Password
Datenbankpasswort einer Accessdatenbank. Dieses darf nicht mit dem Userpasswort aus den Logininformationen einer MDW-Sicherheitsdatei verwechselt werden.
3.5 Jet OLEDB:System database
Pfad zu der System-MDW (Sicherheitdatei).
3.6 User ID
User-ID des anzumeldenden Users.
3.7 Password
Passwort der anzumeldenden Users.
3.8 Persist Security Info
Diese Eigenschaft gibt an, ob nach dem Aufbau der Connection zur Datenbank die Userinformationen aus dem ConnectionString und den Connection-Properties gelöscht werden sollen oder gehalten werden sollen. Die Default-Einstellung ist unverständlicher Weise True (behalten). Dieser Wert sollte also explizit auf False gesetzt werden.
3.9 Prompt
Prompt ist eine sehr feine Sache die ADO bietet. Hierbei spart man sich das Erstellen eines Anmeldeformulars für den Login in der Datenbank. Prompt stellt, je nach Einstellung ein Standardanmeldefenster für die Datenbank dar. Je nach Einstellung kann entschieden werden ob das Fenster immer gezeigt wird oder nur wenn Anmeldedaten fehlen. Auch die Menge der einzugebenden Daten kann angegeben werden, wobei die ausführliche Anzeige eigentlich nur für Datenbankreporter oder ähnliche Software sinnvoll ist, da hier zusätzlich zum Servernamen und den Logindaten auch noch die Datenbank, die Sprache u. a. gewählt werden kann. Man kann z.B. über SaveSettings/GetSettings die UserId speichern und beim nächsten Programmstart wieder vor der Anzeige setzen, so daß der User nur noch sein Passwort eingeben muß.
Beim Einsatz mit Jet-DBs (Access) ist diese Eigenschaft etwas mit Vorsicht zu genießen, weil der User hier den Mode (siehe nächster Punkt) angeben kann und somit evtl. anders auf die DB zugreifen kann als es eigentlich gewünscht ist. Dies kann jedoch durch entsprechende Einstellungen in der System-MDW verhindert werden.
Die verschiedenen Möglichkeiten für die Prompts werde durch das ConnectPromptEnum definiert.
ConnectPromptEnum
Konstante | Beschreibung |
---|---|
adPromptAlways | Das Fenster wird auf jeden Fall gezeigt (auch wenn die Anmeldedaten eigentlich komplett sein könnten). |
adPromptComplete | Das Fenster wird angezeigt wenn nicht alle Daten vorhanden sind. Es sind alle Optionen möglich. |
adPromptCompleteRequired | Das Fenster wird gezeigt wenn nicht alle Daten vorhanden sind. Es können nur User-Id, Passwort und Servername (SQL-Server) angegeben werden. |
adPromptNever | Erlaubt im MultiUser-Bereich das Öffnen anderer Verbindungen ohne Beschränkungen (Lesen/Schreiben/Beides). |
Beispiel für Access:
Private Sub Form_Load() Dim promp As ConnectPromptEnum Dim sUserId As String ' Prompt soll zur Verfollständigung aber nur mit ' Möglichkeiten für normalen User angezeigt werden promp = adPromptCompleteRequired ' Bestehenden Wert aus der Registry auslesen, ' wenn kein Wert vorhanden dann NT-UserId auslesen sUserId = GetSetting(App.Title, "Main", "UserId", _ Environ$("username")) With CnT .CursorLocation = adUseClient .Mode = adModeShareDenyNone .Provider = "Microsoft.Jet.OLEDB.4.0" .Properties("Data Source") = "F:\Test_DBs\Access\db3.mdb" .Properties("Jet OLEDB:Database Password") = "test" .Properties("Jet OLEDB:System database") = _ "F:\Test_DBs\Access\db3.mdw" .Properties("User ID") = sUserId ' UserId übergeben .Properties("Prompt") = promp ' Prompt-Mode übergeben .Open sUserId = .Properties("User Id").Value End With ' UserId für nächsten Programmstart speichern SaveSetting App.Title, "Main", "UserId", sUserId End Sub
Beispiel für SQL-Server:
Private Sub Form_Load() Dim promp As ConnectPromptEnum Dim sUserId As String ' Prompt soll zur Verfollständigung angezeigt werden. ' Es können mehrere Einstellungen vorgenommen werden. promp = adPromptComplete ' Bestehenden Wert aus der Registry auslesen, ' wenn kein Wert vorhanden dann NT-UserId auslesen sUserId = GetSetting(App.Title, "Main", "UserId", _ Environ$("username")) With CnT .Provider = "SQLOLEDB.1" .Properties("Data Source") = "FLO_MAIN" .Properties("Initial Catalog") = "db_Test" .Properties("User ID") = sUserId .Properties("Prompt") = promp .Open End With ' UserId für nächsten Programmstart speichern SaveSetting App.Title, "Main", "UserId", sUserId End Sub
3.10 Beispiel für Properties
Um mal ein paar der beschriebenen Properties einzusetzen hier die Mammut-Routine um eine Access-Datenbank mit Datenbankpasswort und Sicherheitsdatei zu öffnen:
With Cn .Provider = "Microsoft.Jet.OLEDB.4.0" .Properties("Data Source") = "F:\Test_DBs\Access\db3.mdb" .Properties("Jet OLEDB:Database Password") = "test" .Properties("Jet OLEDB:System database") = _ "F:\Test_DBs\Access\db3.mdw" .Properties("User ID") = "florianr" .Properties("Password") = "flo" .Open End With
3.11 Mode
Diese Eigenschaft ist mir bislang nur von Jet-Datenbanken geläufig. Sie gibt prinzipiell an wie der Zugriff der Anwendung auf die DB ist und wie sich die DB im Multiuserbetrieb verhält. Beim Einsatz dieser Eigenschaft beim SQL-Server wird ein Fehler ausgelöst.
ConnectModeEnum
Konstante | Beschreibung |
---|---|
adModeRead | Nur Lesen |
adModeReadWrite | Lesen und Schreiben |
adModeRecursive | Wird verwendet in Kombination mit anderen MultiUser-Eigenschaften (ShareDeny*) die Beschränkungen werden dann auch auf Sub-Records (abgeleitete Datensätze) übergeben. Wenn keine Sub/Child-Records existieren besteht keine Funktionalität. |
adModeShareDenyNone | Erlaubt im MultiUser-Bereich das Öffnen anderer Verbindungen ohne Beschränkungen (Lesen/Schreiben/Beides). |
adModeShareDenyRead | Verbietet anderen Connections einen Lesezugriff. |
adModeShareDenyWrite | Verbietet anderen Connections einen Schreibzugriff. |
adModeShareExclusive | Verbietet das Öffnen anderer Connections grundsätzlich. |
adModeUnknown | Keine Beschränkungen (= Default). |
adModeWrite | Nur Schreiben. |
3.12 Execute
Diese Methode kann genutzt werden um SQL-Statements abzuschicken. Rückgabewert ist ein ReadOnly-Recordset (wird weiter unten noch beschrieben).
Beispiel zum abschicken eines SQL-Statements:
Cn.Execute "INSERT INTO tblName (FeldName) VALUES ('Hallo')"
3.13 BeginTrans, RollbackTrans, CommitTrans
Diese Methoden sollten denjenigen die bereits den Umgang mit dem SQL-Server kennen bereits bekannt vorkommen.
Sie sind vor allem im Umgang mit Errorhandling sehr praktisch um eventuell halbe Einträge in Datenbanken zu vermeiden.
BeginTrans dient zum Festlegen des Beginns einer SQL-Transaktion. Mit CommitTrans wird die Transaktion vollendet, wenn jedoch während der Transaktion ein Fehler auftritt können mit der Methode RollbackTrans die bereits durchgeführten Transaktionen rückgängig gemacht werden. Hier ein kleines Beispiel:
Private Sub Command1_Click() On Error Goto ErrSQL Cn.BeginTrans Cn.Execute "DELETE FROM tblName WHERE Spalte1 = 'Hallo'" Cn.CommitTrans ErrSQL: Cn.RollbackTrans End Sub
3.14 CommandTimeout
Gibt an innerhalb welcher Zeit in Sekunden eine Transaktion ausgeführt sein muß bevor diese von der Connection abgebrochen wird. Default ist 30 Sekunden.
3.15 CursorLocation
Gibt an wo die Daten gehalten werden sollen wenn man eine Datenabfrage auf die Datenbank ausgeführt wird. Es gibt hier zwei Möglichkeiten:
CursorTypeEnum
Konstante | Beschreibung |
---|---|
adUseClient | Bedeutet, daß die Daten komplett auf den Client gezogen werden sollen. Dies bringt jedoch einen eventuell großen Speicherbedarf auf dem Client. |
adUseServer | Die Daten werden nicht komplett auf den Client gezogen, sondern nur diese die vom Client aktuell benötigt werden. Das spart viel Speicherbedarf auf dem Client. Nachteil ist jedoch, daß bei manchen Datenanzeige-Controlls wie z.B. DataGrid oder MSHFlexGrid die Daten mit einem serverseitigen Cursor keine Daten angezeigt werden, bzw. ein paar Funktionen nicht mehr verfügbar sind. |
3.16 Errors
Im Gegensatz zu VB können bei ADO und SQL durch eine einzige Methode mehrere Fehler und Meldungen vom Server an den Client geschickt werden. Aus diesem Grund ist Errors bei ADO kein einzelnes Objekt sondern eine Collection.
3.17 State
Gibt den aktuellen Status der Connection an. Die wichtigsten für den normalen Umgang mit ADO sind adStateOpen für Connection ist offen und adStateClosed für Connection ist geschlossen.
3.18 Cancel
Diese Methode ist für das Abbrechen einer asynchronen Abfrage oder SQL-Action gedacht. Hierauf wird aber erst später eingegangen.
3.19 OpenSchema
Hierbei handelt es sich um eine sehr schöne Methode ohne den Einsatz von zusätzlichen Datenbankverwaltungs-DLLs (ADOX, SQLDMO, …) einen Einblick in die Objektstruktur einer Datenbank zu erhalten. Hiermit lassen sich unter anderem alle Tabellen, Views oder StoredProcedures einer Datenbank auflisten, aber auch Indizes, Keys und Relationen und noch vieles mehr. Gerade für Access ist diese Methode sehr angenehm, lassen sich auch wegen der kleinen Komplexität von Access nicht alle der Möglichkeiten einsetzen. Auf dem SQL-Server sind viele dieser Funktionen durch das Auslesen der Systemtabellen und -views nachzubilden, aber praktisch bleiben sie trotzdem, auch hier sind lange nicht alle Konstanten verwendbar und leider z.T. auch nicht von Microsoft dokumentiert, somit kann ich hierzu keinerlei Aussagen machen.
Auf alle möglichen Parameter möchte ich jetzt nicht eingehen da dies den Rahmen sprengen würde, aber die wichtigsten. Die Parameter welche bestimmen welche Objekte ausgelesen werden sollen werden durch das SchemaEnum definiert (welches ganze 41 Konstanten fast!).
Hier ein Auszug aus dem erwähnten Enum.
BookmarkEnum
Konstante | Beschreibung |
---|---|
adSchemaCatalogs | Auslesen aller Datenbanken eines Servers. Da Access kein Server ist steht diese Funktion natürlich nicht zur Verfügung. |
adSchemaColumns | Auslesen aller Spalten |
adSchemaDBInfoLiterals | Gesetzmäßigkeiten für Benamsungen der Datenbankobjekte. |
adSchemaForeignKeys | Alle Fremdschlüssel der Datenbank auflisten. |
adSchemaIndexes | Alle Indizes der Datenbank auflisten. |
adSchemaPrimaryKeys | Alle Primärschlüssel einer Datenbank auflisten. |
adSchemaProcedureParameters | Alle Parameter der StoredProcedures auslesen. Bei Access nicht verfügbar. |
adSchemaProcedures | Alle StoredProcedures einer Datenbank auflisten. Bei Access werden auch Formular-Datengrundlagen ausgegeben. (Durch den Anfangsbuchstaben „~“ gekennzeichnet.) |
adSchemaProviderTypes | Alle möglichen Felddatentypen und deren Beschaffenheit auslesen. |
adSchemaStatistics | Derzeit aktuelle Anzahl der Zeilen aller Tabellenobjekte. |
adSchemaTableConstraints | Primary- und ForeignKeys auflisten. |
adSchemaTables | Alle Tabellen (und Views) einer Datenbank auflisten. |
adSchemaViews | Alle Views einer Datenbank auflisten. Scheinbar nicht für SQL-Server verfügbar, kann jedoch leicht über die adSchemaTables-Auflistung gemacht werden. |
Der Parameter Restrictions ist nie Pflicht, er kann gesetzt werden um einen Filter vorzudefinieren. Auf dem SQL-Server können hiermit auch Schemata anderer Datenbanken abgefragt werden. Hierbei wird ein Array mit den gewünschten Gegebenheiten und Beschränkungen übergeben bei dem leider kein 100%iges Schema erkennbar ist. Die Filterfunktion kann natürlich auch über das Recordset behandelt werden.
Der dritte Parameter ist nur nötig wenn providerspezifische Schemata abgefragt werden sollen. In diesem Fall muß die GUID des Schemas übergeben werden. Hierauf wird nicht näher eingegangen.
Hier ein paar Beispiele für den Gebrauch, die Daten werden hierbei direkt in ein DataGrid als Recordset (wird später beschrieben) übergeben.
Alle Tabellen (und Views/Auswahlabfragen) auflisten:
Set DataGrid1.DataSource = CnT.OpenSchema(adSchemaTables)
Tabellen des Datenbankbesitzers („dbo“) einer anderen Datenbank auflisten (SQL-Server):
Set DataGrid1.DataSource = CnT.OpenSchema(adSchemaTables, _
Array("DbName", "dbo"))
Alle Spalten einer Tabelle auflisten:
Set DataGrid1.DataSource = CnT.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "TabellenName"))
Alle Datenbanken eines Servers (SQL-Server) auflisten:
Set DataGrid1.DataSource = CnT.OpenSchema(adSchemaCatalogs)
Alle Indizes einer Tabelle auflisten:
Set DataGrid1.DataSource = Cn.OpenSchema(adSchemaIndexes, _
Array(Empty, Empty, Empty, _
Empty, "TabellenName"))
3.20 Beispiel für Connection
Um einige der genannten Eigenschaften und Methoden zu verdeutlichen, hier ein kleines Beispiel für das Verbinden auf einen SQL-Server und abschicken eines kleinen SQL-Statements:
Option Explicit Dim Cn As ADODB.Connection Private Sub Form_Load() Dim i As Integer Dim sMsg As String On Error Goto ErrHandle Set Cn = New ADODB.Connection With Cn ' Daten auf den Cursor ziehen .CursorLocation = adUseClient ' Datenbanktreiber .Provider = "SQLOLEDB.1" ' Servername .Properties("Data Source") = "FLO_MAIN" ' Datenbankname .Properties("Initial Catalog") = "db_Test" ' User-ID .Properties("User ID") = "florianr" ' Userpasswort .Properties("Password") = "flo" ' ' NT-Authentifikation ' .Properties("Integrated Security") = "SSPI" .Open .BeginTrans .Execute "DELETE FROM tblName WHERE FeldName = 'Hallo'" .CommitTrans End With Exit Sub ErrHandle: sMsg = "Es sind Fehler aufgetreten: " & vbCrLf & vbCrLf With Cn .RollbackTrans For i = 0 To .Errors.Count - 1 sMsg = sMsg & .Errors(i).Description & vbCrLf Next i .Errors.Clear End With MsgBox sMsg, vbCritical, "Datenbankfehler" End Sub