ODBCDirect durch die Hintertür

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Haben Sie Ihr Backend in ein DBMS ausgelagert, also die Tabellen auf einen SQL-Server migriert, so steht Ihnen ab Access 2007 nur noch die Schnittstelle ADODB zur Verfügung, um unter VBA auf sie zuzugreifen, falls Sie sich nicht nur auf verknüpfte Tabellen beschränken möchten. Denn die Technologie ODBCDirect, mit der man direkt eine Verbindung zum Server aufbauen konnte, wurde ersatzlos gestrichen. Mit einem Trick schlagen Sie Microsoft jedoch ein Schnippchen!

ODBCDirect

Es handelt sich dabei eine Datenschnittstelle, die in der Bibliothek DAO untergebracht ist. Dort finden Sie ein Connection-Objekt, welches allein für die Kommunikation mit einem SQL-Server über ODBC zuständig ist (s. Bild 1). Doch offenbar enthält die Bibliothek diese Klasse nur noch aus Kompatibilitätsgründen, denn jeglicher Methodenaufruf auf sie endet mit einer Fehlermeldung 3827: ODBCDirect wird nicht mehr unterstützt. Schreiben Sie den Code so um, dass ADO anstelle von DAO verwendet wird. Das können Sie tun, denn etwas anderes bleibt Ihnen nun auch nicht übrig.

Bild 1: Die ODBCDirect-Klasse Connection im Objektkatalog von VBA

Der Sachverhalt stellt sich in der Praxis nicht selten ein. Haben Sie etwa eine ältere Access 2003-Datenbank im Einsatz, die ODBCDirect nutzt, und auf den Rechnern soll Office 2010 installiert werden, so kommen Sie um Anpassungen leider nicht herum. Statt der DAO-Connection verwenden Sie nun überall eine ADODB-Connection, deren Methoden aber deutlich abweichen! Hier ist einiges an Arbeit für die Migration angesagt.

Warum Microsoft die Schnittstelle entfernte, ist nicht nachzuvollziehen. Einerseits wurden die sogenannten Access-Projekte (ADP) in welchen Sie Formulare unmittelbar an einen MSSQL-Server binden konnten, ab Access 2007 komplett entfernt und stattdessen wurden ODBC-verknüpfte Tabellen empfohlen. Gleichzeitig aber strich man ODBCDirect. Ich habe dafür nur eine Erklärung: Einst waren SQL-Server ziemlich teuer, weshalb sie häufig für Low Budget-Lösungen mit Access nicht infrage kamen. Zudem scheuten viele Entwickler den Einsatz des SQL-Servers, weil hier eine Menge zusätzliches Knowhow zu erwerben war. In der Folge fanden Sie in den einschlägigen Foren und Newsgroups nur selten Fragen zum Thema ODBCDirect. Für Microsoft offenbar Grund genug, sich dieser Schnittstelle zu entledigen.

Inzwischen aber stellt nicht nur Microsoft selbst einen kostenlosen leistungsfähigen Server (SQL Server Express) zur Verfügung, sondern auch andere Größen der Branche, wie IBM mit DB2 Express oder Oracle mit Express 11g, von den OpenSource-Lösungen MySQL, MariaDB, Postgres und Firebird ganz zu schweigen, sodass der Einsatz dieser DBMS als Backend, die der Access Engine klar den Rang ablaufen, naheliegt.

Wollen Sie hier ADODB verwenden, so hat dies einen Haken: Viele der genannten DBMS stellen keinen OLEDB-Provider zur Verfügung. Manche sind instabil oder wenig performant, bei manchen finden Sie gar keinen. Dann müssen Sie den ODBC-Provider MSDASQL nehmen, der aber eine zusätzliche Ebene einzieht und deshalb in der Performance das Nachsehen hat.

Empfehlung: Voten Sie auf access.uservoice für die Wiederaufnahme von ODBCDirect! Dort nimmt Microsoft Vorschläge zu zukünftigen Access-Versionen entgegen. Früher einmal gab es das Connect-Programm von Microsoft, wo vornehmlich MVPs ihre Vorschläge einbrachten. Die wurden allerdings nicht wirklich ernst genommen. Offenbar hat sich dies geändert. Nach dem Debakel mit den Access-WebServices, die in der Zukunft deshalb eben entfallen sollen, wendet man sich nun anscheinend vermehrt den Anregungen der Community zu, wie etwa die Wiedergeburt der dBase-Schnittstelle zeigt.

Die Programmierung der ODBCDirect-Schnittstelle ist fast so einfach, wie die von lokalen oder verlinkten Access-Tabellen:

Dim oWrk As DAO.Workspace
Dim oConx As DAO.Connection
Dim sConx As String
Dim rs As DAO.Recordset
Set oWrk = oDbEng.CreateWorkspace("", "", "", dbUseODBC)
sConx = "ODBC;Driver={Microsoft dBase Driver (*.dbf)}" & _
             ";DriverID=533";Dbq=" & CurrentProject.Path
Set oConx = oWrk.OpenConnection("", , , sConx)
Set rs = oConx.OpenRecordset( _
     "SELECT * FROM KUNDENLI.DBF.DBF", dbOpenDynaset)

Hier wird zunächst ein Workspace oWrk angelegt, welches von dem abweicht, das die DBEngine von Access.Application voreingestellt hat, weil die Konstante dbUseODBC angegeben ist. Der Normalfall wäre dbUseJet. Dann wird in sConx der Connect-String zusammengebaut, der mit dem Tag ODBC; beginnen muss.

Mit Driver geben Sie den Namen eines installierten ODBC-Treibers an, in unserem Fall den dBase-Treiber. Daran schließen sich treiberspezifische Optionen an. Hier ist es vor allem der Pfad, unter dem sich die dBase-Dateien befinden, im Schlüssel Dbq.

Mit diesem Connect-String öffnen Sie das Connection-Objekt oConx. ähnlich, wie bei einem Database-Objekt, können Sie nun ein Recordset über ein SQL-Statement auf die Connection öffnen.

Aber das geht doch gar nicht mehr!

Korrekt. Führen Sie den angeführten Code in einer ACCDB aus, so tritt die erwähnte Fehlermeldung auf.

Sie könnten nun auf die Idee kommen, statt der ACE-Bibliothek die alte zu verwenden. Löschen Sie also den Verweis auf DAO und laden Sie einen auf DAO.36 neu (Microsoft DAO 3.6 Object Library). Tatsächlich ändert dies am Sachverhalt rein gar nichts. Denn der Verweis führt nicht dazu, dass die alte Engine geladen würde.

Da die alte, wie die neue, den Namen DAO trägt, verwendet Access weiterhin die interne ADEDAO.dll, die bereits beim Start von Access geladen wird. Die Bibliothek selbst wird zwar von VBA genutzt, doch quasi nur als Tabelle für die Klassen, die unter beiden Bibliotheken an dieselben GUIDs und Methoden für die Interfaces gebunden sind.

Der nächste Versuch wäre, eine eigene DBEngine anzulegen, die auf die dao360.dll verweist. Das lässt sich über die Methode CreateObject erledigen:

Dim oDBEng As Object
Dim oWrk As Object
Set oDbEng = CreateObject("DAO.DBEngine.36")
Set oWrk = oDbEng.CreateWorkspace("", "", "", dbUseODBC)

Verblüffend, auch dies zeitigt dieselbe Fehlermeldung in der letzten Zeile! Hier ist der Grund der, dass die Office-Installation in der Registry die ProgID- und CLSID-Schlüssel so verbogen hat, dass sie ebenfalls auf die acedao.dll verweisen. Also scheinen wohl alle Wege verbaut zu sein.

Es geht doch!

In der letzten Ausgabe erfuhren Sie, wie Sie COM-Objekte instanziieren können, ohne dass jene im System registriert sein müssen (ActiveX und COM ohne Registrierung verwenden). Dabei kommt ein Modul zum Einsatz (mdlComLoaderASM), das ziemlich komplex ist und auch vor Assembler-Teilen nicht Halt macht. Hier haben Sie nun eine nützliche Anwendung für die Routinen des Moduls: Wir versuchen, eine Instanz der alten JET-Engine über die Funktion GetCOMInstance zu erhalten. Das geht im Prinzip so:

Dim oDbEng As DAO.DBEngine
Dim oWrk As DAO.Workspace
Set oDbEng = GetCOMInstance(sFile, "DBEngine", True)
Set oWrk = oDbEng.CreateWorkspace("", "", "", dbUseODBC)

Und, siehe da, die Sache klappt, und wir haben Microsoft ausgetrickst! Natürlich muss in der Variablen sFile zuvor der Pfad zur dao360.dll stehen.

Aber gibt es diese Datei überhaupt auf dem Rechner mit Office 2007 ff. Wir können das nicht für alle Fälle versichern. Doch die dao360.dll wird auch von Windows selbst für seine Zwecke genutzt, weshalb die Datei nach unseren Beobachtungen mit diesem installiert wird. Auch der Pfad scheint immer derselbe zu sein. Sie können ihn vom Pfad der ACEDAO ableiten, wenn auf diese ein Verweis gesetzt ist:

sFile = References("DAO").FullPath
sFile = Replace(sFile, "OFFICE14\ACEDAO.DLL", _
                        "DAO\dao360.dll")

Heraus kommt dabei der String

C:\Program Files (x86)\Common Files\Microsoft Shared\DAO\dao360.dll

Möchten Sie also Ihre alte Access 2003-Datenbank, die ODBCDirect nutzt, nach Access 2010 migrieren, so ersetzen Sie im Code einfach alle Aufrufe auf das Connection-Objekt etwa mit dieser Routine:

Dim oConx As DAO.Connection
Dim oDbEng As DAO.DBEngine
Dim oWrk As DAO.Workspace
Function Connection As DAO.Connection
Dim sFile As String
If oConx Is Nothing Then
   sFile = References("DAO").FullPath
   sFile = Replace(sFile, "OFFICE14\ACEDAO.DLL", _
                        "DAO\dao360.dll")
   Set oDbEng = GetCOMInstance(sFile, "DBEngine", True)
   Set oWrk = oDbEng.CreateWorkspace("", "", "", dbUseODBC)
End If
Set Connection = oConx
End Function

Das Connection-Objekt steht damit generell im VBA-Projekt zur Verfügung. Auf Umgestaltung nach ADO können Sie getrost verzichten.

Ein Anwendungsbeispiel finden Sie in Listing 1. Wie zuvor ausgeführt erzeugt GetCOMInstance hier eine neue DBEngine. Statt der ProgID DBEngine allerdings ist die entsprechende CLSID für das Objekt der Begierde eingesetzt. GetCOMInstance muss nämlich sonst die CLSID aus der ProgID selbst ermitteln, was Sie der Prozedur abnehmen können.

Sub ConnectMysqlODBCDirect()
     Dim oDbEng As DAO.DBEngine
     Dim oWrk As DAO.Workspace
     Dim oConx As DAO.Connection
     Dim sConx As String
     Dim rs As DAO.Recordset
     Dim sFile As String
     
     sFile = Replace(References("DAO").FullPath, "OFFICE14\ACEDAO.DLL", "DAO\dao360.dll")
     
     Set oDbEng = GetCOMInstance(sFile, _
                     "{00000100-0000-0010-8000-00AA006D2EA4}", True)
     ''''oder statt CLSID: DBEngine ~ DAO.DBEngine.36
     
     Set oWrk = oDbEng.CreateWorkspace("", "", "", dbUseODBC)
     sConx = "ODBC;Driver={MySQL ODBC 5.3 ANSI Driver};" & _
         "DATABASE=mysqlbackend;" & _
         "SERVER=127.0.0.1;PORT=3306;" & _
         "UID=root;PWD=aiupwd;OPTION=18475"
     Set oConx = oWrk.OpenConnection("", dbDriverNoPrompt, , sConx)
     ''''oder dbDriverCompleteRequired
     Set rs = oConx.OpenRecordset( _
       "SELECT ID,Artikel FROM tblArtikel", dbOpenDynaset)
     Do While Not rs.EOF
         Debug.Print rs(0).Value, rs(1).Value
         rs.MoveNext
     Loop
     rs.Close
     
     On Error Resume Next
     oWrk.Close
     Set oWrk = Nothing
     oConx.Close
     Set oConx = Nothing
     Set oDbEng = Nothing
End Function

Listing 1: Diese Prozedur erzeugt eine Instanz der DBEngine und spricht damit einen MySQL-Server an.

Den Connect-String sConx verweist in diesem Beispiel auf einen MySQL-ODBC-Treiber. Der Server wird über den Standard-Port 3306 auf die lokale Maschine mit der IP 127.0.0.1 angesprochen und der Benutzername zur Autorisierung am Server ist root, das Passwort ist aiupwd. Die Connection oConx wird über diesen String geöffnet.

Interessant ist in diesem Zusammenhang die Konstante dbDriverNoPrompt. Sie sagt ODBCDirect, dass kein Dialog erscheinen soll, wenn etwas am Connect-String nicht stimmt. In diesem Fall käme es zu einer Fehlermeldung.

Setzten Sie stattdessen dbDriverCompleteRequired ein, so erscheint der ODBC-Dialog des Datenquellenadministrators von Windows. In dem sind dann die korrekten Angaben zur Verbindung vorausgefüllt. Lassen Sie etwa den Benutzernamen weg, so können Sie diesen dann im Dialog nachreichen.

Schließlich öffnet sich ein Recordset rs auf die Tabelle tblArtikel auf die Connection. In der folgenden Schleife werden dann die Werte der Felder ID und Artikel der Datensätze alle im VBA-Direktfenster ausgegeben. Zu erwähnen wäre, dass diese Methode erheblich performanter ist, als ADO über ODBC auf den MySQL-Treiber. Es gibt nämlich keinen funktionierenden kostenlosen MySQL– oder MariaDB-OLEDB-Provider.

dBase über ODBCDirect

Noch ein Feature ist ab Access 2013 dem Rotstift anheimgefallen: die Schnittstelle zu dBase. Bis dahin konnte man noch über das Ribbon und Externe Daten dBase-Dateien sowohl importieren, wie verknüpfen oder exportieren. Das geht unter Umständen eben nicht mehr, obwohl die Schnittstelle im neuesten Access 2016 wieder drin ist. dBase ist als Austauschformat und Schnittstelle zu manchen anderen Programmen (leider) noch immer aktuell.

Der Umweg über eine DSN, welche Sie auf dBase-Dateien im ODBC-Datenquellen-Administrator anlegen, klappt ebenfalls nicht. Der Versuch, darauf eine ODBC-Verknüpfung in Access aufzubauen scheitert mit der Meldung Verknüpfen von externen ISAM-Datenbanktabellen mit Ihrer Datenbank über ODBC ist nicht möglich – warum auch immer das so unsinnigerweise vorgesehen wurde. Die einzige Lösung ist hier in VBA ODBCDirect im Verein mit GetCOMInstance. Auch eine Passthrough-Abfrage auf den ODBC-Treiber funktioniert nicht, wie die Abfrage qry_PT_dBase der Demodatenbank zeigt.

Kleiner Tipp am Rande: Unter Windows 64 existiert wohl schon im Startmenü ein Link zum ODBC-Datenquellenadministrator. Doch dieser öffnet die 64bit-Version desselben, in der demnach auch nur 64bit-Treiber aufgelistet sind. Mit diesen kann Office x32 nichts anfangen. Legen Sie sich deshalb eine Verknüpfung zur Version unter SysWOW64 auf dem Desktop an (C:\Windows\SysWOW64\odbcad32.exe). Sie sollte nach dem Start den Dialog ähnlich zeigen, wie in Bild 2.

Bild 2: Der 32bit-ODBC-Datenquellen-Administrator listet die 32bit-Treiber auf.

Sie können nun eine neue Benutzer-DSN zu dBase-Dateien anlegen, indem Sie unter dem entsprechenden Reiter die Schaltfläche Neu anklicken. In Bild 3 ist der Dialog nach Auswahl des dBase-Treibers ausgefüllt. übrigens können wir auch nicht garantieren, dass der Treiber auch wirklich auf Ihrem System installiert ist. Im Zweifel können Sie das nachholen, indem Sie das Microsoft ODBC Desktop Driver Pack installieren. Infos zum dBase-Treiber gibt es dann etwa in den Microsoft Docs.

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

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo