code it

Martins Tech Blog

Umgang mit Multivalued Fields in Importen

Ein Feature von Access 2007, das wohl besonders die Umschwenker von Excel freut, sind Multivalued Fields. Man kann damit innerhalb einer Tabellenspalte eine Mehrfachauswahl abbilden - etwas wofür man im Datenbankdesign wie wir es alle kennen mindestens 3 Tabellen verwenden würden. Und sie sind ja auch so einfach zu erstellen: Man muss einfach nur im Tabellendesigner bei Mehrere Werte zulassen den Haken setzen.

So bequem dieses Feature für Endanwender ist, so unbequem ist es für Entwickler, denn man muss schon sehr schmerzresistent sein, wenn man auf eine Tabelle mit einem Multivalued Field trifft. Man könnte schon fast sagen, man hat es hier mit einer kleinen Prinzessin zu tun, die auch wie eine solche behandelt werden möchte.

Aktuelles Beispiel für diese These ist der Import solcher Felder. Das Szenario ist leicht erklärt: Eine Anwendung auf der Basis von Access 2007 bekommt ein neues Feature und das Datenmodell ändert sich insofern als dass ein neues Feld in der Tabelle notwendig wird. Schön für den Endanwender ist es nun, wenn es eine Importroutine gibt, die die Daten aus der alten Datenstruktur in die neue Struktur übernehmen kann. An und für sich ist das mit Access kein Problem, denn Access kennt ja Remote Queries:

Private Sub Import(source As String)
  CurrentDb.Execute "INSERT INTO SimpleTable SELECT * FROM SimpleTable IN """ & source & """"
End Sub

Das geht so lange gut, bis man auf eine Tabelle mit einem Multivalued Field trifft, denn plötzlich sind Remote Queries nicht mehr möglich.

Einen entsprechenden Versuch wird Access nicht dulden.

Dabei ist diese Meldung unabhängig davon, ob man das Multivalued Field wirklich mit abfragt oder nicht - man bekommt immer wieder diese Meldung.

Nun gibt es in meinen Augen 3 Lösungsansätze:

  • Der Kunde gibt einfach alle Daten nochmal manuell ein. Ok, dazu wird er sicher nicht bereit sein, aber anscheinend ist es das was in dem Fall die naheliegendste Lösung ist, wenn sich Access so hartnäckig sträubt.
  • Man könnte über Externe Daten eine Tabelle der alten Datenbank mit der neuen Datenbank verknüpfen und hätte damit dann ein INSERT-INTO-Statement, das sich nur auf die aktuelle Datenbank bezieht, da die verknüpfte Tabelle sich wie eine in der Datenbank befindliche Tabelle verhält. Schön automatisieren lässt sich das aber auch nicht. Das wäre also der halbautomatische Weg.
  • Möchte man den vollautomatischen Weg gehen, kommt man wohl nicht drum herum, die Daten feldweise zu übernehmen und mit Recordsets zu hantieren.

Für Tor Nummer 3 hab ich mich letztendlich auch entschieden: 

Private Sub Import(source As String)
   Dim wrksp As DAO.Workspace
   Set wrksp = DBEngine.Workspaces(0)
 
On Error GoTo Err_Import
   ' open transaction
   wrksp.BeginTrans

   ' import a simple table
   CurrentDb.Execute "INSERT INTO SimpleTable SELECT * FROM SimpleTable IN """ & source & """"

   ' import table with multivalued field
   Dim otherDB As DAO.Database, otherRS As DAO.Recordset, currentRS As DAO.Recordset
   Dim i As Long
 
   ' open recordsets to read data from old database and append it to current database
   Set otherDB = DBEngine.OpenDatabase(source)
   Set otherRS = otherDB.OpenRecordset("MVFTable")
   Set currentRS = CurrentDb.OpenRecordset("MVFTable")

   ' loop records in old data and add a new record in current db for each record in old db
   While Not otherRS.EOF
       currentRS.AddNew
       ' iterate through the old fields and update the value in current record
       For i = 0 To otherRS.Fields.Count - 1
           If otherRS.Fields(i).Name <> "MFVField" Then
               currentRS(otherRS.Fields(i).Name) = otherRS(otherRS.Fields(i).Name)
           End If
       Next
       currentRS.Update
       otherRS.MoveNext
   Wend
 
   ' close the recordsets
   currentRS.Close
   otherRS.Close
 
   ' open a recordset for the values of the multi valued field
   Set otherRS = otherDB.OpenRecordset("SELECT Id, MVFField.Value FROM MVFTable")
   While Not otherRS.EOF
       If Not IsNull(otherRS.Fields(1)) Then
           ' insert selected values of the multivalued field
           CurrentDb.Execute "INSERT INTO MVFTable (MVFField.Value) VALUES (" & otherRS.Fields(1) & ") WHERE Id = " & otherRS.Fields(0)
       End If
      otherRS.MoveNext
   Wend

   ' close the recordset
   otherRS.Close
 
   ' commit transaction
   wrksp.CommitTrans
 
   MsgBox "Import erfolgreich beendet"
   Exit Sub
 
Err_Import:
   ' rollback transaction
   wrksp.Rollback
   MsgBox "Import ist fehlgeschlagen"
End Sub

Wie man gut sehen kann, muss man bei dieser Lösung statt eines einzigen SQL Statements (im Beispiel der Import von SimpleTable) eine ganze Routine schreiben und die Tabelle mehrfach anfassen, weil das MultiValued Field quasi eine Tabelle in der Tabelle ist und mit einer speziellen Syntax befüllt werden möchte.

Ich hätte mir gewünscht, dass Features, auch wenn sie nicht Standard-SQL-konform sind, sich doch zumindest so verhalten, wie man es vom Rest der Applikation gewohnt ist - und dazu gehört in meinen Augen auch die Möglichkeit, bei Multivalued Fields Remote Queries zuzulassen. 

In Microsoft Access ein eigenes Ribbon erstellen

Seit Office 2007 glänzt Microsoft Access mit Ribbons statt klassischer Menüs. Die Ribbons sind erweiterbar und mit Hilfe von etwas XML kann man recht einfach eigene Ribbons erstellen.

Die Konfiguration wird in einer neuen Systemtabelle gespeichert. Dazu muss zunächst einmal sichergestellt sein, dass in den Navigationsoptionen der Haken bei Systemobjekte anzeigen gesetzt ist. 

 

Nun legt man eine neue Tabelle mit dem Namen USysRibbons an, mit folgenden Feldern:

NameDatentyp
RibbonName Text
RibbonXml Memo

Da der Ribbonname eindeutig sein muss, kann das entsprechende Feld auch gleich als Primärschlüssel verwendet werden - es spricht aber auch nichts dagegen, noch ein weiteres Feld (z.B. Id) anzufügen und dieses als Primärschlüssel zu definieren.

Nun kann man in die Tabelle seine Ribbon-Definition eintragen. Wie das XML genau aussehen muss, entnimmt man dabei am besten der MSDN. Eine sehr gute Seite zu dem Thema ist auch accessribbon.de. Möchte man das XML nicht manuell erstellen, so können hier die Visual Studio Tools for Office (Export Ribbon to Xml) oder der Ribbon-Creator helfen.

Nachdem man den Datensatz angelegt und das Ribbon in den Access-Optionen als Standard definiert hat, wird nun ab dem nächsten Start derDatenbank das eben erstellte Ribbon angezeigt.

Ein kleiner Tipp noch am Rande: Problemlos können die bereits implementierten Bilder verwendet werden. Dazu muss nur im Tag imageMso ein gültiger Wert angegeben werden. Leider gibt es dafür keine offizielle Dokumentation von Microsoft. Welche Werte gültig sind, kann man ganz leicht selbst ermitteln, indem man in den Einstellungen der Schnellzugriffsleiste mit der Maus über die verfügbaren Symbole fährt. Im Tooltipp steht die Id des Bildes in Klammern dahinter.

Tabellen und Abfragen einer Access-OleDbConnection ermitteln

Möchte man dem Endanwender selbst die Entscheidung überlassen, welche Datenbankobjekte für eine bestimmte Operation relevant sind, so bietet es sich an, diese als Auswahl bereitzustellen und so Fehleingaben und Frust bei manueller Eingabe zu vermeiden. Das Beispiel soll zeigen, wie alle Tabellen und Abfragen einer Access 2003 Datenbank ermittelt werden können. Schnell ist herausgefunden, dass die Methode GetOleDbSchemaTable diese Aufgabe unterstützen kann - aber wie geht man damit genau um?

Tabellen ermitteln
Zunächst gilt es, alle Tabellen zu ermitteln. Im einfachsten Fall ruft man dafür

DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

auf, wobei "connection" eine bestehende OleDbConnection auf die fragliche Access-Datenbank ist.

Das Ergebnis ist ein DataTable. Die Spalte "TABLE_TYPE" ermöglicht eine Filterung auf die verschiedenen Objekttypen. Dabei haben Benutzertabellen den Typ "TABLE", verknüpfte Tabellen den Typ "LINK". Diese Filterung kann auch gleich beim Aufruf der Funktion durchgeführt werden, indem der zweite Parameter der Funktion GetOleDbSchemaTable mit befüllt wird. Der folgende Aufruf gibt die DataTable gefiltert nach dem Typ "TABLE" zurück.

DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] { null, null, null, "TABLE" });

Abfragen ermitteln
Für die Ermittlung der Views gibt es mehrere Möglichkeiten. Die erste sticht direkt ins Auge - ein Aufruf von

DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] { null, null, null, "VIEW" });

Eine weitere Möglichkeit ist, "GetOleDbSchemaTable" mit dem Parameter "OleDbSchemaGuid.Views" aufzurufen. Dabei hat der zweitgenannte Aufruf den Vorteil, dass hier die Definition der Abfrage - also der SQL-Text mit abgerufen wird.

DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Views, null);

Bei näherer Betrachtung fällt aber auf, dass hier nicht alle Abfragen enthalten sind. Es fehlen Abfragen, die einen Parameter erwarten sowie Union- und Kreuztabellen-Abfragen. Die Lösung dieses Problems ist ein weiterer Aufruf von "GetOleDbSchemaTable" mit dem Parameter "OleDbSchemaGuid.Procedures". Darin enthalten sind alle Abfragen, die solche Besonderheiten haben.

 

Dabei erscheinen auch interne Abfragen mit. Das sind SQL-Statements, die direkt in das Feld Datenquelle eingegeben wurden ohne vorher eine Abfrage zu definieren - diese beginnen mit "~sq_" und sollten für externe Belange irrelevant sein. Bei den anderen Einträgen kann anhand selbst gewählter Kriterien entschieden werden, ob diese für externen Zugriff relevant sind oder nicht. 

Das Ergebnis 
Alle diese Erkenntnisse zusammengenommen, könnte der resultierende Quellcode ungefähr wie folgt aussehen:

private List GetDbObjectNames(string dbPath)
{
    List dbObjectsList = new List();

    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath;

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        DataTable schemaTable = null;
        
        // tables
        schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] { null, null, null, "TABLE" });
        foreach (DataRow row in schemaTable.Rows)
        {
            dbObjectsList.Add(row["Table_Name"] as string);
        }

        // linked tables
        schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "LINK" });
        foreach (DataRow row in schemaTable.Rows)
        {
            dbObjectsList.Add(row["Table_Name"] as string);
        }

        // views
        schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Views, null);
        foreach (DataRow row in schemaTable.Rows)
        {
            dbObjectsList.Add(row["Table_Name"] as string);
        }

        // special views
        schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, null);
        foreach (DataRow row in schemaTable.Rows)
        {
            if (!2.Equals(row["Procedure_Type"]))
                continue;
            if (((string)row["Procedure_Name"]).StartsWith("~sq_"))
                continue;
            dbObjectsList.Add(row["Procedure_Name"] as string);
        }


        connection.Close();
    }


    return dbObjectsList;
}


private void loadObjectsButton_Click(object sender, EventArgs e)
{
    dbObjectsList.Items.Clear();
    List items = GetDbObjectNames("C:\\Program Files\\Microsoft Office\\OFFICE11\\SAMPLES\\nordwind.mdb");
    dbObjectsList.Items.AddRange(items.ToArray());
}

 

Laufzeitfehler 3049 beim Import von Daten in Access 2003

Vor die Aufgabe gestellt, mehrere hundert csv-Dateien analysieren, hab ich mich dafür entschieden, diese in eine Access-Datenbank zu importieren. Access bietet die integrierte Funktion DoCmd.TransferText, um CSV-Daten einfach einzulesen. Also schnell einen Fünfzeiler geschrieben, der durch alle Dateien des Ordners iteriert und für jede Datei DoCmd.TransferText aufruft. 

Nach einigen Durchläufen bekam ich allerdings "Laufzeitfehler 3049" mit der vielsagenden Meldung "Datenbank '' konnte nicht geöffnet werden". Aber welche Datenbank eigentlich? Ich importiere doch aus Textdateien und wieso steht da eigentlich kein Name? Glücklicherweise bietet mir der Dialog ja die Möglichkeit, zu debuggen. Und einmal im Sourcecode konnte ich auch schnell herausfinden, welche Datei gerade importiert werden soll - vielleicht hatte ja nur der Entwickler der Funktion im Office-Team vergessen, den Dateinamen mit auszugeben. Die Datei war dann auch schnell im Dateisystem gefunden und ich öffnete sie, um sie zu prüfen. Ergebnis: Sie war nicht gesperrt und auch im richtigen Format.

Nach einiger Zeit kam ich dann auf die rettende Idee: Vielleicht handelt es sich ja wirklich um eine Datenbank und nur der Rest der Fehlermeldung ist verwirrend. Eine Prüfung der Access-Datenbank zeigte: Die Datei war mittlerweile an die Grenzen ihrer Möglichkeiten gelangt und zu einer Größe von 2 GB angewachsen. Da diese Größe bei mir hauptsächlich daraus resultierte, dass ich zuvor einen Testlauf durchgeführt hab und zwischendurch nicht komprimiert hatte, konnte ich das Problem sehr schnell lösen durch einen Aufruf von Datenbank komprimieren und reparieren. 

Problem gelöst - aber: Eine aussagekräftigere Fehlermeldung hätte mich schneller zum Erfolg gebracht....

Ungültige Werte für Eingabeparameter?

Nach längerer Zeit hab ich mich nun auch mal wieder mit einem MS-Access-Problem auseinander gesetzt. Mein bestehendes Projekt besteht aus einer ADP im Frontend und einer Datenbank auf Basis von SQL-Server 2005 im Backend.

Einige der Anwender bekamen folgenden Fehler, wenn sie Daten in ein Formular eingeben wollten:

1. Lösungsversuch – Ist die Datengrundlage aktualisierbar?:

Die Datengrundlage des betroffenen Formulars ist eine Sicht aus mehreren Tabellen, die mittels INNER JOIN verknüpft sind. Wenn hier die Einstellungen nicht korrekt sind, ist die Sicht nicht aktualisierbar. Ich öffnete die Sicht also im SQL-Server und versuchte Daten einzugeben. Nachdem ich alle NOT-NULL-Felder ausgefüllt hatte, wurde der Datensatz in meiner Tabelle erfolgreich angelegt. Daran konnte es also nicht liegen.

2. Lösungsversuch – Stimmen die Eigenschaften im Formular?

Wenn man mit aktualisierbaren Sichten als Datengrundlage arbeiten möchte, ist es wichtig, dass a) die Primärschlüsselspalten mit in der Sicht sind und b) die Eigenschaft UniqueTable des Formulars auf die Tabelle gesetzt ist, in die Access die Daten einfügen soll. Aber auch das war korrekt eingestellt. Wieder nichts… Aber gegen diese generellen Probleme sprach auch, dass es bei einigen Nutzern funktioniert und nur bei einigen anderen nicht.

3. Lösungsversuch – Ist an den Eingabeparametern wirklich was falsch?

Die Meldung sagt, dass Eingabeparameter falsch sind. Und dass ich in Statuswerten nähere Informationen finde. Leider sagt sie eben nicht, wo ich die Statuswerte finde. Also machte ich mich auf dem herkömmlichen Weg auf die Suche nach ungültigen Eingabeparametern. Vielleicht geben die Nutzer wo es auftritt ja im Formular etwas anderes an und der Fehler ist nicht korrekt abgefangen. Per Fernwartung schaute ich beiden Benutzern bei der Eingabe auf den Monitor und konnte keine Unterschiede feststellen. Alles war ausgefüllt und alles waren logische Werte. Trotzdem kam die Meldung. Wieder Sackgasse…

4. Lösungsversuch – Unterschiedliche Nutzer = unterschiedliche Berechtigungen?

Unterschiedliche Nutzer können ja auch unterschiedliche Rechte auf Datenbankobjekten haben. Also prüfte ich die Berechtigungen auf dem SQL-Server. Hier gab es einen Unterschied: Die Benutzer die keine Probleme hatten, waren in der Rolle db_owner für diese Datenbank. Die anderen waren in einer eigenen Rolle, die auf den Tabellen SELECT-, INSERT-, UPDATE- und DELETE-Berechtigung hatten. Da nicht alle Benutzer in die Gruppe db_owner genommen werden sollten, galt es nun, herauszufinden, welche Berechtigung noch notwendig ist, damit Access Inhalte einfügen kann. Nach einer kurzen Probierphase hatte ich die Lösung:

Benutzer, die über eine Sicht in einem Access-Formular Daten eingeben sollen, müssen auf der zugrundeliegenden Tabelle zusätzlich die Berechtigung “View Definition” haben, damit Access die Werte korrekt einfügen kann.