VBA-(SQL)-String Tutorial

Ein Thema mit dem Anfänger in der Access und VBA-Programmierung häufig Probleme haben, ist es innerhalb des VBA-Codes Strings zu definieren. Wenn die betreffenden Strings Text enthalten, der visuell ausgegeben wird, ist es meist noch einfach Fehler zu finden und zu korrigieren. Sobald aber der String technisch weiterverarbeitet wird, wie z.B. ein SQL-String, ist ein Fehler nicht direkt visuell zu erkennen. Es wird nur eine, mehr oder weniger aussagefähige, Fehlermeldung ausgelöst. Als Anfänger bist du dann oft überfordert, ratlos und weißt nicht, wie du den Fehler beheben kannst.

Zum einem möchte ich zeigen, wie du das gewünschte Ergebnis bei Stringverkettung mit Variablen und Funktionen erreichst, ganz besonders für SQL-Strings, die an die Jet-/Ace-Datenbankengine übergeben werden.

Zum anderen möchte ich aber auch verdeutlichen, wie du besonders in diesem Zusammenhang Fehler durch sinnvollen, lesbaren Code von vornherein vermeiden kannst und dass du auch hier ein visuelles Output zur Fehleranalyse und Behebung einsetzen kannst.

Das zentrale Medium, mit dem ich diese Inhalte vermitteln möchte, ist ein Video-Tutorial, in dem ich all die oben genannten Punkte direkt demonstriere. Dieser Text hier ist also als eine Kurzzusammenfassung und eine Art Inhaltsverzeichnis zu verstehen.

Wenn das Thema für dich insgesamt neu ist, empfehle ich dir den Text bzw. das Video von Beginn an zu lesen bzw. anzuschauen. Falls du dich nur zu einem speziellen Unterthema informieren möchtest, habe ich nachfolgend ein Inhaltsverzeichnis mit Direktlinks zu den entsprechenden Textpassagen oder dem jeweiligen Start-Zeitpunkt im Video eingefügt.

Inhaltsverzeichnis / Shortcuts

Allgemeine Stringoperationen

Dim eineStringVariable As String eineStringVariable = "Dies ist der Text des Strings"

Wenn du Anführungszeichen in den String einbauen möchtest, musst du diese im VBA Quellcode verdoppeln. Andernfalls ist für den VBA-Compiler nicht klar, dass diese nicht den String beenden sollen.

eineStringVariable = "Das Wort ""Anführungszeichen"" steht in selbigen."

Funktionsaufrufe und Variablen, deren (Rückgabe-)Werte in den String eingebaut werden sollen, dürfen nicht direkt in den String hineingeschrieben werden, sondern müssen mit dem String verkettet werden. Dazu müssen die einzelnen Teilstrings mit Anführungszeichen abgeschlossen werden und der Variablen- bzw. Funktionswert mit dem Kaufmännischen-Und-Zeichen verkettet werden.

eineStringVariable = "Das aktuelle Datum ist " & Date & " und hier geht der Text weiter"

Lange Texte, die direkt im VBA-Editor einen String zugewiesen werden, resultieren oft in sehr langen Zeilen im Code. Diese langen Zeilen sind sehr unübersichtlich, weil man horizontal scrollen muss, um den ganzen Text zu lesen.

Um die Übersichtlichkeit zu verbessern, sollten lange Anweisungen in VBA-Code umgebrochen werden. Dazu wird am Zeilenende der Unterstrich verwendet, um anzuzeigen, dass die Anweisung auf der nächsten Zeile weitergeht. Bei langen Strings muss der Teil-String am Zeilenende mit Ausführungszeichen beendet werden und das Kaufmännische-Und-Symbol verwendet werden, um ihn mit dem nächsten Teilstring in der nächsten Zeile zu verketten.

eineStringVariable = "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. " & _ "Maecenas porttitor congue massa. Fusce posuere, magna sed " & _ "pulvinar ultricies, purus lectus malesuada libero, sit amet " & _ "commodo magna eros quis urna."

Die obige Vorgehensweise erzeugt aber nur einen Zeilenumbruch in der VBA-Umgebung, nicht aber in dem resultierenden String. Wenn innerhalb eines Strings ein Zeilenumbruch eingebaut werden soll, dann muss das über die VB-Konstante vbCrLf (CR = Carriage Return, LF = Line Feed) erfolgen.

Diese Konstante wird analog zu der oben beschriebenen Technik für Variablen und Funktionen mit dem String verkettet.

Alternativ kann man auch die Funktion Chr verwenden, um aus den ASCII-Codes für den Wagenrücklauf (Carriage Return, CR = 13), und Zeilenvorschub (Line Feed, LF = 10) diese Zeichen generieren zu lassen. Innerhalb des VBA-Editors ist diese Technik weniger lesbar und kostet unnötige Rechenzeit, daher ist die Konstante vorzuziehen. In Ausdrücken in Abfragen und der Access-Oberfläche können keine VBA-Konstanten verwendet werden. Dort ist die Lösung mit den Funktionen eine sinnvolle Alternative.

SQL-Strings

Wenn du SQL-Strings im VBA-Editor aufbaust, solltest du die obigen Grundlagen beherrschen. Du brauchst diese ständig um deine SQL-Strings korrekt und gut lesbar im Code zu schreiben.

Allgemeine Hinweise

Ganz allgemein möchte ich immer empfehlen, einen SQL-String, den man im Code aufbaut nicht direkt an eine Prozedur oder Eigenschaft zuzuweisen, sondern vorher eine dafür deklarierten String Variable zuzuweisen. - Technisch macht dies keinen Unterschied, aber die dedizierte Variable ermöglicht es dir den kompletten, zusammengesetzten SQL-Text einfach mit Debug.Print in das Direktfenster oder in einer Messagebox auszugeben. Das ist eine große Hilfe, um potentielle Fehler in der Stringverkettung zu finden.

SQL-Strings mit Ganzzahlen

Wenn du Ganzzahlen in deinem SQL-String verwenden willst, kannst du konstante unveränderliche Werte direkt in den String schreiben. Variablen müssen mit den String verkettet werden, damit ihre Werte anstatt der Variablennamen in den String eingefügt werden.

Dim customerId As Long Dim sql As String customerId = 4 sql = "SELECT * FROM tblOrder WHERE CustomerId = " & customerId

SQL-Strings mit Zeichenfolgen

Zeichenfolgen, also Text innerhalb von SQL-Strings sind etwas problematischer. Genauso wie in VBA müssen Zeichenfolgeliterale in SQL in Begrenzer eingeschlossen werden, um sie als zusammenhängenden Term von den eigentlichen SQL-Schlüsselworten und Bezeichnern für Tabellen-, Abfragen-, Spalten- und Funktionsnamen abzugrenzen.

In Jet-(Access)-SQL können solche Zeichenfolgeliterale ebenfalls mit Anführungszeichen begrenzt werden.

Dim sql As String sql = "SELECT * FROM tblOrder WHERE OrderStatus = ""In Progress"""

Für konstante Texte ist das noch handhabbar, aber sobald du String-Variablen in VBA verwendest, deren Werte in den SQL-String eingebettet werden sollen, wird das furchtbar unübersichtlich.

Dim sql As String Dim orderStatus As String orderStatus = "In Progress" sql = "SELECT * FROM tblOrder WHERE OrderStatus = """ & orderStatus & """"

Eigentlich ist der Textbegrenzer im SQL-Standard nicht das Anführungszeichen, sondern das Hochkomma. Wenn wir nun die Text-Literale, die wir als Kriterien in unseren SQL-String einbetten wollen, in Hochkommata einschließen, wird wesentlich besser klar, was die VBA-Stringbegrenzer sind und was als Begrenzer für Literale in den String eingebettet wird.

Dim sql As String Dim orderStatus As String orderStatus = "In Progress" sql = "SELECT * FROM tblOrder WHERE OrderStatus = '" & orderStatus & "'"

SQL-Strings mit Datumswerten

Genauso wie Text-Zeichenfolgen müssen auch Datumsliterale mit Begrenzern im SQL-Text eingeschlossen werden. Anders als für Text-Werte wird nicht das Hochkomma als Begrenzer verwendet, sondern das Raute-Symbol #.

Zusätzlich ist bei Datumswerten zu beachten, dass die Jet-/Ace-Datenbankengine, die in Access zum Einsatz kommt, anders als die VBA-Umgebung, Datumswerte nur im US-Amerikanischen- oder ISO-Datumsformat versteht.

Um diesem Umstand Rechnung zu tragen, müssen Datumswerte mit der Format-Funktion explizit in eines dieser Formate konvertiert werden, bevor sie in den SQL-String eingebettet werden. Da ich das ISO-Datumsformat (yyyy-mm-dd) wesentlich intuitiver lesbar finde als das Amerikanische Format, werde ich mich in dem folgenden Beispiel darauf beschränken.

Dim sql As String Dim firstDayCurrentMonth As Date firstDayCurrentMonth = DateSerial(Year(Date), Month(Date), 1) sql = "SELECT * FROM tblOrder WHERE OrderDate >= #" & Format(firstDayCurrentMonth, "yyyy-mm-dd") & "#"

SQL-Strings mit Dezimalzahlen

Eigentlich sollte man ja annehmen, dass die Verarbeitung von Dezimalzahlen nicht anders abläuft, als die Verarbeitung von Ganzzahlen. - Leider kommt uns hierbei das Dezimaltrennzeichen in die Quere.

Bei einer Konvertierung von einer Kommazahl in einen String in VBA wird automatisch das Dezimaltrennzeichen aus den Regionaleinstellungen des Windows Betriebssystems verwendet. Dies ist in Deutschland üblicherweise das Komma.

Die Jet-/Ace-Datenbankengine versteht ausschließlich den Punkt als Dezimaltrennzeichen. Das Komma trennt in SQL verschiedene Ausdrücke voneinander ab und führt somit innerhalb einer Zahl zu einem Fehler.

Um explizit eine Konvertierung von einer Dezimalzahl in einen String, mit dem Punkt als Trennzeichen, zu erreichen, kannst du in VBA die Str-Funktion verwenden.

Dim sql As String Dim orderTotal As Currency orderTotal = 119.99 sql = "SELECT * FROM tblCustomer WHERE TotalOrderValue >= " & Str(orderTotal)

Lange SQL-Strings in VBA formatieren

Wenn du in VBA lange (länger als direkt auf deinem Bildschirm in einer Zeile sichtbar) SQL-Statements aufbaust, verbessert es die Lesbarkeit der SQL-Anweisung deutlich, wenn du an vor den wesentlichen SQL-Elementen einen Zeilenumbruch in VBA einfügst.

Das heißt, die FROM-Klausel, nachfolgende JOIN-Ausdrücke, eine WHERE-Bedingung oder ein eventuell verwendetes GROUP BY, sollten jeweils auf einer neuen Zeile beginnen.

Dim sql As String sql = "SELECT tblCustomer.CustomerId, tblCustomer.CustomerName, tblCustomer.CustomerZipCode, " & _ " tblCustomer.DateOfBirth, tblCustomer.TotalOrderValue, tblCustomer.LastOrderTime, " & _ " tblOrder.OrderId, tblOrder.OrderDate, tblOrder.OrderStatus " & _ " FROM tblCustomer " & _ " INNER JOIN tblOrder " & _ " ON tblCustomer.CustomerId = tblOrder.CustomerId " & _ " WHERE tblCustomer.CustomerId = 4 " & _ " AND TotalOrderValue > 100;"

Dabei solltest du aber unbedingt beachten, dass zwischen dem Ende der einen Zeile und dem Beginn der nächsten innerhalb des SQL-Textes ein Leerzeichen vorhanden ist, um die jeweiligen Ausdrücke voneinander zu trennen.

Es schadet nicht, wenn mehr als ein Leerzeichen vorhanden ist, daher habe ich mir angewöhnt sowohl am Ende einer Zeile als auch zu Beginn der nächsten ein Leerzeichen zu schreiben.

Nachträgliche Ergänzungen / Häufige Nachfragen

Der Inhalt meines Videos ist mit dem obigen Text bereits vollständig abgedeckt. Ich habe allerdings seit Veröffentlichung des Videos einige Emails mit Fragen bekommen, die zwar nicht direkt das Kernthema der Strings in VBA betreffen (daher habe ich sie ursprünglich nicht behandelt), aber damit sehr eng verwandt sind. Diese Fragen möchte ich hier noch beantworten.

Leer- und Sonderzeichen in Spalten- oder Tabellennamen

Du versuchst meine Beispiele mit deiner Datenbank nachvollziehen, aber du bekommst Fehlermeldungen wie

Eine mögliche Ursache für diese Fehlermeldungen, ist es wenn du Leerzeichen oder Sonderzeichen wie + - & * / in deinem Tabellen- oder Feldnamen verwendet hast.

In fast allen Programmiersprachen, einschließlich VBA und SQL, werden nur zusammenhängende Zeichenfolgen aus Zahlen, Buchstaben und dem Unterstrich als Namen für Objekte oder Variablen erkannt. Die obigen Sonderzeichen sind Operatoren in SQL und das Leerzeichen trennt verschiedene Bezeichner voneinander.

Wenn du neue Tabellen erstellst, solltest du daran denken, dass du keine Leer- und Sonderzeichen in den Tabellen oder Feldnamen verwendest. Diese verursachen Mehrarbeit und bringen keine Vorteile. Feld- und Tabellennamen sind nur für den Entwickler und die Datenbank-Engine relevant, aber nicht für Benutzer, die sich vielleicht eine „schöne“ Schreibweise wünschen. Diese kannst du über entsprechende Beschriftungen in deiner Programmoberfläche erreichen.

Wenn du bereits eine bestehende Anwendung hast, in der Leerzeichen oder Sonderzeichen in den Objektnamen vorkommen, musst du glücklicherweise nicht von vorne anfangen und alles ändern.

In SQL kannst du Tabellen-, Feld- oder Abfragenamen in eckige Klammern einschließen, damit diese trotz evtl. im Namen vorkommender Problemzeichen als zusammenhängender Name erkannt werden.

Dim sql As String sql = "SELECT [Customer Name] FROM [tblCustomer Blanks] "

Begrenzer in Text-Literalen

Du hast ein Feld vom Typ Text in einer Tabelle und möchtest es in der Where-Klausel einer Abfrage als verwenden. Die möglichen Kriterien können aber den selbst den Text-Begrenzer, also das Hochkomma enthalten, wie das z.B. bei einer Namenssuche nach O‘Brian der Fall wäre.

Wenn du, wie oben gezeigt, solche Zeichenfolgeliterale in einem SQL-String einbaust, kommt es zu einem Syntaxfehler.

Das Problem ist im Kern dasselbe, das ich zu Beginn des Videos mit den Anführungszeichen im Text dargestellt habe. Für den Query-Parser der Datenbankengine ist das Literal im SQL-Text zu Ende, wenn er auf einen Begrenzer trifft. Befindet sich der Begrenzer in einem Literal, ist der Rest des Literals einfach syntaktisch falsch.

Die Lösung ist auch dieselbe, wie für das ursprüngliche Problem. Das Hochkomma innerhalb des Literals muss verdoppelt werden damit es als einzelnes Hochkomma innerhalb des Literals betrachtet wird.

Dim sql As String sql = "SELECT * FROM tblCustomer WHERE CustomerName = 'Martha O''Brian' "

Im Kontext einer Anwendung muss das natürlich automatisch erfolgen. Dies kannst du mit der Replace-Funktion erreichen.

Dim sql As String Dim customerName As String customerName = "Martha O'Brian" customerName = Replace(customerName, "'", "''") sql = "SELECT * FROM tblCustomer WHERE CustomerName = '" & customerName & "' "

Gelegentlich wird in diesem Zusammenhang der Vorschlag gemacht, anstelle der Hochkommas als Zeichenfolgebegrenzer im SQL-String doch wieder die Anführungszeichen zu verwenden. - Das ist aber keine echte Lösung. Es verschiebt nur die Symptome. Mit einem Namen, wie z.B. Wirtshaus "Zum Hirsch", hast du dann wieder dieselbe Situation.

Schlimmer noch, wenn tatsächlich beide möglichen Trennzeichen in einem Literal vorkommen, z.B. O'Brian’s Pub “The bloody bucket” dann gibt es keine Möglichkeit, dies durch die Wahl der Begrenzer zu behandeln.

Eine andere Lösung ist es, anstelle von direkt im SQL-String eingebauten Werten Parameter in der Abfrage zu verwenden. Dies ist aber ein Thema für einen anderen Artikel.

Download der Beispiel-Datenbank

Die Beispiel-Datei, die ich in dem Video verwendet habe und der auch die hier gezeigten Beispiele entnommen sind, habe ich als Referenz hier zum Download bereitgestellt.

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.
Die Emailliste wird bei Mailchimp in den USA gespeichert. Diese Auftragsverarbeitung ist vertraglich geregelt. Weitere Details in der Datenschutzerklärung.

Vorteile des Newsletter-Abos

VBA-(SQL)-String Video-Tutorial für Anfänger