SQL Server-Migration: Anpassen des VBA-Codes

Möchtest Du den gesamten Artikel lesen? Und vielleicht sogar den Artikel im PDF-Format und die Beispieldateien herunterladen? Dann hole Dir den Artikel gleich hier - völlig kostenlos!

Wenn Du Deine Datenbank zum SQL Server migriert hast, also alle Tabellen in einer SQL Server-Datenbank liegen und mit dem Frontend verknüpft sind, kannst Du ohne Probleme über die Tabellenverknüpfungen auf die Tabellen im SQL Server zugreifen. Wenn Du jedoch per VBA, speziell mit den Methoden OpenRecordset und Execute, auf diese Tabellen zugreifen möchtest, kann es zu Fehlermeldungen kommen. In diesem Artikel erläutern wir, welche Fehler dies sind, warum sie auftreten und wie Du Deine Datenzugriffe auf die Tabellen der SQL Server-Datenbank so anpasst, dass sie fehlerfrei laufen. Außerdem schauen wir uns eine Besonderheit an, die beim Anlegen neuer Datensätze mit der AddNew-Methode des Recordset-Objekts auftritt – und schließlich gibt es noch eine Besonderheit bei Formularen, die wir ebenfalls berücksichtigen.

Wenn Du die Migration zum SQL Server zum Beispiel mit dem SQL Server Migration Assistant durchgeführt hast, steht als nächster Schritt in der Regel der Test an, wie sich das Access-Frontend mit den Tabellen aus dem neuen SQL Server-Backend verträgt.

Dabei schauen wir uns in diesem Artikel primär die VBA-Seite an. Hier treten verschiedene Fehler auf, die wir uns zunächst einmal ansehen.

Fehler beim Öffnen eines Recordsets

Der erste Fehler, der auftreten kann und vermutlich auch auftreten wird, wenn Du die OpenRecordset-Methode des Database-Objekts verwendest, ist der aus Bild 1.

Bild 1: Fehler in der OpenRecordset-Methode

Die Fehlermeldung ist aussagekräftig genug, um den Fehler selbst zu beheben: Wir benötigen einen weiteren Parameter namens dbSeeChanges.

Diesen fügen wir wie folgt zur OpenRecordset-Methode hinzu:

Set rst = db.OpenRecordset("tblBuecher", _
     dbOpenDynaset, dbSeeChanges)

Diese Konstanten müssen wir immer als dritten Parameter angeben, wenn wir mit OpenRecordset eine Tabelle oder Abfrage öffnen wollen.

Wenn wir bisher wie in folgendem Beispiel überhaupt keinen Parameter angegeben haben, erscheint die gleiche Fehlermeldung:

Set rst = db.OpenRecordset("tblBuecher")

Wir können dann aber nicht einfach dbSeeChanges wie hier als dritten Parameter setzen und den zweiten Parameter leer lassen:

Set rst = db.OpenRecordset("tblBuecher", , dbSeeChanges)

Dies würde bedeuten, dass beim OpenRecordset der Standardwert für den zweiten Parameter verwendet wird, der dbOpenTable lautet.

Und dbOpenTable funktioniert mit ODBC-Tabellenverknüpfungen gar nicht. Wir müssen also einen der übrigen Werte für den zweiten Parameter angeben.

Wenn wir hingegen dbOpenSnapshot oder dbOpenForwardOnly nutzen, brauchen wir dbSeeChanges nicht anzugeben. Diese beiden Methoden öffnen das Recordset nämlich schreibgeschützt.

Dies liefert bereits einen wichtigen Hinweis auf den Grund für die Fehlermeldung. Wenn wir diese nur erhalten, wenn wir das Recordset in dem Modus öffnen, in dem wir es bearbeiten können, muss es etwas mit dem Bearbeiten zu tun haben.

Außerdem tritt dieser Fehler nur auf, wenn die zum SQL Server migrierte Tabelle ein Autowert-Feld als Primärschlüsselfeld enthält. Dieses wird im SQL Server bei der Migration als IDENTITY-Spalte abgebildet, was im Grunde nichts anderes ist als ein Autowert.

Der entscheidende Unterschied ist jedoch, dass die Identitätsspalte nun vom SQL Server gefüllt und verwaltet wird und nicht mehr direkt von der Access-Datenbank aus.

Nun stellen wir uns vor, dass wir im Recordset mit AddNew einen neuen Datensatz anlegen. Dies sorgt dafür, dass beim Speichern zwar ein neuer Identitätswert in der SQL Server-Tabelle angelegt wird, aber dieser wird nicht automatisch an Access zurückgegeben.

Um genau dies zu gewährleisten, muss man den Parameter dbSeeChanges setzen. Er sorgt dafür, dass bei Anlegen neuer Datensätze das Recordset direkt aktualisiert wird und SQL Server die ID des neuen Datensatzes an Access zurückmeldet.

Zusammengefasst: dbSeeChanges zwingt DAO/ODBC dazu, nach einem Insert/Update die serverseitig erzeugten beziehungsweise geänderten Werte (zum Beispiel IDENTITY) wieder einzulesen. Ohne diesen Flag bricht DAO bei schreibenden Zugriffen auf verknüpfte SQL-Server-Tabellen mit Fehler 3622 ab.

OpenRecordset für ein QueryDef-Objekt

Das Gleiche gilt, wenn wir OpenRecordset als Methode eines QueryDef-Objekts ausführen. Im folgenden Beispiel erstellen wir ein QueryDef-Objekt auf Basis einer Abfrage.

Wenn wir ein Recordset für dieses QueryDef-Objekt öffnen wollen, müssen wir ebenfalls dbSeeChanges übergeben – dieses Mal allerdings mit dem zweiten Parameter:

Public Sub Test_Recordset_1()
     Dim db As DAO.Database
     Dim qdf As DAO.QueryDef
     Dim rst As DAO.Recordset
     Set db = CurrentDb
     Set qdf = db.CreateQueryDef("", _
         "SELECT * FROM tblBuecher")
     Set rst = qdf.OpenRecordset(dbOpenDynaset, _
         dbSeeChanges)
     Do While Not rst.EOF
         Debug.Print rst!Buchtitel
         rst.MoveNext
     Loop
End Sub

Fehler beim Ausführen von Aktionsabfragen

Aktionsabfragen wie INSERT INTO, UPDATE oder DELETE können wir auf zwei Arten ausführen:

• Docmd.RunSQL
• Execute-Methode des Database-Objekts

Aktionsabfragen mit DoCmd.RunSQL

Wenn wir die DoCmd.RunSQL-Methode verwenden, können jede der folgenden Aktionsabfragen ohne Fehler ausführen:

DoCmd.RunSQL "INSERT INTO tblBuecher(BuchID, Buchtitel) VALUES(38, ''Buch'')"
DoCmd.RunSQL "UPDATE tblBuecher SET Buchtitel = ''Buch 1'' WHERE BuchID = 38"
DoCmd.RunSQL "DELETE FROM tblBuecher WHERE BuchID = 38"

Aktionsabfragen mit Execute

Bei Verwendung der Execute-Methode sieht dies anders aus. Allein das Anlegen eines Datensatzes wie folgt gelingt ohne Fehlermeldung:

Public Sub Test_DbExecute_INSERTINTO()
     Dim db As DAO.Database
     Set db = CurrentDb
     db.Execute "INSERT INTO tblBuecher(BuchID, Buchtitel) " _
         & "VALUES(37, ''Buch'')", dbFailOnError
End Sub

Wenn wir jedoch versuchen, diesen Datensatz zu aktualisieren, erhalten wir wieder den Fehler mit der Nummer 3622:

Public Sub Test_DbExecute_UPDATE()
     Dim db As DAO.Database
     Set db = CurrentDb
     db.Execute "UPDATE tblBuecher SET Buchtitel = ''Buch'' " _
         & "WHERE BuchID = 36", dbFailOnError
End Sub

Und auch beim Löschen eines Datensatzes tritt dieser Fehler auf:

Public Sub Test_DbExecute_DELETE()
     Dim db As DAO.Database
     Set db = CurrentDb
     db.Execute "DELETE FROM tblBuecher WHERE BuchID = 36",  _
         dbFailOnError
End Sub

Diesen können wir ähnlich beheben wie bei der OpenRecordset-Methode. Dieses Mal müssen wir dbSeeChanges jedoch nicht als weiteren Parameter anhängen, sondern wir müssen diesen mit dem vorhandenen (und immer dringend empfohlenen) dbFailOnError in einem Parameter vereinen.

Das gelingt über den Plus-Operator (+) etwa wie folgt:

db.Execute "UPDATE tblBuecher SET Buchtitel = ''Buch'' " _
     & "WHERE BuchID = 36", dbFailOnError + dbSeeChanges

Warum aber funktioniert INSERT INTO ohne die Angabe von dbSeeChanges, UPDATE und DELETE nicht? Weil INSERT INTO in diesem Fall direkt an den SQL Server durchgereicht wird, für die anderen sind jedoch Änderungen an bestehenden Datensätzen nötig – und dafür müssen die Primärschlüssel wieder zwischen Access und dem SQL Server synchron gehalten werden.

Probleme mit Ja/Nein-Feldern

Ein weiteres Problem, das dieses Mal eher inhaltliche Fehler produziert statt Laufzeitfehler, sind Ja/Nein-Felder. Wenn wir eine Tabelle mit Ja/Nein-Feldern zum SQL Server übertragen, wird daraus standardmäßig der Datentyp bit erzeugt.

Hier gibt es zunächst einen wichtigen Unterschied zu berücksichtigen: Während in Access der Wert für Ja/True intern als -1 und der Wert für Nein/False als 0 gespeichert wird, kann das bit-Feld lediglich die Werte 1 und 0 speichern.

Im SQL Server haben die Werte 1 und 0 auch keine tiefergehende Bedeutung – sie entsprechen lediglich den Zahlenwerten.

Dass Access die Werte dieser Felder korrekt als True oder False interpretiert, stellt der ODBC-Treiber sicher. Mit Tabellen, die per ODBC verknüpft sind, ändert sich auch erst einmal nichts: 1 entspricht True und 0 entspricht False.

Wenn wir also zum Beispiel in der Beispieldatenbank alle gelesenen Bücher abfragen wollen, funktioniert die folgende Abfrage auf der entsprechenden Tabellenverknüpfung korrekt:

SELECT * FROM tblBuecher WHERE Gelesen = True

Auch False funktioniert hier einwandfrei.

Gerade wenn wir solche Abfragen mit dem Abfrageentwurf zusammenstellen, ist sichergestellt, dass diese funktionieren, da dieser für Ja/Nein-Felder auch die Werte -1 und 0 immer in Wahr und Falsch übersetzt.

Nun schauen wir uns aber ein Recordset an, das wir per VBA öffnen. Hier verwenden wir nicht den Wert True als Vergleichswert, sondern den Wert -1:

Public Sub Test_TrueFalse()
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Set db = CurrentDb
     Set rst = db.OpenRecordset("SELECT * FROM tblBuecher " _
         & "WHERE Gelesen = -1", dbOpenDynaset, dbSeeChanges)
     Do While Not rst.EOF
         Debug.Print rst!BuchID, rst!Buchtitel
         rst.MoveNext
     Loop
End Sub

Führen wir diese Abfrage aus, erhalten wir überraschenderweise keine Daten im Direktbereich!

Auch eine DCount-Funktion, mit der wir die Anzahl der gelesenen Bücher prüfen wollen, liefert den Wert 0 im Direktbereich:

Public Sub Test_TrueFalseDLookup()
     Debug.Print DCount("BuchID", "tblBuecher", "Gelesen = -1")
End Sub

Damit scheint festzustehen: In Abfragen, in denen nicht durch den Abfrageentwurf der Wert -1 durch den Wert Wahr/True ersetzt wird, erhalten wir nicht die korrekten Ergebnisse, weil hier tatsächlich der Access-Wert -1 mit dem im SQL Server gespeicherten Wert 1 verglichen wird – und das liefert keine Ergebnisse.

Korrekt wäre hier also für das Recordset:

Set rst = db.OpenRecordset("SELECT * FROM tblBuecher " _
     & "WHERE Gelesen = 1", dbOpenDynaset, dbSeeChanges)

Und in der DCount-Anweisung müssten wir Folgendes schreiben, um das korrekte Ergebnis zu erhalten:

Debug.Print DCount("BuchID", "tblBuecher", "Gelesen = 1")

Müssen wir nun in allen Abfragen -1 durch 1 ersetzen?

Dies würde tatsächlich funktionieren, aber nur, wenn wir ausschließlich mit SQL Server-Tabellen arbeiten würden.

Wenn wir später einmal, aus welchem Grund auch immer, noch einmal auf Access-Tabellen umstellen, liefern die Abfragen wieder falsche Werte, weil Access beim Vergleich eines Ja/Nein-Feldes mit dem Wert -1/True mit dem Wert 1 wieder falsche Ergebnisse liefern würde.

Damit liegt die Lösung auf der Hand: Wir müssen einfach immer True oder False als Kriterium für Ja/Nein-Felder verwenden, solange diese Abfragen sich auf per ODBC verknüpfte Tabellen aus einer SQL Server-Datenbank beziehen.

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo