View Full Version : ADODB.Recordset in VBA und Unicode-Daten
Hallo,
ich versuche gerade Unicode-Daten von der AS/400 per VBA über eine QueryTable in Excel zu bringen. Die normalen Zeichen werden korrekt dargestellt. Die Sonderzeichen (wie z.B. kyrillisch) werden nur als Quadrate dargestellt. Der VBA-Code sieht folgendermaßen aus:
Set AS400 = New ADODB.Connection
AS400.Open "ConnectionString"
Dim qt As QueryTable
Dim rs As New ADODB.Recordset
' Recordset mit Abfrage-Ergebnis füllen
Set rs = AS400.Execute(sql)
' Recordset in Excel-Sheet ausgeben
Set qt = Sheets(blatt).QueryTables.Add(rs, Sheets(blatt).Range(zelle))
qt.Name = "AS400_Query"
qt.FieldNames = False
qt.RowNumbers = False
qt.FillAdjacentFormulas = True
qt.PreserveFormatting = True
qt.RefreshOnFileOpen = False
qt.BackgroundQuery = False
qt.RefreshStyle = xlInsertDeleteCells
qt.SavePassword = True
qt.SaveData = True
qt.AdjustColumnWidth = False
qt.RefreshPeriod = 0
qt.PreserveColumnInfo = True
qt.Refresh
Wenn ich nicht über einen ADODB.Recordset gehe, sondern beim QueryTables.Add eine neue ODBC-Verbindung angebe, funktioniert's problemlos. Nur wird hier halt immer eine neue ODBC-Verbindung aufgebaut, was ich vermeiden wollte.
Ist der ADODB.Recordset etwa nicht unicodefähig?
Gruß,
KM
Dieser Weg ist zu kompliziert.
Wenn ADO, dann mach den SQL auch komplett selber (Connect, Execute, Blatt füllen).
ADO ist natürlich Unicodefähig, der Feldtyp ist hierfür adWChar oder adVarWChar.
Einfacher ist es erst mal, das Ganze über MS-Query zu machen.
Die Vorschau von MS-Query kann Unicode leider nicht anzeigen, da hier kein Unicod-Font verwendet wird.
Im Excel stehen die Daten dann aber korrekt, wenn denn die Quelle auch Unicode ist.
Was ist hier bitte zu kompliziert? Ich fülle das Recordset mit einem Connection.Execute und das Recordset verwende ich als Input für meine QueryTable. Einfacher geht's doch gar nicht.
Ich hab jetzt mal von QueryTable umgestellt auf CopyFromRecordset, um die Daten des Recordset ab einer bestimmten Zelle in Excel einzufügen. Die Daten werden auch eingefügt, sie liegen aber je nach Treiber in einer unterschiedlichen Form vor.
Wenn ich den ODBC-Treiber verwende, werden die Unicode-Sonderzeichen als kleine Quadrate eingefügt. Ein Datumsfeld wird hingegen korrekt interpretiert.
Wenn ich den OLE-DB Treiber (IBMDA400 oder IBMDASQL) verwende, werden die Unicode-Sonderzeichen korrekt eingefügt, jedoch kommt dieser Treiber offenbar nicht mit Datumsfeldern zurecht. Stattdessen werden nur irgendwelche Zeichen eingefügt.
Dies zeigt, dass das Recordset an sich schon unicodefähig ist.
Wie kann ich mein Problem nun lösen und alle Daten korrekt importieren?
Gruß,
KM
Weil du mit MS-Query (Import Daten) nichts programmieren brauchst und das QueryDef automatisch erstellst.
Du benötigst nur ein DSN über den ODBC-Manager.
Die restlichen Eigenschaften definierst du dann über die Query-Eigenschaften im Excel.
Bei mir hat das bisher immer problemlos funktioniert.
Also erstens ist mir der Umweg über MS Query viel zu umständlich. Außerdem will ich ja nicht manuell Daten importieren, sondern die Datenübertragung soll variabel gehalten werden. Deshalb will ich diese Funktionalität über entsprechende Prozeduren in einem Excel-AddIn den Benutzern zur Verfügung stellen. Ich kann auch nicht sicher davon ausgehen, dass bei den Benutzern bereits DSNs definiert sind. Deshalb stelle ich die ODBC-Verbindung bzw. OLEDB-Verbindung über einen Connection-String her.
Das Problem ist jetzt also, dass je nach Verbindungstyp (ODBC oder OLEDB) die Feldtypen im Resultset anders sind. Ich hatte inzwischen auch schon versucht das Resultset vorher mit den entsprechenden Felddefinitionen zu erstellen. Jedoch werden diese beim Füllen des Resultsets wieder überschrieben.
Die einzige Methode, die für alle Feldtypen funktionierte, war als ich beim QueryTables.Add den ODBC-Connection-String direkt angegeben habe. Das hat halt zur Folge, dass jedesmal eine neue ODBC-Verbindung hergestellt wird, was ich nicht unbedingt haben möchte.
Gibt es sonst noch irgendwelche Möglichkeiten?
Gruß,
KM
Dann würde ich das QueryDef so füllen, wie es MS-Query auch macht und das ausführen und laden dann Excel selber machen lassen, also neben der Verbindungsfolge auch den SQL-String eintragen.
Somit wird das AddIn nicht mehr benötigt, wenn die Daten nur aktualisiert werden sollen.
Nein, das bringt mir so nichts. Wie bereits erwähnt soll die Importfunktion völlig variabel gehalten werden. Das heißt es können völlig unterschiedliche SQL-Abfragen sein, deren Results an völlig unterschiedliche Positionen im jeweiligen Excel-Sheet eingefügt werden können. Diese Funktionalität soll ja nicht auf eine Excel-Tabelle beschränkt sein, sondern sie soll wiederverwendbar sein. Deshalb das AddIn.
So wie ich das verstanden habe, nützt mir die vorherige Definition der Feldtypen im Resultset nichts, da diese beim Füllen des Resultsets sowieso wieder überschrieben werden. Ist das so korrekt? Und die Feldtypen selbst werden bestimmt durch die zugrunde liegende Engine (ODBC oder OLEDB). Das Problem ist also nur (wie oben schon beschrieben), dass der ODBC-Treiber bei Unicode-Daten den Feldtyp adVarChar anstatt adVarWChar generiert, während der OLEDB-Treiber bei Datumsfeldern den Feldtyp adChar anstatt adDate.
Kann man das irgendwie beeinflussen bzw. ändern?
Gruß,
KM
Das kann ich fast nicht glauben.
Die Feldtypen eines Recordsets werden natürlich durch das Ergebnis eines SQL's gesetzt.
Schließlich zerstörst du ja das Recordset mittels:
set myRcd = myCnn.Execute(...)
oder
myRcd.Open MySql, MyCnn ...
Bisher hatte ich mit Unicode diesbezüglich auch bei ODBC keine Probleme.
Hast du das Recordset daraufhin mal analysiert?
Beim OLEDB-Treiber muss man für "Datum" explizite Eigenschaften in der Verbindung setzen (siehe Doku des OLEDB-Treibers).
Allerdings habe ich da für ODBC noch einen Hinweis gefunden:
Unicode fields, which are the data types with a CCSID of 1200 (UTF-16) or 13488 (UCS-2), report to ODBC 2.x applications as SQL_CHAR, SQL_VARCHAR, and SQL_LONGVARCHAR instead of SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR.
ODBC data types and how they correspond to DB2 UDB database types (http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzaik/typemapping.htm)
Woran der Treiber nun allerdings den Typ (2.x oder 3.x) erkennt weiß ich nun auch nicht.
Zumindest bist du mit dem Problem nicht alleine (ich habe das bisher tatsächlich nicht gemerkt).
Die Konvertierung erfolgt bereits durch den ODBC-Treiber, da der MSDASQL-OLEDBTreiber wohl noch aus ODBC 2.x-Zeiten stammt.
Ich habe auch keine Einstellung für ODBC 3.x gefunden.
Dir bleibt wohl nur der IBMDASQL (IBMDA400 ist der selbe Treiber).
Excel selber meldet sich wohl als ODBC 3.x am Treiber an, so dass nicodedaten korrekt geladen werden.
Fülle doch das QueryDef auch mit dem SQL und lass Excel die Daten selber laden.
Und nun noch ein paar Ergänzungen:
Der MSDASQL-OLEDB-Treiber stammt noch aus Zeiten vor ODBC 3.51. Erst ab ODBC 3.51 wird auch Unicode beim Lesen unterstützt.
Für das Senden von Unicode ist das ja kein Problem, da du ja Parameter gezielt mit adVarWChar definieren kannst.
Laut SQL-Trace bindet der MSDASQL leider bei einem Recordset mit adChar so dass Unicode eben verloren geht.
Excel selber verwendet die neu Jet-Engine 4.0 mit SQL-Pasthru-Abfragen.
Hier funktioniert das Binden also mit adVarWChar so dass Unicode funktioniert.
Um also dein AddIn dynamisch zu machen, füllst du die Connection-Egenschaft des QueryTable-Objekts mit "ODBC;Verbindungsfolge", den CommandText und wendest die Methode Refresh an.
Vor mehreren Verbindungen brauchst du keine Angst zu haben, da der ConnectionPool standardmäßig aktiviert ist.
Bei der Verwendung des IBMDA400/IBMDASQL muss leider in der Verbindungsfolge die Konvertierung von DateTime gezielt ausgeschaltet werden:
IBM i Support: Software Technical Document : 23062121 (http://www-912.ibm.com/s_dir/SLKBase.nsf/0/c52e46013ce1ded286256a3b006a38c5?OpenDocument)
Convert Date Time to Char = False;