blockiert ein Dictionary eine Tabelle?

[Doming]

Hallo,

ich lese die Dateinamen von Zeichnungen aus einer Netzwerk-Verzeichnisstruktur aus und schreibe den Dateinamen und den Pfad in eine Tabelle. Dann lese ich aus dem Dateinamen eine Materialnummer aus und trage diese in ein weiteres Feld ein. Das ist dann zwar ein redundantes Feld aber da die Syntax Dateinamen variiert, will ich wissen, ob das Auslesen der Nummer korrekt ist.

Es gibt aber ein FK-Feld in der Tabelle, welches ich mit einer Prozedur aus der tbl_Material auslese. Es geht um etwa 22.000 Zeichnungen für etwa 18.000 Materialien.
Zuerst habe ich dann in einer Schleife bei jedem Zeichnungsdatensatz in der Materialtabelle nach Übereinstimmungen gesucht, was natürlich einiges an Zeit in Anspruch genommen hat. Später bin ich dann dazu übergegangen, sowohl die Zeichnungs- als auch die Materialtabelle in ein jeweils ein Array einzulesen und diese dann zu vergleichen. Danach dann das modifizierte Zeichnungsarray zurück in die Tabelle.
Das hat soweit auch funktioniert.

Nun habe ich das Einlesen der Dateien mittels KI umgebaut und somit die Einlesezeit von etwa 80 auf 6 Minuten reduziert. In dem Zuge habe ich auch den Vergleich mal analysieren lassen und man schlug mir den Einsatz von einem Dictionary vor.

Code [Auswählen] Erweitern

Private Function LadeMaterialDikt() As Scripting.Dictionary
On Error GoTo Fehler
 Dim rs As DAO.Recordset
 Dim dict As Object
 Dim MNr As String
 Dim ID As Long

    Set dict = CreateObject("Scripting.Dictionary")
    Set rs = CurrentDb.OpenRecordset("SELECT Artikel_ID, MaterialNummer FROM tbl_Material", dbOpenSnapshot)
   
    Do Until rs.EOF
        MNr = rs!Materialnummer
        ID = rs!Artikel_ID
        If Not dict.Exists(MNr) Then dict.Add MNr, ID
        rs.MoveNext
    Loop
   
    Set LadeMaterialDikt = dict
Ende:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set dict = Nothing
End Function
Der Vergleich sieht dann so aus:

Code [Auswählen] Erweitern

Public Sub Zeich2Mat1()
 Dim MatDikt As Scripting.Dictionary
 Dim cDB As DAO.Database
 Dim rs As DAO.Recordset
 Dim MNr As String
 Dim b As Long
 Dim MatID As Long

    Set MatDikt = LadeMaterialDikt()
    Set cDB = CurrentDb
    Set rs = cDB.OpenRecordset("SELECT FS_MatID, MatNR " _
                             & "FROM tbl_Zeichnung_LCL " _
                             & "WHERE MatNr <> '0'", dbOpenDynaset, dbOptimistic)
                             
    DBEngine.BeginTrans
    Do Until rs.EOF
        MNr = Nz(rs!MatNr, "0")
        If MNr <> "0" Then
            b = b + 1
            rs.Edit
            If MatDikt.Exists(MNr) Then
                rs!FS_MatID = MatDikt(MNr)
            Else
                MatID = NeuMaterial(MNr, rs!Dateiname)
                MatDikt.Add MNr, MatID
            End If
            rs.Update
        End If
       
        If b > 1 And b Mod 1000 = 0 Then    'Unterbrechnung der Schleife weil sonst Speicher voll
            DBEngine.CommitTrans
            DBEngine.BeginTrans
            DBEngine.Idle dbRefreshCache
        End If
       
        rs.MoveNext
    Loop
    DBEngine.CommitTrans
Ende:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set MatDikt = Nothing
    Debug.Print Now, "Zeich2Mat abgeschlossen"
End Sub
Das funktioniert so weit gut und ist auch echt flott. Was leider nicht funktioniert ist, wenn es Zeichnungen gibt, für die es in der Materialtabelle keine Entsprechnung gibt.
Der Plan ist, dass das Material in einer Funktion in die Tabelle eingefügt wird und dann die ID des neuen Datensatzes zurückgibt:

Code [Auswählen] Erweitern

Public Function NeuMaterial(MatNr As String, MatBez As String) As Long
On Error GoTo Fehler
 Dim cDB As DAO.Database
 Dim rs As DAO.Recordset
 Dim MName As String
 Dim ID As Long
 
    Set cDB = CurrentDb
    MName = "AutoEingabe_" & MatBez
    Set rs = cDB.OpenRecordset("SELECT Materialnummer, Bezeichnung FROM tbl_Material")
    rs.AddNew
    rs!Materialnummer = MatNr
    rs!Bezeichnung = MName
    rs.Update
   
    rs.Close
   
   
'    cDB.Execute "INSERT INTO tbl_Material (Materialnummer, Bezeichnung) " _
'               & "VALUES('" & MatNr _
'               & "', '" & MName & "')", dbFailOnError
    ID = Nz(DLookup("Artikel_ID", "tbl_Material", "Materialnummer = '" & MatNr _
                                          & "' AND Bezeichnung = '" & MName & "'"), 0)
    If ID > 0 Then
        Debug.Print Now, "Neumaterial " & MatNr, ID
        cDB.Execute "UPDATE tbl_Material SET RefID = " & ID & " WHERE Artikel_ID = " & ID
        cDB.Execute "INSERT INTO tbl_Index(FS_Mat, IX) VALUES (" & ID & ", 0)"
        NeuMaterial = ID
        DoEvents
    Else
        MsgBox "Keine ID gefunden"
    End If

Ende:
    rs.Close
    Set rs = Nothing
    cDB.Close
    Set cDB = Nothing
end function Ursprünglich habe ich es mit dem (jetzt auskommentierten) SQL-Ausdruck versucht, dann mit rs.Addnew, aber beide Male verweigert mir die Tabelle einen Neueintrag.
Ich bin verwirrt, denn eigentlich gehören beide Prozeduren zum kleinen 1x1, die Weigerung kenne ich nicht. Das einzige was anders ist als sonst, ist der/das Dictionary. Wird die Tabelle dadurch blockiert oder welchen meiner Fehler sehe ich nicht?

Gruß
 Doming

[PhilS]

Ursprünglich habe ich es mit dem (jetzt auskommentierten) SQL-Ausdruck versucht, dann mit rs.Addnew, aber beide Male verweigert mir die Tabelle einen Neueintrag.

Was genau bedeutet "verweigert mir die Tabelle einen Neueintrag"?
Da gibt es vermutlich eine Fehlermeldung. Wie lautet diese Fehlermeldung genau?

Das Dictionary hat sicherlich nichts damit zu tun, dass du nicht in die Tabelle schreiben kannst.

[Doming]

Hallo,

Da gibt es vermutlich eine Fehlermeldung.

Tja, dann wäre ich weiter. Das einige was passiert, ist die MsgBox "Keine ID gefunden"
Egal ob ich den Code so wie oben lasse oder den SQL-String abschicke.

Code [Auswählen] Erweitern

MName = "AutoEingabe_" & MatBez
'    Set rs = cDB.OpenRecordset("SELECT Materialnummer, Bezeichnung FROM tbl_Material")
'    rs.AddNew
'    rs!Materialnummer = MatNr
'    rs!Bezeichnung = MName
'    rs.Update
'
'    rs.Close
   
   
    cDB.Execute "INSERT INTO tbl_Material (Materialnummer, Bezeichnung) " _
              & "VALUES('" & MatNr _
              & "', '" & MName & "')", dbFailOnError
    ID = Nz(DLookup("Artikel_ID", "tbl_Material", "Materialnummer = '" & MatNr _
                                          & "' AND Bezeichnung = '" & MName & "'"), 0)
Gerade habe ich mir den SQL-String im Direktfenster anzeigen lassen. Setze ich ein currentDB.Execute davor, wird das Material angelegt (ohne laufenden Code, also nicht im Unterbrechungsmodus).
In der Tabelle ist nur bei der Artikel_ID und der Bezeichnung eine Eingabe erforderlich.

Rufe ich im Direktfenster die Funktion auf (?Neumaterial("1234.12.1234", "Kannweg") wird mir sofort die neue ID ausgegeben.

kein "On Error Resume Next", auch im Einzelschrittmodus laufe ich so durch

[Doming]

Edit: Rufe ich im Unterbrechnungsmodus (da, wo er gerade vergeblich versucht hat, eine ID zu finden) die Materialtabelle auf, kann ich in den Feldern Daten verändern, allerdings nicht beim letzten Datensatz.

[PhilS]

Nur kurz vor unterwegs...

Denke.mal darüber nach was DBEngine.BeginTrans eigentlich bewirkt.

[Beaker s.a.]

Hallo Doming,

Hast du dieses Rs mal auf ".NoMatch" geprüft?

Code [Auswählen] Erweitern

    Set rs = cDB.OpenRecordset("SELECT FS_MatID, MatNR " _
                             & "FROM tbl_Zeichnung_LCL " _
                             & "WHERE MatNr <> '0'", dbOpenDynaset, dbOptimistic)
gruss ekkehard

[PhilS]

Hast du dieses Rs mal auf ".NoMatch" geprüft?

?
Recordset.NoMatch hat meines Wissens nur in Verbindung mit Recordset.Seek/.Find... eine dokumentierte Funktion.

[Doming]

Hast du dieses Rs mal auf ".NoMatch" geprüft?

Hallo Ekkehard,
es gibt viele Dateinamen, die keine Materialnummer enthalten, die bekommen alle eine 0 als Ersatzwert.
Sie dürfen in diesem Board keine Dateianhänge sehen. 
Ein Nomatch würde mich wundern, außerdem wird NeuMaterial ja korrekt aufgerufen.

Gruß
 Doming

[Doming]

Denke.mal darüber nach was DBEngine.BeginTrans eigentlich bewirkt.

Moin Phil,

vielen Dank für den Hinweis.

Code [Auswählen] Erweitern

            If MatDikt.Exists(MNr) Then
                rs!FS_MatID = MatDikt(MNr)
            Else
                DBEngine.CommitTrans
                MatID = NeuMaterial(MNr, rs!Dateiname)
                MatDikt.Add MNr, MatID
                DBEngine.BeginTrans
                DBEngine.Idle dbRefreshCache
            End IfDamit läuft der Code wie gewünscht durch.
Es ist das erste Mal, dass ich mit Dictionarys gearbeitet habe und ich war über die Geschwindigkeit begeistert.
Das kommt davon, wenn man den Code von der KI einfach mal so übernimmt..

[Knobbi38]

Hallo Doming,

irgendwie sieht das für mich nicht gerade optimal aus. Massenverarbeitung in Datenbanken wird normalerweise in SQL ausgeführt, nicht in VBA und schon gar nicht mit einem Dictionary.

Ich habe auch mal die KI dazu befragt und bekam folgende Antwort:

Dein aktueller Workflow leidet unter dem sogenannten RBAR-Syndrom (Row-By-Agonizing-Row – Zeile für schmerzhafte Zeile). Du öffnest für jeden fehlenden Datensatz Recordsets, feuerst langsame DLookup-Domänenfunktionen ab und führst nachträglich UPDATE-Befehle aus.In einem relationalen Datenbanksystem wie Access löst man so etwas performant über mengenbasierte SQL-Abfragen. Wir können die komplette Logik aus Schleifen, Dictionaries und VBA-Funktionen in nur drei SQL-Befehle gießen.

Vielleicht solltest du mal deine KI nochmal befragen und in die richtige Richtung "stubsen", von alleine bekommt man nämlich keine optimalen Ergebnisse.

Hier mal der Vorschlag von der KI:

Code [Auswählen] Erweitern

Public Sub Zeich2Mat_Mengenbasiert()
    On Error GoTo Fehler
    Dim cDB As DAO.Database
    Set cDB = CurrentDb
   
    ' Ganzen Prozess in eine einzige Transaktion packen (alles oder nichts)
    DBEngine.BeginTrans
   
    ' 1. Fehlende Materialien massenhaft anlegen
    cDB.Execute "INSERT INTO tbl_Material (Materialnummer, Bezeichnung) " & _
                "SELECT DISTINCT Z.MatNR, 'AutoEingabe_' & Z.Dateiname " & _
                "FROM tbl_Zeichnung_LCL AS Z LEFT JOIN tbl_Material AS M ON Z.MatNR = M.Materialnummer " & _
                "WHERE Z.MatNR Is Not Null AND Z.MatNR <> '0' AND M.Materialnummer Is Null;", dbFailOnError
               
    ' 2. RefID für die neuen Datensätze nachziehen
    cDB.Execute "UPDATE tbl_Material SET RefID = Artikel_ID WHERE RefID Is Null OR RefID = 0;", dbFailOnError
   
    ' 3. Initialen Index (0) für neue Materialien anlegen
    cDB.Execute "INSERT INTO tbl_Index (FS_Mat, IX) " & _
                "SELECT M.Artikel_ID, 0 " & _
                "FROM tbl_Material AS M LEFT JOIN tbl_Index AS I ON M.Artikel_ID = I.FS_Mat " & _
                "WHERE I.FS_Mat Is Null;", dbFailOnError
               
    ' 4. Die Fremdschlüssel in der Zeichnungstabelle aktualisieren
    cDB.Execute "UPDATE tbl_Zeichnung_LCL AS Z INNER JOIN tbl_Material AS M ON Z.MatNR = M.Materialnummer " & _
                "SET Z.FS_MatID = M.Artikel_ID " & _
                "WHERE Z.MatNR Is Not Null AND Z.MatNR <> '0';", dbFailOnError
               
    ' Wenn alles glatt lief: Speichern
    DBEngine.CommitTrans
    Debug.Print Now, "Workflow erfolgreich und mengenbasiert abgeschlossen!"
    Exit Sub

Fehler:
    DBEngine.Rollback
    MsgBox "Fehler " & Err.Number & " im SQL-Workflow: " & Err.Description, vbCritical, "Datenbankfehler"
End Sub

und bitte: die Angaben sind natürlich ungeprüft mangels fehlender Demodaten. Auch hat sich die KI wohl verzählt, denn das Endergebnis hat dann

Warum diese SQL-Lösung massiv besser ist:Kein Speicherüberlauf mehr:
- Da Access die Abfragen intern im Abfrage-Optimierer verarbeitet, entfällt das manuelle "Chuncking" (Aufteilen in 1000er-Schritte).
- Keine fehleranfälligen Lookups: Das gefährliche DLookup, das bei großen Tabellen die Performance komplett killt, ist restlos eliminiert.
- Echte atomare Transaktion: Tritt an irgendeiner Stelle ein Fehler auf, wird die gesamte Kette (Schritt 1 bis 4) sauber zurückgerollt. Es entstehen niemals "halbe" Datenleichen.

Wenn man jetzt noch die passenden Indizes einsetzt, sollte das eigentlich schön sauber und performant durchlaufen.

Knobbi38

[Doming]

Hallo,

mir ist klar, das DLookup kein Brandbeschleuniger ist, aber eigentlich läuft der Import der ganzen Geschichte einmal in der Woche. In der Zeit kommen etwa 0-30 neue Datensätze dazu. Häufig sind es neue Versionen von überarbeitetem Material, es ist in der Materialtabelle also schon vorhanden. Es sind also nicht so oft Aufrufe von "NeuMaterial".
Gerne würde ich es ja so verwenden:

Code [Auswählen] Erweitern

rs.AddNew
rs!Materialnummer = MatNr
rs!Bezeichnung = MName
rs.Update
ID = rs!Artikel_IDAber wenn das sicher funktionieren würde, gäbe es die vielen Diskussionen, wie man an die ID des gerade eingefügten DS kommt, vermutlich nicht.

Gruß
Doming

[Knobbi38]

Wieso sollte das nicht funktionieren?

Probiere das mal mit diesem Zwischenschritt:

Code [Auswählen] Erweitern

rs.Update
rs.Bookmark = rs.LastModified ' Aktualisiert den Zeiger auf den neuen Datensatz
ID = rs!Artikel_ID

Knobbi38

[Doming]

Hm gut...

...aber da ist ja immer noch die Geschichte mit dem SQL-Server (ich warte noch auf Genehmigung), wie bekomme ich mit SQL-Mitteln die ID heraus?

Gruß
 Doming

[Knobbi38]

Das hat ja jetzt nicht direkt etwas mit einem SQL-Server zu tun, sollte dort aber auch laufen. Alternativ gibt es auch noch @@Indentity und deren verwandte Funktionen.

[Bitsqueezer]

Hallo,

wenn alle Tabellen auf SQL Server liegen, könntest Du zum Beispiel auch eine Stored Procedure schreiben, die die INSERT...SELECT komplett auf dem Server erledigen, u.a. ginge hier auch die Übergabe von Parametern.

@@IDENTITY ist eine Systemvariable von SQL Server, die die letzte PK-AutoID in der aktuellen Sitzung zurückgibt. Das funktioniert auch mit VBA, wenn man sie per SELECT ausliest, allerdings nicht mit DAO und auch nicht mit Pass-Through-Query, weil das eine neue Verbindung/Sitzung erzeugen würde. Wenn Du ein ADO-Recordset verwendest zum Einfügen, kannst Du aber ohne weiteres (vor dem Schließen der Verbindung) die @@IDENTITY-Variable mit einem SELECT auslesen.

@@IDENTITY funktioniert übrigens auch ohne SQL Server in Access mit ADO Recordsets (mit DAO habe ich es nicht probiert). (Besser in SQL Server "SCOPE_IDENTITY()" verwenden.)

Besser ist allerdings, eine SP zu verwenden. Das Problem bei INSERT..SELECT ist, daß Dir die letzte ID nicht hilft, denn hier mußt Du ja alle IDs wissen, die neu hinzugekommen sind, und diese als FK im passenden Datensatz einbauen.

Dazu gibt es in T-SQL (nicht in Access SQL) die OUTPUT TO Klausel, die man in den INSERT..SELECT einfügen kann. Man erstellt vorher z.B. eine Temp-Tabelle mit passenden Feldern und OUTPUT TO schreibt die Daten, die mit INSERT eingefügt wurden, in die Temp-Tabelle. Das geht aber nur mit den Feldern, die Bestandteil von INSERT sind (und der ID). Am Ende kannst Du die ID und andere Felder aus der Temp-Tabelle ermitteln und darauf basierend Fremdschlüssel in andere Tabellen schreiben per UPDATE oder passende neue Datensätze generieren.

Es gibt viele Varianten, Daten in Tabellen zu importieren, SQL Server hat auch noch mehr Varianten, z.B. könntest Du eine lokale Tabelle als XML als Parameter einer SP übergeben( VBA kann das einfach umwandeln). Oder Du könntest den ganz dicken Hammer auspacken und Integration Services verwenden, wo Du genau konfigurieren kannst, welche Datei wie gelesen und importiert werden soll, das ginge auch automatisiert zu bestimmten Tageszeiten... usw.

Da man aus dem Konstrukt hier nicht so richtig schlau wird, kann man das auch nur so allgemein beantworten.

Gruß

Christian