Office: Primärschlüssel/Fremdschlüssel aus 2.Tabelle automatisch einfügen

Hallo zusammen,

ich stehe gerade vor dem Problem, dass ich die Datensätze zwischen zwei Tabellen nicht verknüpfen kann.
Konkret habe ich die beiden Tabellen tblEigenschaft und tblBasis.
In der tblBasis habe ich die Daten Bezeichnung (kurzer Text), Leistung (Zahl), Jahr (Zahl) und die BasisID (Autowert, Primärschlüssel).
In der tblEigenschaft befindet sich eine EigenschaftNr (Kurzer Text, Primärschlüssel), Messergebnis (Währung) und die BasisID(Zahl, Fremdschlüssel).
Nun möchte ich gerne, dass eine automatische Zuordnung erfolgt zwischen der tblEigenschaft und der tblBasis. In der tblBasis und in der tblEigenschaft werden laufend neue Daten über einen XML Import eingepflegt und die Zuordnung muss aktuell für jeden Datensatz in der tblEigenschaft manuell durch Eingabe der BasisID (Fremdschlüssel) erfolgen.
Anbei die beiden Tabellen sowie die vorliegende Beziehung zur besseren Veranschaulichung.

Vielen Dank vorab.

 

Hallo,
der Autowert als Primärschlüssel und dann als Fremdschlüssel in der 1:n-Beziehung ist grundsätzlich eine gute Idee.
Beim Import, der gleichzeitig in zwei Tabellen erfolgen soll, hat er jedoch den Nachteil, dass er erst beim tatsächlichen Insert in die erste Tabelle erzeugt wird. Dann der Satz neu gelesen werden muss um die ID dann als Fremdschüssel in die zweite Tabelle zu schreiben.
Ich würde in diesem Fall die Bezeichnung, soweit eindeutig, anstatt der BasisID als Fremdschlüssel in die zweite Tabelle nehmen und darüber die 1:n-Beziehung legen. Dann können beide Tabellen in einem Schritt geschrieben werden.
Jetzt werden alle Datenbanknormalisierer vielleicht protestieren, aber dann kann man sich eigentlich auch die BasisID sparen und die Bezeichnung in der ersten Tabelle zum Primärschlüssel machen.
Muss man dann sehen, was die Performance macht. Ich glaube, dass sich das erst bei vielen tausend Datensätzen bemerkbar macht.
Beste Grüße
Andreas

 

Hallo Andyfau,

vielen Dank für deine schnelle Rückmeldung.
Das mit dem Bezeichnungsfeld hatte ich mir auch bereits überlegt, allerdings kann das Feld Bezeichnung unter Umständen sehr lange werden und ist leider nicht eindeutig sodass ggf. ein natürlicher Schlüssel definiert werden muss. Im Hinblick auf die Normalisierung wäre es mir ebenfalls lieber, wenn es über die BasisID abbildbar wäre. Deshalb würde ich gerne versuchen die Thematik anders zu lösen.
Meine aktuelle Überlegung ist, die BasisID der tblBasis nach dem Import über VBA an die tblEigenschaft zu übertragen. Mein erster Ansatz wäre nach dem Import in die tblBasis und die tblEigenschaft die neu erzeugte BasisID aus der tblBasis zu kopieren und in die Einträge BasisID der tblEigenschaft zu kopieren in denen entweder der Wert 0 (Standardeingabe) bzw. leer hinterlegt ist. Das hätte allerdings wiederum den Nachteil, dass pro Import nur ein Datensatz in die tblBasis eingepflegt werden kann oder wie erfolgt sonst die Zuordnung?
Leider bin ich im Umgang mit VBA nicht sonderlich erfahren, weshalb mein Ansatz (siehe unten) bisher nicht funktioniert.

Ich habe es über folgende Prozedur versucht:

Sub SucheBasisID ()

Dim strSQL as String
strSQL = "UPDATE tblBasis " & SET tblEigenschaft.BasisID = SELECT (MAX([tblEigenschaft].[BasisID]) &
"WHERE tblEigenschaft.BasisID = 0 "
DoCmd.RunSQL strSQL

End Sub

Leider bekomme ich dann jedes Mal eine Fehlermeldung.

Bitte um Unterstützung.
Vielen Dank und Grüße
Rainyday

 

Hallo Rainyday,

so, wie Du das vor hast kann das nicht funktionieren. Die 1:n-Beziehung erzwingt eine referentielle Integrität.
Es kann also in tblEigenschaft kein Satz angelegt werden, der nicht bereits bei der Anlage eine gültige BasisID aus tblBasis hat.
Daraus folgt, dass es nicht über einen nachträglichen Update geht, sondern unmittelbar bei der Anlage des Satzes die BasisID gefüllt werden muss.
Oder es gibt eine Zwischentabelle tblEigenschaftZwi, die keine Beziehungen hat und deren Datensätze dann per Anfügeabfrage mit der BasisID ergänzt und nach nach tblEigenschaft übertragen wird.

 

Du kannst mir gern mal beispielhafte Eingabedaten senden (XML Datei). Vielleicht gehts dann einfacher eine kleine VBA-Routine zu schreiben.

 

Guten Abend Andyfau,

danke für die Rückmeldung.
Die Xml-Datei hat folgenden Aufbau:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataroot>
<Nutzdaten>
<tblBasis>
<Bezeichnung>Apfel</Bezeichnung>
<Leistung>20</Leistung>
<Jahr>2099</Jahr>
<Eigenschaften>
<tblEigenschaft>
<EigenschaftNr>1CDE</EigenschaftNr>
<Messergebnis>0.01</Messergebnis>
</tblEigenschaft>
<tblEigenschaft>
<EigenschaftNr>3SPF</EigenschaftNr>
<Messergebnis>0.04</Messergebnis>
</tblEigenschaft>
</Eigenschaften>
</tblBasis>
<tblBasis>
<Bezeichnung>Birne</Bezeichnung>
<Leistung>30</Leistung>
<Jahr>2055</Jahr>
<Eigenschaften>
<tblEigenschaft>
<EigenschaftNr>3GPA</EigenschaftNr>
<Messergebnis>0.04</Messergebnis>
</tblEigenschaft>
<tblEigenschaft>
<EigenschaftNr>4BBC</EigenschaftNr>
<Messergebnis>0.008</Messergebnis>
</tblEigenschaft>
</Eigenschaften>
</tblBasis>
</Nutzdaten>
</dataroot>


Die BasisID habe ich als Zusatzfeld erst in Access eingebaut, weshalb diese nicht in der XML Datei enthalten ist.

 

Hi,
lege diesen Code in ein allgemeines VBA-Modul. Der Aufruf erfolgt ueber die SUB "aufruf", z.b im Click-Ereignis eines Buttons in einem Formular.
Beachte, dass ich die Felder Messwert und Jahr als String deklariert habe. Musst Du ggf. noch anpassen.
Der Code sollte sebsterklaerend sein. (Die BTags weglassen, die mogelt der Editor ein)

Code:

' xml als Textdatei lesen und auf zwei Tabellen verteilen[/B]
[B]Option Compare Database
Option Explicit

Dim Textdatei As String
Dim Zeile As String

Dim Bezeichnung As String
Dim Leistung As Long
Dim Jahr As Long
Dim BasisID As Long
Dim EigenschaftNr As String
Dim Messergebnis As String

Dim rs As Recordset
Dim rsf As Recordset
Dim db As Database

Sub aufruf()
 Textdatei = "C:\Users\an\Documents\Testdatenbanken\Test.xml"
 parsetxt Textdatei
End Sub

Sub parsetxt(Textdatei)
  Open Textdatei For Input As #1
  Do While Not EOF(1)
       Line Input #1, Zeile 'Textdatei zeilenweise einlesen
       analyse ' Zeile analysieren, zerlegen und in temporäre Felder ablegen.
  Loop
 Close #1 ' Textdatei schließen
 Set db = Nothing
 Set rs = Nothing
 Set rsf = Nothing
End Sub

Sub analyse()
    If Mid$(Zeile, 1, 13) = "<Bezeichnung>" Then
       Bezeichnung = Mid$(Zeile, 14, InStr(1, Zeile, "</") - 14)
       'Debug.Print Bezeichnung
       Exit Sub
    End If
    If Mid$(Zeile, 1, 10) = "<Leistung>" Then
       Leistung = Mid$(Zeile, 11, InStr(1, Zeile, "</") - 11)
       Exit Sub
    End If
    If Mid$(Zeile, 1, 6) = "<Jahr>" Then
       Jahr = Mid$(Zeile, 7, InStr(1, Zeile, "</") - 7)
       schreib_tblBasis
       Exit Sub
    End If
    If Mid$(Zeile, 1, 15) = "<EigenschaftNr>" Then
       EigenschaftNr = Mid$(Zeile, 16, InStr(1, Zeile, "</") - 16)
       Exit Sub
    End If
    If Mid$(Zeile, 1, 14) = "<Messergebnis>" Then
       Messergebnis = Mid$(Zeile, 15, InStr(1, Zeile, "</") - 15)
       schreib_tblEigenschaft
    End If
End Sub

Sub schreib_tblBasis()
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblBasis", dbOpenDynaset)
    rs.AddNew
    rs!Bezeichnung = Bezeichnung
    rs!Leistung = Leistung
    rs!Jahr = Jahr
    rs.Update
    BasisID = DMax("BasisID", "tblBasis") 'erzeugte BasisID fuer Eigenschaftssatz merken
End Sub

Sub schreib_tblEigenschaft()
    Set db = CurrentDb
    Set rsf = db.OpenRecordset("tblEigenschaft", dbOpenDynaset)
 
    rsf.AddNew
    rsf!EigenschaftNr = EigenschaftNr
    rsf!Messergebnis = Messergebnis
    rsf!BasisID = BasisID
    rsf.Update
End Sub

 

Hallo Andyfau,

vielen Dank für deine schnelle Rückmeldung und den umfangreichen Code. Prinzipiell wäre das die Lösung gleich zwei meiner Herausforderungen:
1. Beziehung zwischen tblEigenschaft und tblBasis über die BasisID und
2. XML-Import ohne manuellen Eingriff in die XML Datei.

Leider habe ich aktuell noch Schwierigkeiten bei der Umsetzung und bin dabei auf weitere Probleme gestoßen:

1. In der tblEigenschaft benötige ich nun noch den Hersteller. Die Beziehung wird über die HerstellerID zwischen tblHersteller und tblEigenschaft hergestellt. Es würde zunächst genügen einen Standardwert zu setzen, welcher die ID 1 annimmt.
2. Aus dem Eintrag EigenschaftNr benötige ich nur die hinteren drei Stellen und diese soll als RFNr (es existiert ebenfalls die tblRF) hinterlegt werden.
Ich habe versucht das entsprechend umzusetzen jedoch bekomme ich dann die Fehlermeldung "Laufzeitfehler ´3201´: Der Datensatz kann nicht hinzugefügt oder geändert werden, da ein Datensatz in der Tabelle ´tblRF´ mit diesem Datensatz in Beziehung stehen muss.
3. Weiterhin ist in der Bezeichnung noch die Anbauposition enthalten z.B. Apfel 2NW, von der ich die Anbauposition 2NW in das Feld Anbauposition schreiben möchte
4. Der Aufruf der Prozedur funktioniert nicht über den Button im Formular

Anbei mein Versuch:

' xml als Textdatei lesen und auf zwei Tabellen verteilen
Option Compare Database
Option Explicit

Dim Textdatei As String
Dim Zeile As String

Dim Bezeichnung As String
Dim Leistung As Long
Dim Jahr As Long
Dim BasisID As Long
Dim EigenschaftNr As String
Dim Messergebnis As Double
Dim RFNr As String

Dim rs As Recordset
Dim rsf As Recordset
Dim db As Database

Sub aufruf()
Textdatei = "C:\Users\Ra\Documents\Testdatenbanken\TestImport.xml"
parsetxt Textdatei
End Sub

Sub parsetxt(Textdatei)
Open Textdatei For Input As #1
Do While Not EOF(1)
Line Input #1, Zeile 'Textdatei zeilenweise einlesen
analyse ' Zeile analysieren, zerlegen und in temporäre Felder ablegen.
Loop
Close #1 ' Textdatei schließen
Set db = Nothing
Set rs = Nothing
Set rsf = Nothing
End Sub

Sub analyse()
If Mid$(Zeile, 1, 13) = "<Bezeichnung>" Then
Bezeichnung = Mid$(Zeile, 14, InStr(1, Zeile, "</") - 14)
'Debug.Print Bezeichnung
Exit Sub
End If
If Mid$(Zeile, 1, 10) = "<Leistung>" Then
Leistung = Mid$(Zeile, 11, InStr(1, Zeile, "</") - 11)
Exit Sub
End If
If Mid$(Zeile, 1, 6) = "<Jahr>" Then
Jahr = Mid$(Zeile, 7, InStr(1, Zeile, "</") - 7)
schreib_tblBasis
Exit Sub
End If
If Mid$(Zeile, 1, 15) = "<asg:EigenschaftNr>" Then
EigenschaftNr = Mid$(Zeile, 23, InStr(1, Zeile, "</") - 23)
If Mid$(Zeile, 1, 15) = "<EigenschaftNr>" Then
RFNr = Mid$(Zeile, 19, InStr(1, Zeile, "</") - 19)
Exit Sub
End If
If Mid$(Zeile, 1, 14) = "<Messergebnis>" Then
Messergebnis = Mid$(Zeile, 15, InStr(1, Zeile, "</") - 15)
schreib_tblEigenschaft
End If
End Sub

Sub schreib_tblBasis()
Set db = CurrentDb
Set rs = db.OpenRecordset("tblBasis", dbOpenDynaset)
rs.AddNew
rs!Bezeichnung = Bezeichnung
rs!Leistung = Leistung
rs!Jahr = Jahr
rs.Update
BasisID = DMax("BasisID", "tblBasis") 'erzeugte BasisID fuer Eigenschaftssatz merken
End Sub

Sub schreib_tblEigenschaft()
Set db = CurrentDb
Set rsf = db.OpenRecordset("tblEigenschaft", dbOpenDynaset)

rsf.AddNew
rsf!EigenschaftNr = EigenschaftNr
rsf!RFNr = RFNr
rsf!Messergebnis = Messergebnis
rsf!BasisID = BasisID
rsf!HerstellerID = 1
rsf.Update
End Sub



Sub Aufruf über Formular:
Option Compare Database
Private Sub Befehl0_Click()
Call aufruf
End Sub


XML Datei:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataroot generated="2019-07-15T12:12:29"
xmlns:asg= "http://anbau.Firma.com/PP/asg">
<Nutzdaten>
<tblBasis>
<Bezeichnung>Apfel 2NW</Bezeichnung>
<asg:EigenschaftNr>as:GM8+88:GST</asg:EigenschaftNr>
<Leistung>20</Leistung>
<Jahr>2099</Jahr>
<Eigenschaften>
<tblEigenschaft>
<EigenschaftNr>RA:1CDE</EigenschaftNr>
<Messergebnis>0.01</Messergebnis>
</tblEigenschaft>
<tblEigenschaft>
<EigenschaftNr>RA:3SPF</EigenschaftNr>
<Messergebnis>0.04</Messergebnis>
</tblEigenschaft>
</Eigenschaften>
</tblBasis>
<tblBasis>
<Bezeichnung>Birne 50SW</Bezeichnung>
<asg:EigenschaftNr>as:JU8+88:OPA</asg:EigenschaftNr>
<Leistung>30</Leistung>
<Jahr>2055</Jahr>
<Eigenschaften>
<tblEigenschaft>
<EigenschaftNr>RA:3GPA</EigenschaftNr>
<Messergebnis>0.04</Messergebnis>
</tblEigenschaft>
<tblEigenschaft>
<EigenschaftNr>RA:4BBC</EigenschaftNr>
<Messergebnis>0.008</Messergebnis>
</tblEigenschaft>
</Eigenschaften>
</tblBasis>
</Nutzdaten>
</dataroot>

Vielen Dank.
Grüße
Rainyday

 

Wenn UTF-8 Encoding verwendet wird, sollte sich das im VBA Code auch widerspiegeln.
Access bietet doch seit Jahren eine Funktion für xml Import an.

Wozu VBA, wo man bei jeder Änderung den Code ändern muss und dann auch noch selbst für das korrekte Umsetzung der Textcodierung sorgen muss und dazu noch grottenlangsam ist?

Wie hast du denn bis jetzt importiert?
Verwendest du den XML Import aus dem Access Objektmodell?

Wenn ja, dann geht es doch nur um zwei simple Abfragen.

 

Hallo Markus,
das ist durchaus bekannt, aber die Aufgabenstellung macht das mit dem xml-import kompliziert. Währen des Imports muss der Schlùssel für die zweite Datei erzeugt werden. Über eine Transferdatei habe ich das nicht hinbekommen. Wenn Du weißt wie, gerne her damit.

 

Hallo Markusxy,

bisher habe ich den XML Import von Access über die Externe Daten Funktion verwendet. Das Problem bei dem Import ist allerdings, dass die XML Datei nicht mit der Struktur vorliegt wie ich die Daten in Access benötige, weshalb ich die Lösung von Andyfau als optimal empfinde. Die Struktur der XML Datei bleibt bis auf weiteres unverändert. Wie würden denn die zwei simple Abfragen konkret aussehen und wie verhält es sich dann mit der Struktur der XML Datei? Müsste man für die XML Datei dann ein eigenes Schema erstellen? Die Performance würde ich erst zweitranging berücksichtigen, in erster Linie geht es mir um die Konsistenz der Datenbank und das alle Daten in der korrekten Form vorliegen.

Vielen Dank.
Grüße
Rainyday

 

Hallo Rainyday,
das ist das Problem mit der Lösungssuche über ein Forum. Es wird nach einem Teilproblem gefragt, aber das Drumherum wird nicht mit dargestellt. Hier ist es so, dass die DB offensichtlich noch weitere Tabellen enthält, die wiederum untereinander in Beziehung stehen. Daher auch die Fehlermeldung. Du kannst keinen Satz in eine (Unter)tabelle schreiben, die eine Beziehung mit referentieller Integrität zu einer anderen Tabelle hat, wenn dort der entsprechende Schlüssel noch nicht vorhanden ist.
Die Themen, in die Du dich einarbeiten musst sind:
-Stringverarbeitung (Funktionen wie Mid$, instr, split, usw.) (Für Fragen 1 bis 3)
-Was ist referentielle Integrität und Normalisierung? (Für Frage 2)
-Prozeduraufrufe und Gültigkeit von Variablen lokal oder global (Stichwort Public sub, wenn der Code in einem allgemeinen Modul steht. Oder nehme den Code in das aufrufende Formular) (Für Frage4 )
-Debugging des VBA-Codes um den Ablauf des Programms nachzuvollziehen.

Es bringt nichts, wenn ich Dir den Code jetzt baue, wenn Du es selbst lernen möchtest. Das wird dann eher zur Auftragsarbeit.
Das soll dich aber nicht davon abhalten weitere Fragen zu stellen, wenn es hakt.

 

Wie genau machst du das?


Edit: Also grundsätzlich zum Thema.
In VBA nimmt man der Einfachheit halber die MSXML Klassenbibliothek.
Beispiel sind da genug zu finden. Dann hat man mit einer simplen Anweisung Zugang zu einem strukturierten Objekt. Die Frage ist natürlich, wie es möglich ist , dass da sogar Tabellennamen stehen und aber keine ID's. Wer macht so was?

Und ja, natürlich musst du dich mit den einfachen Grundlagen auseinander setzen.
Ohne simpelste Basiskenntnisse kommst du nicht weit.
Das ist auch der Grund, warum ich im Normalfall keine fertigen Lösungen präsentiere.
Grundlagen muss man sich erarbeiten, sonst hat man in einem Forum nichts verloren.
Wenn jemand fertige Lösungen haben will, muss er zahlen.