Per ODBC auf Excel zugreifen

15.12.2006 von André Minhorst
Immer noch nutzen viele Anwender Excel als preiswerten Datenbankersatz – nicht immer freiwillig, sondern oft auch, weil sich die Firma dadurch massenweise Access-Lizenzen einspart. Sollen diese Daten dann zentral in einer Access- Anwendung weiterverarbeitet oder abgeglichen werden, ist das ein Problem. Eine Lösung ist die Datenbankverbindung per ODBC oder OleDB-Provider, aber auch sie hat ihre Einschränkungen und Tücken.

Der Zugriff auf Excel-Daten über eine Datenbankverbindung erfordert, dass die Excel-Daten ähnlich wie in einer Datenbank angeordnet sind. Nicht jedes Tabellenblatt ist daher für den Datenzugriff geeignet.

Anforderung an die Excel-Daten

Damit Sie auf Excel-Daten mittels ODBC zugreifen können, benötigen Sie die Daten mit einem tabellarischen Aufbau, bei dem die Datensätze in Zeilen und die Felder in Spalten angeordnet sind. Die Daten müssen also prinzipiell wie in Access-Tabellen vorliegen.

Allerdings gibt es in Excel natürlich keine Feldnamen, sodass Sie auf andere Weise dafür sorgen müssen, dass die Spalten einen Namen bekommen, den Sie dann auch in Abfragen verwenden können. Dazu müssen Sie in die erste Zeile derm Tabellenblätter die Spaltenüberschriften einfügen, die dann als Feldnamen verwendet werden. Achten Sie daher darauf, dass die erste Zeile nurgültige Zeichen in den Feldern enthält. Leerzeichen sind zulässig, machen aber doch Probleme, Sie sollten daher möglichst keine Leerzeichen verwenden. Diese erste Zeile sollten Sie außerdem mit einer besonderen Formatierung versehen, z.B. indem Sie die Zellen fett formatieren. In der zweiten Zeile und den weiteren Zeilen sollten die Daten stehen. Wenn Sie den Zellbereich mit den Daten nicht benennen möchten, sollten Sie darauf achten, dass es keine Leerzeilen gibt und die Daten lückenlos untereinander stehen.

Der Name des Tabellenblatts entspricht den Tabellennamen von Access. Ihn benötigen Sie zum Zugriff auf das Tabellenblatt in den SQL-Anweisungen. Sie sollten also wie in Access auch einen sinnvollen Namen für das Tabellenblatt definieren (Bild 1).

Bild 1: Aufbau der Excel-Tabelle.

Wo Sie die Arbeitsmappe speichern, spielt eigentlich keine Rolle, da Sie den Pfad beim erbindungsaufbau angeben müssen. Die Beispielanwendung geht jedoch davon aus, dass die Datei sich im gleichen Verzeichnis wie die Datenbank befindet.

Eingeschränkte Nutzung

Zwar funktioniert der nachfolgend vorgestellte Code grundsätzlich unter Access 2000 und höher, allerdings nicht mehr, wenn für Office XP Service-Pack 3 beziehungsweise für Office 2003 Service Pack 2 oder ein Update für Access installiert wurde, das nach diesen Service-Packs erschienen ist.
Der Grund liegt darin, dass Microsoft aus rechtlichen Gründen die Aktualisierung von Excel-Daten per ODBC aus Access 2002/2003 deaktiviert hat. Dies gilt nicht nur für die hier vorgestellte VBA-Lösung, sondern auch für normale verknüpfte ODBC-Excel-Tabellen innerhalb von Access.

Verbindungsaufbau

Die Datenbankverbindung per ODBC erfordert die Installation des passenden ODBC-Treibers. Das ist auf jeden Fall gewährleistet, wenn Sie über Windows 2000 oder höher verfügen, weil die Datenbanktreiber mit Windows installiert werden.

Generell gibt es zwei verschiedene Möglichkeiten, eine Datenbankverbindung zu einer ODBC-Datenquelle aufzubauen.

Problematisch ist eine DSN aber grundsätzlich für die Installation einer Anwendung, weil die DSN zunächst manuell oder auch per WSH- oder PowerShell-Skript erstellt werden muss.

MDAC-Bibliothek downloaden

Bei Windows NT, 98 und Me müssen Sie gegebenenfalls noch die aktuelle MDAC-Bibliothek downloaden und installieren. MDAC ist die Abkürzung für Microsoft Data Access Components und bezeichnet eine Sammlung von Bibliotheken und DLLs, die für den Datenbankzugriff auf verschiedene Datenbanken erforderlich sind. Sie finden die aktuelle Version für Ihr Betriebssystem auf der Webseite von Microsoft. Suchen Sie dazu im Downloadbereich nach „MDAC“.

3-mal DSN

Windows kennt drei verschiedene Arten von DSNs, System-, Benutzer- und Datei-DSNs. System-DSNs werden für das System eingerichtet, auf dem sie erstellt werden. Sie stehen damit allen Benutzern zur Verfügung, die sich dort anmelden. Benutzer-DSNs sind nur für den Benutzer gültig, unter dem man sich anmeldet, wenn man die DSN erstellt.

Alternativ können Sie in Ihrer Anwendung auch die Verbindungszeichenfolge direkt verwenden. Dann haben Sie die Möglichkeit, auch Name und Pfad der Datenbank gegebenenfalls auszutauschen, und bei der Installation der Anwendung muss keine DSN generiert werden.

Das Problem bei der Nutzung von Verbindungszeichenfolgen ist, dass deren Erzeugung nicht ganz einfach ist. Jeder Treiber braucht eine andere Verbindungszeichenfolge, und selbst kleine Änderungen können dazu führen, dass die Verbindungszeichenfolge nicht mehr funktioniert. Allerdings ist eine einfache Verbindungszeichenfolge für Excel nicht ganz so kompliziert wie für andere ODBC-Datenquellen.

Mit der Verbindungszeichenfolge können Sie beim Zugriff auf Excel aber auch festlegen, ob Sie den ODBC-Treiber direkt oder den OleDB-Provider nutzen möchten. Beide Möglichkeiten, also der direkte Zugriff per ODBC und der indirekte über OleDB werden nachfolgend gezeigt.

Verbindung zu einer ODBCDatenquelle aufbauen

Möchten Sie eine Verbindung zu einer ODBCDatenquelle aufbauen und benötigen Sie diese Datenquelle mehrmals, ist es sinnvoll, dazu eine Funktion zu erstellen, die die geöffnete Verbindung als OleDB-Connection-Objekt zurückgibt. Die könnte wie in Listing 1 aussehen. Die Funktion Connect1 baut eine Verbindung direkt über den ODBC-Treiber auf, die Funktion Connect2 nutzt den OleDB-Provider, der allerdings im Hintergrund ebenfalls auf den ODBC-Treiber für Excel zugreift.

Function Connect1(Optional strDatei As String = "daten.xls", _
Optional strPfad As String = "") As ADODB.Connection
Dim strVerb As String
'Baut eine Verbindung über den ODBC-Treiber auf.
If strPfad = "" Then
strPfad = getDBPfad()
End If
strVerb = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" _
& strDatei & ";DefaultDir=" & strPfad & ";Readonly=0;"
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.Mode = adModeReadWrite
objConn.ConnectionString = strVerb
On Error GoTo FEHLER
objConn.Open
Set Connect1 = objConn
Exit Function
FEHLER:
MsgBox Err.Number & ": " & Err.Description
Resume Next
End Function
Function Connect2(Optional strDatei As String = "daten.xls", _
Optional strPfad As String = "") As ADODB.Connection
'Baut eine Verbindung über den OLEDB-Provider für ODBC auf.
Dim strVerb As String
If strPfad = "" Then
strPfad = getDBPfad()
End If
strVerb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strPfad & strDatei & _
";Extended Properties=""Excel 8.0;HDR=0;IMEX=1"""
Debug.Print strVerb
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = strVerb
objConn.Mode = adModeReadWrite
On Error GoTo FEHLER
objConn.Open
Set Connect2 = objConn
Exit Function
FEHLER:
MsgBox Err.Number & ": " & Err.Description
Resume Next
End Functio

Die beiden Funktionen unterscheiden sich im Prinzip nur in der verwendeten Verbindungszeichenfolge. Für die ODBC-Verbindung wird folgende Zeichenfolge verwendet, wobei Dateiname und Pfad über die Parameter der Funktion definiert werden:

Driver={Microsoft Excel Driver (*.xls)};DriverId=
790;Dbq=daten.xls;DefaultDir=G:\Artikel\ARTIKEL\A
ccessxcelODBC_1206\bsp\;Readonly=0;

Wichtig ist hier neben der Treiberangabe und dem Pfad und Namen der Datei vor allem die Angabe Readonly=0 am Ende. Sie bewirkt, dass die Verbindung mit Schreibrechten aufgebaut wird. Lassen Sie die Angabe weg, können Sie Daten zwar lesen, aber nicht schreiben.

Für die OleDB-Verbindung wird folgende Verbindung verwendet:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Artikel\
ARTIKEL\AccessxcelODBC_1206\bsp\daten.xls;Extended
Properties="Excel 8.0;HDR=Yes;IMEX=1"

Hier ist neben der Excel-Versionsangabe mit Properties="Excel.8.0…" vor allem die Angabe IMEX=1 wichtig. Sie bestimmt, dass in der ersten Tabellenzeile die Spaltenbeschriftungen stehen. Beide Funktionen geben die geöffnete Datenbankverbindung zurück, sollte die Verbindung nicht geöffnet werden können, wird eine Fehlermeldung angezeigt.

Möchten Sie nun über die Datenbankverbindung Daten abrufen, müssen Sie dazu eine entsprechende SELECT-Anweisung formulieren und diese ausführen, um eine RecordSet-Objekt zurückzugeben.

SELECT-Anweisungen formulieren

Anders als Access, kennt Excel Tabellen nur in Form von Tabellenblättern, die zudem aus weitaus mehr Zeilen und Spalten bestehen, als mit Daten gefüllt sind. In Access ist das anders. Hier gibt es weder undefinierte Spalten noch komplette leere Zeilen in einer Tabelle. Zumindest ist Letzteres die Regel, weil eigentlich jede logisch aufgebaute Datenbank dafür sorgt, dass Datensätze gelöscht und nicht nur geleert werden, wenn die Daten nicht mehr erforderlich sind.

Da es auch leere Spalten und Zeilen gibt, genügt es nicht, die Tabellen anzugeben, aus denen die Daten stammen, sondern Sie sollten auch den Zellbereich bestimmen, in dem tatsächlich Daten gespeichert sind. Grundsätzlich gibt es folgende Möglichkeiten eine SELECT-Abfrage zu definieren:

Immer dann, wenn Sie den Namen des Tabellenblatts als Tabellename verwenden möchten, müssen Sie ihn in Klammern setzen und danach ein $-Zeichen angeben. Die Anweisung SELECT * FROM [tabAdressen$] gibt also alle Datensätze aus dem Tabellenblatt tabAdressen zurück. Bei der Anweisung SELECT ID,Nachname,Vorname, Strasse, PLZ, Ort FROM [tabAdressen$] werden nur bestimmte Spalten in der hier angegebenen Reihenfolge zurückgegeben. Wichtig ist dabei, dass Sie die Spalten so bezeichnen, wie Sie in der ersten Zeile des Tabellenblattes angegeben sind. Berücksichtigt werden in beiden Fällen nur die Zeilen, die nicht leer sind, bei Angabe des * in der Feldliste werden nur die Spalten zurückgegeben, die nicht leer sind.

Anders sieht das aus, wenn Sie wie in der zweiten Alternative einen benannten Bereich anstelle des Blattnamens angeben. Hier heißt der Bereich „Adressliste“ und umfasst die Spalten A bis G des Blatts. Hinter dem Schlüsselwort FROM folgt dann der Name des Zellbereichs.

Nutzen Sie Zellbereiche, können Sie natürlich auch auf einem Blatt mehrere Bereiche definieren und wie separate Tabellen in der SQL-Abfrage verwenden. Der Nachteil ist allerdings, dass beim Einlesen der Daten alle Zeilen und Spalten im Bereich verwendet werden, auch dann, wenn sie leer sind.

Das bedeutet, dass Sie bei Angabe eines Zellbereichs, der komplette Spalten umfasst, unter Umständen zwei Datensätze zurückbekommen und über 16.000 leere Zeilen. Das ist nicht nur unschön, sondern aus Performancegründen auch nicht optimal. Sie können aber natürlich auch über WHERE-Ausdrücke die Anzahl Datensätze begrenzen. In der Beispieltabelle könnte dieser Filter so lauten:

SELECT * FROM Adressliste WHERE ID>0

Der Vergleich ID>0 ist hier deshalb korrekt, weil in der Spalte ID nur numerische Werte stehen, die auch nicht als Text formatiert wurden. Daher werden sie wie numerische Felder einer Datenbank behandelt. Leere Zellen werden in numerischen Spalten als 0 interpretiert.

Alternativ zu benannten Zellbereichen können Sie auch den zu berücksichtigenden Zellbereich nach dem Tabellenblattnamen angeben. Im folgenden Beispiel wird der Zellbereich "A1" bis "G20" verwendet.

SELECT ID, Nachname, Vorname, Strasse, PLZ, Ort FROM [tabAdressen$A1:G20]

Die Daten abrufen

Wenn Sie die Daten abrufen möchten, ist es wiederum sinnvoll, dazu eine Funktion zu erstellen, die das gefüllte Recordset mit den Daten zurückgibt (Listing 2).

Function GetRecordset(objConn As ADODB.Connection, _
Optional strSQL As String = _
"SELECT * FROM [tabAdressen$]") _
As ADODB.Recordset
Dim objRs As ADODB.Recordset
Set objRs = New ADODB.Recordset
On Error GoTo FEHLER
objRs.Open strSQL, objConn, adOpenDynamic, adLockOptimistic
Set GetRecordset = objRs
Exit Function
FEHLER:
Set objConn = Connect1()
objRs.Open strSQL, objConn, adOpenDynamic, adLockOptimistic
Resume Next
End Function

Zellbereich benennen

Wenn Sie benannte Zellbereiche verwenden möchten, müssen Sie den Bereich allerdings vorab in der Excel-Arbeitmappe festlegen. Markieren Sie dazu in der Excel-Arbeitsmappe die Spalten A bis G, oder auch einen anderen Zellbereich, in dem sich die Daten befinden, geben Sie den Namen des Zellbereichs in das Adressfeld ein, und drücken Sie [Enter] (Bild 2).

Bild 2: Zellbereich benennen.

Daten in Formularen anzeigen

Sie können natürlich auch ODBC-Datenquellen mit Access verknüpfen und so normale gebundene Formulare basierend auf den verknüpften Tabellen erstellen. Nicht immer ist das jedoch optimal. Alternativ können Sie eine manuelle Datenbindung erreichen.

Basis dafür ist ein Formular, das für jede Spalte der Excel-Tabelle ein Steuerelement enthält. Das Steuerelement muss den gleichen Namen haben wie das Tabellenfeld. Für die hier verwendete Adresstabelle erstellen Sie also im Detailbereich des Formulars Steuerelemente mit den Namen ID, Nachname, Vorname, Strasse, PLZ und Ort.

Bild 3: Aufbau des Formulars.

Im Fußbereich des Formulars benötigen Sie zwei Schaltflächen bttWeiter und bttVor, für eine einfache Navigation in den Datensätzen (Bild 3). Und dann benötigen Sie natürlich noch den Code, der dafür sorgt, dass die Daten aus der ODBC-Datenquelle angezeigt werden.

Load- und Close-Ereignisprozedur

Als Nächstes müssen Sie für das Formular eine Load- und eine Close-Ereignisprozedur schreiben (Listing 3) sowie zwei Variablen auf Klassenebene deklarieren. Die Variablen benötigen Sie, damit Sie jederzeit auf die geöffnete Verbindung und das gefüllte Recordset Zugriff haben.

Option Compare Database
Public objRs As ADODB.Recordset
Public objConn As ADODB.Connection
Const strPS = "ID" 'Name des Primärschlüsselfeldes
Const strTab = "[tabAdressen$]"
Private Sub Form_Close()
On Error Resume Next
objRs.Close
objConn.Close
End Sub
Private Sub Form_Load()
Set objConn = Connect1()
Set objRs = GetRecordset(objConn, _
"SELECT * FROM " & strTab & " WHERE " & strPS & ">0")
objRs.MoveFirst
DSanzeigen
End Sub
Sub DSanzeigen()
Dim objContr As Control
If (objRs.EOF = True) Or (objRs.BOF = True) Then
Exit Sub
End If
For Each objContr In Me.Detailbereich.Controls
On Error Resume Next
objContr.Value = objRs.Fields(objContr.Name)
Next objContr
Err.Clear
End Sub

In der Load-Ereignisprozedur stellen Sie zunächst die Datenbankverbindung her und rufen dann die Funktion GetRecordset auf, mit der Sie das Recordset zurückgeben. Anschließend wechseln Sie mit MoveFirst zum ersten Datensatz und rufen danach die Prozedur DSanzeigen auf.

Auf Modulebene deklarieren Sie zwei Variablen und zwei Konstanten. Die Konstanten legen den Namen des Primärschlüsselfeldes und der Tabelle fest. Das ist nützlich, dann brauchen Sie nur die Konstanten ändern, wenn Ihr Primärschlüsselfeld oder das Tabellenblatt anders lautet.

Die Prozedur DSAnzeigen zeigt immer den gerade aktiven Datensatz an. Sie müssen also vorher nur den Datensatzzeiger an die gewünschte Stelle bewegen, und schon werden die entsprechenden Daten geladen. Die Prozedur macht dazu nichts weiter, als alle Steuerelemente des Detailbereichs zu durchlaufen und ihnen den Wert aus dem gleichnamigen Tabellenfeld zuzuweisen. Sollte es kein entsprechendes Tabellenfeld geben, weil es sich um ein Labelfeld oder ein Steuerelement handelt, das nicht an ein Feld gebunden werden soll, sorgt die On-Error-Resume-Next-Anweisung dafür, dass kein Laufzeitfehler auftritt. Außerdem sollten Sie vorab prüfen, ob der Datensatzzeiger vor dem ersten oder nach dem letzten Datensatz steht. In diesem Fall verlassen Sie einfach die Prozedur mit Exit Sub.

Damit die Navigation durch die Datensätze gelingt, müssen Sie jetzt noch zwei Click-Ereignisprozeduren für die beiden Buttons im Fußbereich erstellen (Listing 4). In ihnen bewegen Sie nur den Datensatzzeiger rückwärts oder vorwärts und rufen dann wieder die Prozedur DSAnzeigen auf.

Private Sub bttWeiter_Click()
On Error Resume Next
objRS.MoveNext
DSanzeigen
End Sub
Private Sub bttZurueck_Click()
On Error Resume Next
objRS.MovePrevious
DSanzeigen
End Sub

Daten löschen und ändern

Das Anzeigen der Daten klappt jetzt. Allerdings hat der Benutzer noch keine Möglichkeit, die Daten auch zu speichern oder zu löschen. Bevor er einen neuen Datensatz anzeigt, sollte er daher den alten speichern. Außerdem müssen Sie eine Möglichkeit vorsehen, wie der Benutzer den Datensatz löschen kann.

Letzteres ist nicht ganz einfach. Der ODBCTreiber für Excel unterstützt die DELETE-Anweisung nicht. Das bedeutet, Sie können einen Datensatz nicht mit der DELETE-Anweisung aus der Tabelle löschen, sondern müssen stattdessen alle Felder auf ihre Nullwerte setzen und dazu die UPDATE-Anweisung ausführen. Dadurch entstehen allerdings leeren Zeilen im Tabellenblatt. Besser ist daher, Sie setzen die ID auf einen für die Abfragen ungültigen Wert. Numerische IDs können Sie beispielsweise mit -1 multiplizieren und dann nur die Datensätze mit IDs größer als 0 anzeigen. Bei nicht numerischen IDs könnten Sie die ID durch eine Zeichenfolgen #gelöscht# ersetzen. Für das Formular und Ihren Code spielt es keine Rolle, ob ein Datensatz gelöscht oder geändert werden soll. Sie brauchen die UPDATEAnweisung in jedem Fall. Sie müssen also lediglich einen Löschen-Button im Formular ergänzen und für diesen einen Click-Eventhandler (Listing 5) erstellen.

Private Sub bttLoeschen_Click()
DSLoeschen strTab, strPS
DSanzeigen
End Sub

Was jetzt noch fehlt sind natürlich die Prozeduren DSLoeschen und DSSpeichern sowie eine Prozedur zum Erstellen neuer Datensätze. Um die UPDATE-Anweisung zum Speichern der Daten zu erstellen, müssen Sie allerdings berücksichtigen, ob es sich um numerische oder alphanumerische Felder handelt. Dazu sieht die Prozedur einen optionalen Parameter mit einer kommaseparierten Liste vor, die die numerischen Felder der Tabelle enthält. Für diese Felder werden dann die Werte nicht in Hochkomma gesetzt. Um zu prüfen, ob ein Feld in der Liste vorhanden ist, sollten Sie sich eine Funktion erstellen, die die Liste mit der Split-Funktion auftrennt und das Array durchsucht. Im Beispiel dient hierfür die inArray-Funktion (Listing 6).

Function inArray(strFeldliste As String, _
strFeldname As String) As Boolean
Dim boolErg As Boolean
Dim arrTemp As Variant
boolErg = False
arrTemp = Split(strFeldliste, ",")
For Each varWert In arrTemp
If varWert = strFeldname Then
boolErg = True
Exit For
End If
Next varWert
inArray = boolErg
End Function
Sub DSSpeichern(Optional strTabname As String = _
"[tabAdressen$]", Optional strID As String = "ID", _
Optional strFeldliste As String = "PLZ,ID")
Dim objContr As Control
Dim objFeld As ADODB.Field
Dim strSQL As String
Dim varID As Variant
varID = Me.Detailbereich.Controls(strID).Value
On Error GoTo 0
If objRs Is Nothing Then
Set objRs = GetRecordset(objConn, "SELECT * FROM " & _
strTab & " WHERE " & strPS & ">0")
End If
If ((objRs.EOF) Or (objRs.BOF)) Then
Exit Sub
End If
If IsNull(Me.Detailbereich.Controls(strID).Value) Then
Exit Sub
End If
strSQL = "UPDATE " & strTabname & " SET "
For Each objFeld In objRs.Fields
On Error Resume Next
If (objFeld.Name <> strID) And (inArray(strFeldliste, _
objFeld.Name) = False) Then
strSQL = strSQL & objFeld.Name & " = '" & _
Me.Detailbereich.Controls(objFeld.Name).Value & "',"
Else
If (inArray(strFeldliste, objFeld.Name) = True) _
And ((IsNull(Me.Detailbereich.Controls(objFeld.Name).Value))) Then
strSQL = strSQL & objFeld.Name & " = NULL,"
ElseIf (inArray(strFeldliste, objFeld.Name) = True) _
And (Me.Detailbereich.Controls(objFeld.Name).Value = "") Then
strSQL = strSQL & objFeld.Name & " = NULL,"
Else
strSQL = strSQL & objFeld.Name & " = " & _
Me.Detailbereich.Controls(objFeld.Name).Value & ","
End If
End If
Next objFeld
'Komma am Ende abschneiden
strSQL = Mid(strSQL, 1, Len(strSQL) - 1)
strSQL = strSQL + " WHERE " & strID & "=" & _
Me.Detailbereich.Controls(strID).OldValue
'SQL-Anweisung ausführen
On Error GoTo FEHLER
Debug.Print strSQL
objConn.Execute strSQL
'Recordset neu laden
objConn.Close
Set objConn = Connect1()
Set objRs = GetRecordset(objConn, "SELECT * FROM " & _
strTab & " WHERE " & strPS & ">0")
'Datensatz aktivieren und suchen
objRs.MoveFirst
Do While Not (objRs.EOF)
If Not (objRs.Fields(strID).Value = varID) Then
objRs.MoveNext
Fortsetzung Listing 6
Else
Exit Do
End If
Loop
Exit Sub
FEHLER:
Debug.Print strSQL
Debug.Print Err.Number & " -> " & Err.Description
MsgBox Err.Number & " -> " & Err.Description
End Sub
Sub DSLoeschen(Optional strTabname As String = _
"[tabAdressen$]", Optional strID As String = "ID")
Dim varID As Variant
Dim objFeld As ADODB.Field
Dim strSQL As String
varID = Me.Detailbereich.Controls(strID).Value
If Not (objRs.EOF) Then
objRs.MoveNext
Else
objRs.MoveFirst
End If
DSanzeigen
If IsNumeric(varID) Then
strSQL = "UPDATE " & strTabname & _
" SET " & strPS & "=" & (-1 * varID)
strSQL = strSQL & " WHERE " & strPS & "=" & varID
Else
strSQL = "UPDATE " & strTabname & _
" SET " & strPS & "='#geloescht#'"
strSQL = strSQL & " WHERE " & strPS & "='" & varID & "'"
End If
objConn.Execute strSQL
Set objRs = GetRecordset(objConn, "SELECT * FROM " & _
strTab & " WHERE " & strPS & ">0")
'Datensatz aktivieren und suchen
objRs.MoveFirst
Do While Not (objRs.EOF)
If Not (objRs.Fields(strID).Value = _
Me.Detailbereich.Controls(strPS).Value) Then
objRs.MoveNext
Else
Exit Do
End If
Loop
End Sub
Sub DSNeu(Optional strTabname As String = _
"[tabAdressen$]", Optional strID As String = "ID")
'Maximale ID berechnen
Dim varID As Variant
Dim objContr As Control
Dim objFeld As ADODB.Field
Dim objRS2 As ADODB.Recordset
Set objRS2 = GetRecordset(objConn, _
"SELECT Max(" & strPS & ") AS MAXID FROM " & _
strTab & " WHERE " & strID & ">0")
objRS2.MoveFirst
varID = objRS2.Fields("MAXID").Value
If IsNumeric(varID) Then
'1 addieren
varID = varID + 1
Else
'löschen
varID = ""
End If
objRS2.Close
Set objRS2 = Nothing
'Alle Felder leeren
On Error Resume Next
For Each objFeld In objRs.Fields
Me.Detailbereich.Controls(objFeld.Name).Value = ""
Next objFeld
'ID-Feld setzen
Me.Detailbereich.Controls(strPS).Value = varID
'Datensatz einfügen
On Error GoTo FEHLER
objConn.Execute "INSERT INTO " & strTab & _
" (" & strPS & ") VALUES(" & varID & ");"
Exit Sub
FEHLER:
Debug.Print Err.Number & " -> " & Err.Description
Debug.Print "INSERT INTO " & strTab & _
" (" & strPS & ") VALUES(" & varID & ");"
End Sub

Die Prozedur DSSpeichern erzeugt aus den Daten im Formular die benötigte UPDATE-Anweisung. Damit auch die ID bei Bedarf geändert werden kann, wird als WHERE-Kriterium der ursprüngliche Wert des Steuerelements ID verwendet. Für Feldwerte mit leeren Zeichenfolgen, die in numerischen Feldern gespeichert werden sollen, wird der Wert NULL in die UPDATE-Anweisung geschrieben.

Ist die UPDATE-Anweisung ausgeführt, liest die Prozedur die Daten neu in das Recordset ein und sucht dann wieder den zuvor angezeigten Datensatz anhand der ID, die zuvor in der Variablen varID gespeichert wurde.

Die Prozedur DSLoeschen verfährt ganz ähnlich, nur dass sie zunächst den vorhergehenden Datensatz (oder den ersten Datensatz) aktiviert und ihn anzeigt. Danach wird abhängig davon, ob die ID numerisch ist oder nicht, die UPDATEAnweisung generiert und ausgeführt. Dann wird auch hier wieder das Recordset neu gefüllt und der vorher angezeigte Datensatz aktiviert, bevor er dann mit der Prozedur DSAnzeigen angezeigt wird.

Die Prozedur DSNeu erzeugt einen neuen Datensatz. Das Besondere hier ist, dass zunächst aus der Primärschlüsselspalte der nächste Wert ermittelt wird, indem die Feldfunktion Max in einer SQL-Abfrage ausgeführt und dann bei numerischem Ergebnis 1 addiert wird. Dieser neue Wert wird dann in das Eingabefeld der Primärschüsselspalte geschrieben und die übrigen Felder auf eine leere Zeichenfolge gesetzt. Gleichzeitig sorgt eine INSERT-Anweisung dafür, dass der leere Datensatz mit der berechneten ID in die Tabelle eingefügt wird.

Datensätze erstellen

Damit der Benutzer einen neuen Datensatz anfügen kann, müssen Sie noch einen weiteren Button einfügen und für diesen die Click-Ereignisprozedur aus Listing 7 erstellen. Die Prozedur ruft zuerst die Prozedur DSNeu und dann DSSpeichern auf.

Private Sub bttNeu_Click()
DSNeu strTab, strPS
DSSpeichern strTab, strPS, "PLZ,ID"
End Sub

Mögliche Fehlermeldungen und Fehlerquellen

Versuchen Sie den Code anzupassen oder haben Sie selbst schon einmal versucht, ODBC-Abfragen zu erstellen und auszuführen, wissen Sie, wie schnell es dabei zu Fehlermeldungen kommt. Leider sind diese in der Regel unverständlich und lassen nicht immer einen einfachen Schluss auf das zugrunde liegende Problem zu. Nachfolgend sollen daher die wichtigsten Fehlermeldungen kurz erläutert werden.

Fehlermeldungen, die auf fehlende Schreibrechte hinweisen

Die folgenden Fehlermeldungen erhalten Sie
auch dann, wenn das Recordset Schreibrechte
hat und auch für die Datenbankverbindung
Schreibrechte definiert sind. Sie weisen darauf
hin, dass der Schreibzugriff auf Excel über die
Service-Packs deaktiviert wurde:

ERROR [22018] [Microsoft][ODBC Excel Driver] Datentypen in Kriterienausdruck unverträglich

Diese Fehlermeldung besagt, dass Sie

Mit „ungültig“ ist hier gemeint, dass der zugewiesene
Wert nicht den korrekten Datentyp hat.

Fehlerhaft wäre WHERE ID='17', wenn ID ein numerisches Feld ist. Genauso fehlerhaft wäre die Zuweisung SET Artikeltext=107, wenn es sich beim Artikeltext um ein Textfeld handelt.

Erhalten Sie diese Fehlermeldung, prüfen Sie, ob Sie die Werte für Textfelder in Hochkommata eingefasst haben und ob Sie die Werte für numerische Felder nicht in Hochkommata eingefasst haben. Außerdem sollten Sie prüfen, ob Sie allen numerischen Feldern auch tatsächlich numerische Werte zugewiesen haben.

ERROR [HY000] [Microsoft][ODBC Excel Driver] ISAM unterstützt das Löschen von Daten in einer verknüpften Tabelle nicht

Diese Meldung erhalten Sie, wenn Sie versuchen, eine DELETE-Anweisung auszuführen, die eine oder mehrere Zeilen aus der Excel-Tabelle löscht. Die DELETE-Anweisung wird vom ODBC-Treiber für Excel nicht unterstützt.

ERROR [07006] [Microsoft][ODBC Excel Driver]Attributverletzung beschränkter Datentypen

Die Fehlermeldung „Attributverletzung beschränkter Datentypen“ tritt auf, wenn einer der folgenden Fälle zutrifft:

Sie sollten als Erstes kontrollieren, ob einer der Werte den Typ Null hat, und diesen dann gegebenenfalls durch einen anderen, gültigen Wert ersetzen.

ERROR [07002] [Microsoft][ODBC Excel Driver] ? Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben

Diese Fehlermeldung ist ganz kurios. Sie erhalten Sie, wenn Sie in einer SQL-Anweisung Feldnamen verwenden, die es nicht gibt, oder wenn Sie Zeichenketten in Anführungszeichen und nicht in Hochkommata einfassen. Genauso kann sie auftreten, wenn Sie versuchen, numerische Werte in Hochkommata einzufassen. Dabei hat die Anzahl Parameter, die als erwartet gemeldet werden, nichts damit zu tun, wie viele nicht vorhandene oder fehlerhafte Felder Sie verwenden, sondern lässt nur einen vagen Schluss darauf zu, wo in etwa der Fehler zu finden ist. Ist der zweite genannte Feldname nicht vorhanden, erhalten Sie die Meldung „1 Parameter wurde erwartet“, beim dritten erhalten Sie die Meldung „2 Parameter wurden erwartet“ etc.

Sollten Sie diese Fehlermeldung erhalten, prüfen Sie am besten noch einmal, ob die Schreibweisen der Feldnamen auch hinsichtlich Großund Kleinschreibung korrekt sind und ob Sie nicht versehentlich eine Null anstelle eines „O“ eingegeben haben. Eventuell haben Sie auch nur einen Buchstabendreher im Feldnamen oder ein kleines „l“ mit einem großen „I“ verwechselt.

Zusammenfassung

ODBC-Datenbankzugriffe sind nicht einfach zu handhaben, insbesondere dann nicht, wenn die ODBC-Datenquelle eine Excel-Arbeitsmappe ist. Beim Löschen und Erstellen von Datensätzen entstehen leere Zeilen, die so einfach über ODBC nicht zu beheben sind (Bild 4), weil eben keine ganzen Zeilen gelöscht werden können.

Bild 4: Leerzeilen und „gelöschte“ Daten machen die Tabelle unübersichtlich.

Problematisch ist auch, dass es zu Problemen wegen inkompatibler Datentypen kommen kann, und nicht zuletzt ist auch das Service Pack 2 für Office 2003 und Service Pack 3 für Office XP ein großes Hindernis. Ein ODBC-Datenzugriff auf Excel ist also möglich, aber in keinem Fall eine optimale Lösung. Das werden Sie auch erkennen, wenn Sie das Beispiel testen. Auch wenn die wichtigsten Funktionen implementiert wurden, ist es trotz der langen Listings noch keinesfalls perfekt, sondern gerade mal eine Rohfassung.