Office: (Office 2013) BackEnd-DB updaten per VBA

Hallo Forum,

die Anwendung ist draußen beim Kunden. Nun gibt es neue Anforderungen, die Änderungen am Datenmodell erfordern. Natürlich ist die Anwendung in FE und BE getrennt. Ich möchte nun die bei einem Update erforderlichen BE-Updates per VBA-Script vom FE aus durchführen lassen. Die Vorteile liegen auf der Hand:

• Ich muss nicht bei jedem Update die BE-Dateien aller Kunden einsammeln und manuell ändern.
  Ich kann die BE-Updates ausführlich testen und damit Fehler bei der manuellen Anpassung des BE ausschließen.

Per VBA kann ich ganze Tabellen hinzufügen oder vorhandenen Tabellen Felder hinzufügen:
Code:

Wie wir sehen bietet die CreateField-Methode nur die Möglichkeit, den Datentyp zu definieren. Ich möchte aber alle Eigenschaften des Feldes setzen können. Bei einem Ja/Nein-Feld ist zum Beispiel der Standardwert interessant, bei anderen Datentypen "Eingabe erforderlich" oder "Anzahl Dezimalstellen" etc.. Bis hin zur Beschreibung.

Wie kann ich alle Eigenschaften eines Datenbank-Feldes per VBA-Code setzen?

Herzlichen Dank für alle hilfreichen Anregungen!

Viele Grüße
Michael


PS:
Früher habe ich u. a. mit Oracle als DBMS gearbeitet. Im Entwicklungsprozess haben wir unserem DBA alle Änderungen an der Datenbank-Struktur als DDL-Scripts zur Verfügung gestellt. Wenn ich mich richtig erinnere konnte Oracle diese Scripts generieren. So war man bei einem neuen Deployment immer auf der sicheren Seite.
Da MS Access dieser Anforderung - bei aller Wertschätzung - nicht gerecht wird und ich auch noch kein Tool gefunden habe, das eine Differenz-Analyse zweier Datenbank-Stände durchführt, ist wohl ein wenig Handarbeit angesagt. Das Objekt-Modell von Access bietet eigentlich auch alle Möglichkeiten - wenn man nur alle Möglichkeiten verstehen würde....

:)

 

Hallo,

die Eigenschaften sollten alle in der DAO.Field.Properties-Auflistung stecken. Die Properties selber existieren in Abhängigkeit von den gemachten Einstellungen. Zum Bespiel existiert eine Property "Description" erst, nachdem im Entwurfsmodus eine Beschreibung eingegeben worden ist.

Hinweis: je nach Anwendung, kann es auch benutzerdefinierte Properties geben!

Ulrich

 

Tatsächlich ist es ein bisschen komplizierter als zunächst angenommen. Nicht alle Properties stehen sofort zur Verfügung und müssen deshalb im Code hinzugefügt werden. Ich habe das in meinem Code bereits berücksicht.


Code:

Der Aufwand ist also gigantisch und die Fehleranfälligkeit groß, um nur ein einziges Feld zu einer Tabelle hinzuzufügen. Und dabei gelingt es mir noch nicht einmal, den DefaultValue des Ja/Nein-Feldes korrekt zu setzen!

Was wären nun die Alternativen bei unveränderter Ausgangslage?
- Die neue Tabellen-Struktur in die Datenbank kopieren (TransferDatabase) und anschließend die Daten aus der alten in die neue Struktur übertragen? Aber was passiert dann mit den AutoWert-Feldern? Wenn diese neu vergeben werden stimmen die Relationen nicht mehr!

Wie auch immer:
wer steht vor einer ähnlichen Herausforderung und hat eine Idee, wie wir damit umgehen können?

Viele Grüße
Michael

 

Hallo Michael,

Bei den Properties muß du schon auf den Property.Type achten. Für das Ja/Nein Feld gilt: Properties("DefaultValue").Typ = (12) - dbMemo, d.h. die Value Eigenschaft ist vom Datentyp Variant/String! Demnach muß der Wert "false" als String übergeben werden und das ist in der lokalen Variante entsprechend "Falsch".
Wenn man sich nicht sicher ist, gibt es die Funktion Application.BuildCriteria(), mit der man das im Direktfenster auch ausprobieren kann:
Code:

Das Setzen/Erstellen der Properties kann auch mit einer eigenen Funktion gemacht werden, um sich die Arbeit etwas zu erleichtern.

Grundsätzlich würde ich bei einem Update zwischen einem Patch und einer Migration unterscheiden. Beim Patch einfach das bestehende Schema verändern und bei einer Migration tatsächlich eine Datenübernahme machen.
Ob TransferDatabase dafür geeignet ist, müßte man prüfen. Aus dem stehgreif weiß ich jetzt nicht, wie sich das dann mit den Relationen und den Indizes verhält. AutowertFelder kann man übernehmen.

Vielleicht gibt es einen Sinn, eine Patch-Steuerdatei zu entwickeln, in der alle Änderungen als Kommandos erfasst und dann von deinem Tool abgearbeit werden.

Gruß Ulrich

 

Hallo Michael,
Mag sein, dass ich was übersehen habe, aber wenn du ein Feld löscht
Code:

gehen auch alle Inhalte verloren. Dann erstellst du das Feld neu und
setzt den Standardwert, der nur bei neuen DS eingetragen wird.
Es muss doch Ziel sein, die erfassten Daten zu erhalten.
Ich würde das Update mit einer Anfügeabfrage in eine leere Tabelle mit
der neuen Struktur realisieren. Danach die alte Tabelle löschen und die
neue umbenennen.
gruss ekkehard

 

Ich bin schon wieder mit meinen Antworten im Rückstand, sorry...

@Ulrich
Tatsächlich erhalte ich bei DefaultValue mit "Falsch" einen Datentypenfehler, mit "false" läuft der Code durch.
Allerdings zeigt Access in der Tabelle Unterschiede zwischen der manuellen und der Code-Version:
Property Wert manuell Wert Code
Format Ja/Nein leer (zeigt allerdings Checkboxen an
Standard-Wert Nein Falsch

So richtig überzeugt mich das noch nicht...

@Ekkehard
Du hast natürlich vollkommen recht - das Löschen führe ich nur durch, damit ich meine Tests beliebig oft wiederholen kann. Denn ich bin noch im Stadium des Experimentierens. Allerdings: wenn ich einer Tabelle ein Feld neu hinzufügen kann es dafür noch keine erfassten Daten geben.

Die Vorteile Deiner Vorgehensweise liegen auf der Hand:

• Ich kann die neue Tabellenstruktur wie gewohnt manuell vorbereiten, das ganze VBA-Gehampel entfällt ersatzlos.manuell
• Die Anfügeabfrage ist einfach
  (z. B.: INSERT INTO REZEPT_neu
  SELECT REZEPT.*
  FROM REZEPT*wink.gif*
  
  Lösen muss ich allerdings zwei Fragen:
  • Wie kopiere ich die neue Tabellenstruktur in die Datenbank (TransferDatatbase?)?
  • Was passiert mit den vorhandenen Relationen???
  
  Ich denke, diese beiden Fragen muss ich beantworten, bevor ich weiter in diese Richtung denken kann.
  
  Viele Grüße und besten Dank für die lebhafte Diskussion
  Michael

 

\@Michael O.,
nimm einfach ein Feld mit gesetztem Standardwert, dann siehst du ja wie die Property aussehen muss und das der Wert Englisch drinnen steht.

Das erstellen einer neuen Tabelle und einfügen, halte ich persönlich für Unsinn - allein schon deswegen, weil Access Daten erst beim Komprimieren löscht.
Ob man die Änderungen per DDL oder per Tabledef durchführt ist dabei Geschmackssache. Wichtig ist nur zu wissen: Manche Dinge gehen nur per DDL manchen nur via Access Objektmodell.

 

Ich habe auf dem Balkon *cool.gif* noch ein bisschen über Ekkehards Idee nachgedacht. Ist es nicht möglich, eine Tabellenbeschreibung oder auch nur ein Feld über die TabelDefs- oder Fields-Auflistung vom FE in das BE zu kopieren?
Für die neue Tabelle etwa so:
Code:

"db" = CurrentDB = FrontEnd
"db_BE" = geöffnete DB _ BackEnd

Debug.Print zeigt mir auch die korrekten Tabellen beider TableDefs.

Dennoch erhalte ich Fehlercode 3367. Der besagt, dass das Objekt sich bereits in der Auflistung befindet.
Also scheint db_BE.TableDefs.Append obj_table nicht auf das BE zu verweisen, sonder falsch auf das FE. Aber warum?

Viele Grüße
Michael

 

Die Fehlermeldung sagt doch eh alles.
Einfach mal genau hinsehen.

Edit:
Hast du es jetzt geschafft, das Feld korrekt mittels createfield zu erstellen?

 

DDL-Scripte zum Update einer BackEnd-DB generieren
So richtig weitergekommen bist Du noch nicht?

Schon weil man ein solches Script zur Strukturänderung an einem Backend mehrfach ausführen wird (aus Versehen, zum Testen, zum Kumulieren des Codes für eine Dokumentation), sollte man generell erst prüfen, dann Aktionieren, sprich "wenn nicht da, mache".
Ein Backend enthält gesammeltes Wissen und ist durch einen Riesenaufwand an Eingaben entstanden, den wohl keiner wiederholen will oder kann. Fehler sind da nicht erlaubt.

Ich halte es für extrem ambitioniert mit einer hohen Wahrscheinlichkeit des Scheiterns, wenn man die Daten eines richtigen Arbeits-Backends in die Struktur eines überarbeiteten Backends schieben wollte, dann womöglich noch unter Zeitdruck und in einer Verwendungspause bei einer Mehrnutzerumgebung.
Wenn man eine Anwendung in mehrere lokale Nutzungen verteilt hat, wird es einem dann auch schwer fallen, überall bei einer Aktualisierung beizuwohnen.

VBA wie auch SQL "sprechen" nur englisch. Da wird es wieder übersichtlich gegenüber der Verwendung von Assistenten mit teilweiser Sprachübersetzung.

 

Zusätzliche ja/Nein Felder bei bestehendem Datenmodell - was wird das wohl bedeuten? *grins *grins

 

Meine Augen haben geschaut und mein Hirn versteht, dass das db_BE.TableDefs.Append offenbar auf das FrontEnd zielt. Es versteht aber nicht, warum! *boah

Wie ich schon schrieb bin ich im Stadium des Experimentierens und Entdeckens. Die paar Code-Schnipsel sind natürlich noch keine robuste Software.

Durch das Experimentieren hab ich herausgefunden:

1. DDL bietet offenbar die geringsten Möglichkeiten.
2. Alle Tabellen / Felder / Eigenschaften per VBA aufzubauen ist code-intensiv und höchst Fehleranfällig.Properteis

Deshalb wollte ich noch den zuletzt beschriebenen Weg wählen. Die betroffenen Tabellen kann ich aus jedem beliebigen Entwicklungsstand entnehmen. Wenn es gelänge, die Tabellen / Felder vom FE in das BE zu übertragen wäre der Code sehr kompakt und wenig fehleranfällig. Leider erkenne ich momentan noch nicht, warum es nicht so funktioniert wie mein Kopf es denkt... *wink.gif*

Dass Stammdatentabellen im Laufe der Zeit mal um das eine oder andere Attribut ergänzt werden halte ich für normal.

Viele Grüße
Michael

 

1) Man sieht die Erstellung von db_BE nicht. Bis dahin kann man nur glauben ...
2) Wenn man einer (Objekt)Variablen einen Wert zuweist (vermutlich sogar den richtigen) und dann in den beiden DB's per Schleife herumturnt, wäre es ein Riesenzufall, dass man am Ende bei Verwendung genau den gewünschten ursprünglichen Wert hat. Ich würde eher den letzten Wert aus der letzten Schleife und dann aus deren DB erwarten.

 

Mit Erstellung db_BE und ohne Turnen *wink.gif*
Code:

Die fett markierten Code-Zeilen führen zum Fehler 3367.

Habe ich jetzt alle Fakten geliefert, um den Glauben in ein Wissen gerinnen zu lassen?

Viele Grüße
Michael

 

Dann experimentiere weiter.
Das führt dann automatisch dazu, dass man die Klassen versteht.

So wie du es dir vorstellst geht es aber grundsätzlich nicht - habe ich vor einiger Zeit auch schon mal versucht.
Entweder du kopierst per Transfair Funktion, oder du schreibst den Code selbst.
Der ist dann genau so fehleranfällig, wie du ihn programmierst.
Du musst also wissen was du tust.
Das Studium der Klassen und das Experimentieren ist da der richtige Weg.

Leider geht das per DDL nicht vernünftig, so wie in anderen Datenbanken.
Ich persönlich würde es dennoch per DDL Anweisung machen.
Da ist die gesamte Änderung einer Tabelle eine Codeanweisung.
Die Erstellung von Default Werten/Constraints ist dann noch eine Anweisung pro Feld.