Office: (Office 2003) Anfüge abfrage

Hallo zusammen..

Ich versuche eine Excel Liste zu importieren und diese auf einzelne Tabellen zu verteilen. Das Importieren der Liste in eine Tabelle war kein Problem. Beim verteilen verzweifel ich komplett..

Über die SF bin ich auf viele Hilfestellungen gestoßen. Habe die Lösungsvorschläge versucht an meine DB anzupassen und bin kläglich gescheitert

Um es besser verständlich zu machen, habe ich die DB (samt der importierten Tabelle) im Anhang hinzugefügt..

Ich brauche dringend Hilfe zur Erstellung einer Anfügeabfrage

Ich hoffe dass mir irgend jemand helfen kann

:)

 

Alle Tage wieder:
Acc2003 - Datensätze verteilen - MS-Office-Forum

 

Um die Daten einer Tabelle auf fünf Tabellen zu verteilen, wird eine Anfügeabfrage nicht ausreichen (und somit auch nicht auffindbar sein).

Die vier Mastertabellen kann man je mit einer Anfügeabfrage mit eingebauter Inkonsistenzprüfung füllen, etwa in der Art ...
Code:

Damit werden dort fehlende Werte ergänzt und die zugehörigen Primärschlüssel erzeugt.

Die Tabelle Kundenprojekt macht dann die eigentliche Arbeit aus, da hier statt der Klarwerte wie Kundenbereich dessen PK als Fremdschlüssel einzufügen ist. Diese Fremdschlüssel müssen aus den 4 Mastertabellen ermittelt und dann beim Einfügen berücksichtigt werden.
Das wird dann vermutlich datensatzweise erfolgen müssen.

Zum Ermitteln der PK´s wird man in einfachen Fällen auf ein Seek/FindFirst oder Einzelabfragen (DLookup, -ersatz) zurückgreifen können, bei performancelastigen Fällen wird man die Tabellen in lokale Objekte mit Indexnutzung (Collection, Dictionary) laden und damit auswerten.

Nicht nur nebenbei: Bei Deinen "eckigen" Bezeichnungen (Leer- und Sonderzeichen) musst Du eckige Klammern verwenden.

 

Damit werden dort fehlende Werte ergänzt und die zugehörigen Primärschlüssel erzeugt.

Die Tabelle Kundenprojekt macht dann die eigentliche Arbeit aus, da hier statt der Klarwerte wie Kundenbereich dessen PK als Fremdschlüssel einzufügen ist. Diese Fremdschlüssel müssen aus den 4 Mastertabellen ermittelt und dann beim Einfügen berücksichtigt werden.
Das wird dann vermutlich datensatzweise erfolgen müssen.

Zum Ermitteln der PK´s wird man in einfachen Fällen auf ein Seek/FindFirst oder Einzelabfragen (DLookup, -ersatz) zurückgreifen können, bei performancelastigen Fällen wird man die Tabellen in lokale Objekte mit Indexnutzung (Collection, Dictionary) laden und damit auswerten.

Nicht nur nebenbei: Bei Deinen "eckigen" Bezeichnungen (Leer- und Sonderzeichen) musst Du eckige Klammern verwenden.

Die ANfügeabfragen der 4 Mastertabellen habe ich dank deiner Hilfe hinbekommen..

Bei der Zerlegung der Kundenprojekttabelle stehe ich mal wieder wie der Ochse vor dem Berg

im Anhang habe ich die DB mit den ersten 4 Anfügeabfragen...

Kannst du mir vielleicht auch beim nächsten Schritt helfen?

 

Verzichte bitte auf Doppelpostings.
Es ist auch unnötig, einen unmittelbar davor stehenden Beitrag komplett zu zitieren, die Antwort sollte hinreichend zeigen, dass Du ihn wahrgenommen hast.

Hier ist ein etwas ausgebauter Ansatz unter Verwendung von Collections:
Code:

 

Vorne weg vielen Dank für deine Unterstützung..

Ich bekomme leider einen 'Laufzeitfehler 457' markiert wird die Zeile
col.Add CStr(.Fields(0)), .Fields(1)

Der Grund ist wohl die unkorrekte Bearbeitung der Auswertung eines Filterausdrucks, der einen falsch geschriebenen Objektnamen enthält

Kann den Fehler aber nicht finden

 

Der Code ist für die in #1 bereitgestellte DB mit Acc2000 geschrieben und lief damit.

Deine Objektnamen musst Du selber im Griff haben. Hilfsweise solltest Du ermitteln, für welche Abfrage/Tabelle das Problem besteht.

 

Du hast vollkommen recht.. Alles hat wunderbar funktioniert. Habe eine Mastertabelle hinzugefügt (CRM Stage).. Darum habe ich Zuordnungsfehler

Habe nun alles deklariert und eine zusätzliche INSERT Aufforderung geschrieben

Jetzt muss ich nur noch das Feld angeben...

Bsp: rsZ("Sales Manager ID") = CLng(colS.Item(.Fields(5)))

 

Code:

Die Feldauflistung ergibt sich nach der Datenquelle, also hier im Beispiel aus der Tabelle und deren Reihenfolge der Felder, wobei die Zählung beim Index 0 beginnt und somit Index 5 auf das sechste Feld verweist. Ersatzweise kann man auch die Feldnamen verwenden (wie vor dem =).

Angenommen, das folgende Feld ist das dritte in der Reihenfolge, dann sind folgende Ausdrücke für ein Recordsetfeld gleichwertig:
Code:

Bei Verwendung des Index würde ich eine minimal höhere Geschwindigkeit erwarten, außerdem kann man mit Schleifen arbeiten. Mit Verwendung des Feldnamens hat man eine bessere Lesbarkeit, insbesondere wenn die Feldliste länger ist bzw. gar nicht (Tabelle) sichtbar ist.

 

Also folgender Code zum Anfügen neuer Datensätze klappt ausgezeichnet:

Public Sub Import()
Dim db As DAO.Database
Dim rsQ As DAO.Recordset
Dim rsZ As DAO.Recordset
Dim colV As VBA.Collection
Dim colS As VBA.Collection
Dim colP As VBA.Collection
Dim colC As VBA.Collection
Dim colW As VBA.Collection


Set db = CurrentDb
db.Execute "INSERT INTO Vertriebsbereich (Vertriebsbereich) SELECT Q.Vertriebsbereich" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.Vertriebsbereich FROM Vertriebsbereich Z" & _
" WHERE Z.Vertriebsbereich = Q.Vertriebsbereich)", dbFailOnError
db.Execute "INSERT INTO [Sales Manager] (Name) SELECT Q.Salesmanager" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.Name FROM [Sales Manager] Z" & _
" WHERE Z.Name = Q.Salesmanager)", dbFailOnError
db.Execute "INSERT INTO Projekt ([Projekt Kürzel]) SELECT Q.Projekt" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.[Projekt Kürzel] FROM Projekt Z" & _
" WHERE Z.[Projekt Kürzel] = Q.Projekt)", dbFailOnError
db.Execute "INSERT INTO Customer ([Customer No], [Customer Name])" & _
" SELECT Q.[Customer No], Q.[Customer Name]" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.[Customer No] FROM Customer Z" & _
" WHERE Z.[Customer Name] = Q.[Customer Name])", dbFailOnError
db.Execute "INSERT INTO [CRM Status] ([CRM Status]) SELECT Q.[CRM Status]" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.[CRM Status] FROM [CRM Status] Z" & _
" WHERE Z.[CRM Status] = Q.[CRM Status])", dbFailOnError

Set colV = New VBA.Collection
FillCollection colV, db, _
"SELECT [Vertriebsbereich ID], Vertriebsbereich FROM Vertriebsbereich"
Set colS = New VBA.Collection
FillCollection colS, db, _
"SELECT [Sales Manager ID], Name FROM [Sales Manager]"
Set colP = New VBA.Collection
FillCollection colP, db, _
"SELECT [Projekt ID], [Projekt Kürzel] FROM Projekt"
Set colC = New VBA.Collection
FillCollection colC, db, _
"SELECT [Customer ID], [Customer Name] FROM Customer"
Set colW = New VBA.Collection
FillCollection colW, db, _
"SELECT [CRM ID], [CRM Status] FROM [CRM Status]"

Set rsZ = db.OpenRecordset("Kundenprojekt")
Set rsQ = db.OpenRecordset("Import", dbOpenSnapshot)
With rsQ
Do While Not .EOF
rsZ.AddNew
rsZ("Customer ID") = CLng(colC.Item(.Fields(2)))
rsZ("Projekt ID") = CLng(colP.Item(.Fields(4)))
rsZ("Vertriebsbereich ID") = CLng(colV.Item(.Fields(6)))
rsZ("Sales Manager ID") = CLng(colS.Item(.Fields(5)))
rsZ("CRM ID") = CLng(colW.Item(.Fields(16)))

rsZ.Update
.MoveNext
Loop
.Close
End With
rsZ.Close

Set rsZ = Nothing
Set rsQ = Nothing
Set colV = Nothing
Set colS = Nothing
Set colP = Nothing
Set colC = Nothing
Set colW = Nothing
Set db = Nothing
End Sub

Private Sub FillCollection(ByRef col As Object, ByRef dbs As Object, ByVal SQL As String)
Dim rs As DAO.Recordset
Set rs = dbs.OpenRecordset(SQL, dbOpenSnapshot)
With rs
Do While Not .EOF
col.Add CStr(.Fields(0)), .Fields(1)
.MoveNext
Loop
.Close
End With
Set rs = Nothing
End Sub

Gibt es die Möglichkeit, die "neuen Daten" mit den vorhandenen abzugleichen und nur die neuen zu übernehmen? Momentan fügt er einfach hinzu...

 

Du meinst in der Tabelle "Kundenprojekt"? Im speziellen und im allgemeinen Fall: Selbstverständlich kann man in einer Tabelle nicht nur Datensätze anfügen, sondern auch editieren (Aktualisierungs- statt Anfügeabfrage, rs.Edit statt rs.AddNew).
Das Finden eines Datensatzes, um ihn bearbeiten zu können, kann etwas aufwändiger sein als ein einfaches Anfügen:
Beim Zielrecordset, wie es oben verwendet wird, bräuchte man ein FindFirst nach dem gemeinsamen Schlüssel, ein NoMatch wäre dann die Weiche für AddNew oder Edit. Ein oftmaliges FindFirst in einer großen vorhandenen Datenmenge kann dann aber zeitlich anstrengend sein.
Eine Alternative kann es sein, die momentan erzeugten Datensätze in eine lokale temporäre Tabelle zu schreiben und dann per jeweils einmaliger Aktualisierungsabfrage die vorhandenen Datensätze zu aktualisieren und per Anfügeabfrage nur die neuen Datensätze in der Zieltabelle einzufügen.
Es kann auch eine Alternative sein, sich die Zieltabelle in eine Collection zu holen (analog zu den anderen Tabellen) und in diesem lokalen und indizierten (und damit schnellen Objekt) die Prüfung auf vorhandenen Schlüssel vorzunehmen und danach die Weiche auf Edit bzw. AddNew zu stellen.
Vermutlich gibt es noch weitere Varianten.

Empfehlenswert ist vor allem, besonders wenn die Zieltabelle(n) groß und über ein Netzwerk verbunden ist/sind, nicht Hunderte oder Tausende Einzelprüfungen über das Netzwerk zu jagen, sondern das sinnvoll zusammenzufassen.

 

Seitlicher Einwurf:
Erkenntnis aus Acc2003 - Fehler 4230 ff.:
Code:

 

Hallo!

[Seitlicher Einwurf aus dem Spielfeld von nebenan *biggrin.gif*]
Code:

.. damit ein irrtümliches set dbs = nothing o. ä. keine Auswirkungen nach außen hat.

mfg
Josef

 

\@Josef: Danke für den Hinweis. Die Gefahr des Deinitialisierens der Objektvariablen ist mir bewusst, bei dem übergebenen CurrentDb aber wohl nicht kritisch. An der Stelle wollte ich nur die vorhandene DB-Instanz verwenden und nicht mit CurrentDb jeweils eine neue. Eine ständige wie CurrentDbC wäre dann die einfachere Lösung, hier im Beispiel wollte ich aber nicht zu viel Neues einführen.

Das eigentlich Überraschende für mich: Die Objekte kann man auch mit ByVal als Argumente übergeben und dann in der aufrufenden Prozedur weiterverwenden. In Beispielen, die ich (bewusst) las, wurden Objekte immer als Referenz übergeben, so dass dieses von mir als selbstverständlich angenommen und nicht in Frage gestellt wurde.

 

Also sollte ich mit dem u.g. Code eine Aktualisierungsabfrage (falls vorhandene Daten geändert wurden [in dem Fall Kundenprojektdetails]) und eine Anfügeabfrage (bei nicht vorhandenen Datensätzen) realisieren können?



Private Sub Befehl11_Click()
On Error GoTo Err_Befehl11_Click



Dim db As DAO.Database
Dim rsQ As DAO.Recordset
Dim rsZ As DAO.Recordset
Dim colV As VBA.Collection
Dim colS As VBA.Collection
Dim colP As VBA.Collection
Dim colC As VBA.Collection
Dim colW As VBA.Collection


Set db = CurrentDb
db.Execute "INSERT INTO Vertriebsbereich (Vertriebsbereich) SELECT Q.Vertriebsbereich" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.Vertriebsbereich FROM Vertriebsbereich Z" & _
" WHERE Z.Vertriebsbereich = Q.Vertriebsbereich)", dbFailOnError
db.Execute "INSERT INTO [Sales Manager] (Name) SELECT Q.Salesmanager" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.Name FROM [Sales Manager] Z" & _
" WHERE Z.Name = Q.Salesmanager)", dbFailOnError
db.Execute "INSERT INTO Projekt ([Projekt Kürzel]) SELECT Q.Projekt" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.[Projekt Kürzel] FROM Projekt Z" & _
" WHERE Z.[Projekt Kürzel] = Q.Projekt)", dbFailOnError
db.Execute "INSERT INTO Customer ([Customer No], [Customer Name])" & _
" SELECT Q.[Customer No], Q.[Customer Name]" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.[Customer No] FROM Customer Z" & _
" WHERE Z.[Customer Name] = Q.[Customer Name])", dbFailOnError
db.Execute "INSERT INTO [CRM Status] ([CRM Status]) SELECT Q.[CRM Status]" & _
" FROM Import Q WHERE NOT EXISTS (SELECT Z.[CRM Status] FROM [CRM Status] Z" & _
" WHERE Z.[CRM Status] = Q.[CRM Status])", dbFailOnError

Set colV = New VBA.Collection
FillCollection colV, db, _
"SELECT [Vertriebsbereich ID], Vertriebsbereich FROM Vertriebsbereich"
Set colS = New VBA.Collection
FillCollection colS, db, _
"SELECT [Sales Manager ID], Name FROM [Sales Manager]"
Set colP = New VBA.Collection
FillCollection colP, db, _
"SELECT [Projekt ID], [Projekt Kürzel] FROM Projekt"
Set colC = New VBA.Collection
FillCollection colC, db, _
"SELECT [Customer ID], [Customer Name] FROM Customer"
Set colW = New VBA.Collection
FillCollection colW, db, _
"SELECT [CRM ID], [CRM Status] FROM [CRM Status]"

Set rsZ = db.OpenRecordset("Kundenprojekt")
Set rsQ = db.OpenRecordset("Import", dbOpenSnapshot)
With rsQ
Do While Not .EOF
rsZ.AddNew
rsZ("Customer ID") = colC.Item(.Fields(2))
rsZ("Projekt ID") = colP.Item(.Fields(4))
rsZ("Vertriebsbereich ID") = colV.Item(.Fields(6))
rsZ("Sales Manager ID") = colS.Item(.Fields(5))
rsZ("CRM ID") = colW.Item(.Fields(16))

rsZ.Update
.MoveNext
Loop
.Close
End With
rsZ.Close

Set rsZ = Nothing
Set rsQ = Nothing
Set colV = Nothing
Set colS = Nothing
Set colP = Nothing
Set colC = Nothing
Set colW = Nothing
Set db = Nothing



Exit_Befehl11_Click:
Exit Sub

Err_Befehl11_Click:
MsgBox Err.Description
Resume Exit_Befehl11_Click

End Sub

Private Sub FillCollection(ByVal col As Object, ByVal dbs As Object, ByVal SQL As String)
Dim rs As DAO.Recordset
Set rs = dbs.OpenRecordset(SQL, dbOpenSnapshot)
With rs
Do While Not .EOF
col.Add .Fields(0).Value, .Fields(1).Value
.MoveNext
Loop
.Close
End With
Set rs = Nothing
End Sub



Wie bekomme ich die Fillcollection mit in die Ereignisprozedur des Command Buttons?