Hallo,
ich möchte über VBA aus einer Excel-Tabelle bestimmte Werte in eine Access-Tabelle importieren.
Im Anhang habe ich einen Screen der Tabellen angefügt.
In Excel entsprechen die Spaltenüberschriften den Feldnamen in Access.
Einfügen möchte ich die Werte am Schnittpunkt von DatumWert und IdfWert.
Gespeichert ist die Tabelle Werte.xlsx unter:
D:\A&W-Dokumente\02 Wolfgang\Access
Für Eure Hilfe im Voraus vielen Dank
Gruß - Wolfgang
ExcelWert.jpg
tblWert.jpg
Hallo,
was möchtest Du nun? In eine vorhandene Tabelle neue Tabellenfelder einfügen oder Werte aus der Excel-Liste in schon bestehende Tabellenfelder anhängen, bzw. aktualisieren?
Oder vielleicht auch beides?
Hallo,
Zitat von: DF6GL am Mai 03, 2021, 13:23:28was möchtest Du nun? In eine vorhandene Tabelle neue Tabellenfelder einfügen oder Werte aus der Excel-Liste in schon bestehende Tabellenfelder anhängen, bzw. aktualisieren?
Oder vielleicht auch beides?
Die beste Lösung:
Datum und Werte in Excel vorhanden - Datum in Access nicht vorhanden: Datum und Werte in Access einfügen.
Datum in Access schon vorhanden: Werte aus Excel werden eingefügt, wenn das Feld in Excel nicht Null oder Leer ist.
Zum besseren Verständnis:
Datum und Werte werden in der Excel-Tabelle zugefügt und eventuell auch korrigiert. Diese Daten sollen dann nach Access übernommen werden.
Die IdfWert entspricht der Spaltenüberschrift in Excel, ist schon in Access vorhanden und muss nicht neu zugefügt werden. Lediglich das Datum und die Inhalte (Werte) an den Kreuzungspunkten können neu hinzukommen.
Gruß - Wolfgang
Hallo,
Werden die Daten nur in dem Excel-Sheet geändert?
Dann würde doch eine Verknüpfung reichen, um die Daten in
Access immer aktuell zu haben. Für Access ist das wie eine
eigene Tabelle.
gruss ekkehard
Hallo,
bei einer reinen permanenten Verknüpfung kommt man schnell mit den Zugriffssperrungen in Konflikt.
Mir ist es immer noch nicht klar, ob neue Tabellenfelder hinzukommen sollen oder nicht.
Wie auch immer:
Importiere die Excel-Datei in eine (neue) Access-Tabelle.
Benutze dann Anfüge-, bzw. Aktualisierungsabfragen, um Daten in gewünschter Weise von dort in die vorhanden Access-Tabelle zu übertragen.
Wenn neue Tabellenfelder erforderlich sind, könnten diese mit DDL-Statements oder auch DAO-Methoden in die Tabelle eingebaut werden.
Woher kommt denn "lfdWert" ??
Hallo,
ganz so einfach geht es nicht.
Zitat von: DF6GL am Mai 03, 2021, 14:45:19Importiere die Excel-Datei in eine (neue) Access-Tabelle.
Benutze dann Anfüge-, bzw. Aktualisierungsabfragen, um Daten in gewünschter Weise von dort in die vorhanden Access-Tabelle zu übertragen.
Wenn neue Tabellenfelder erforderlich sind, könnten diese mit DDL-Statements oder auch DAO-Methoden in die Tabelle eingebaut werden.
Woher kommt denn "lfdWert" ??
"lfdWert" ist "IdfWert" - eine Fremd ID aus einer anderen Tabelle und ist identisch mit der Spaltenüberschrift bei Excel.
Neue Tabellenfelder sollen nicht über die Abfrage erzeugt werden.
Wenn ich die Excel-Datei in eine Access-Tabelle importiere wird mir für jede "IdfWert" ein Feld in der Tabelle erstellt. Die kann ich so nicht verwenden.
Gruß - Wolfgang
Hallo,
ZitatWenn ich die Excel-Datei in eine Access-Tabelle importiere wird mir für jede "IdfWert" ein Feld in der Tabelle erstellt. Die kann ich so nicht verwenden.
warum nicht?
Wenn ich es richtig verstehe, dann ist "lfdWert" die Zahl, die in der Excel-Liste in Zeile 1 ab Spalte B jeweils steht.
Deine Importtabelle besteht also aus den Tabellenfeldern (lt. Beispiel):
DatumWert
1
98
94
110
77
73
103
102
102
101
87
106
49
104
Was hindert Dich jetzt daran, für jedes Tabellenfeld (entspr. jeder Excel-Spalte) eine Anfüge- und/oder Aktualisierungsabfrage auf die Access-Tabelle abzusetzen?
Das ganze kann dann auch "automatisch" geschehen, indem per VBA die Tabellenfelder "durchlaufen" werden und jeweils die entspr. Abfrage ausgeführt wird.
Hallo,
die Importtabelle müsste immer neu erstellt werden, wenn ein weiterer Artikel hinzukommt.
Die Zahl in der Excel-Liste in Zeile 1 ab Spalte B heißt "IdfWert" und ist in Access identisch mit einem Fremdschlüssel über den die Werte an den Schnittpunkten von Datum und "IdfWert" einem Artikel in Access zugeordnet werden.
Kommt ein neuer Artikel in Access hinzu, hat er auch eine Zahl ("IdfWert"). Diese Zahl wird dann als Spaltenüberschrift in Excel verwendet, um hierfür Werte zu erfassen.
Ich möchte über einen Aufruf aus Access nach der Excel-Tabelle schauen und neue oder geänderte Daten in der Access-Tabelle anfügen.
Gruß - Wolfgang
Zitatneue oder geänderte Daten in der Access-Tabelle anfügen
Das halte ich für effizient: Grundlagen - SQL ist leicht (4) - Aktualisierung einer Tabelle (https://www.ms-office-forum.net/forum/showthread.php?t=304156)
Deine Exceltabelle ist dafür aber schlecht geeignet, es sei denn, man möchte umständlich und fehleranfällig viele Schleifen laufen lassen.
Empfehlenswert wäre ein Unpivot auf die Tabelle. Das könnte man einfach direkt in Excel über Power Query erledigen lassen, oder aber man löst es über eine temporäre Zwischentabelle (Grundlagen - SQL ist leicht (9) - Pivottabelle in Listtabelle umformen (https://www.ms-office-forum.net/forum/showthread.php?t=338193))
Hallo,
ich würde gerne nochmal auf meine eigentliche Frage zurückkommen.
Ich möchte über VBA den Wert am Schnittpunkt von Datum und einer über die Spaltenüberschrift bestimmten Spalte aus einer Excel-Tabelle in eine Acces-Tabelle einfügen.
Ist das möglich, und wenn ja wie?
Gruß - Wolfgang
Hallo,,,
ZitatIst das möglich
Ja
Zitatund wenn ja wie?
Habe ich vorher doch vorgeschlagen und ebs hat Dir einen alternativen Lösungsweg gezeigt.
Was hakt jetzt und wo?
Willst Du ein vollständiges und funktionierendes DB-Modul?
Hallo,
sorry, aber ich sehe nichts was ich mit VBA in Verbindung bringen würde.
Zu der vorgeschlagenen Importtabelle hatte ich angemerkt, dass diese Tabelle immer geändert werden müsste, wenn eine neue Spalte mit "IdfWert" hinzukommen würde.
Ich habe eher an so etwas gedacht:
strSQL = " INSERT INTO tblWert ... und so weiter
Leider weiß ich nicht, ob und wie man direkt auf eine Excel-Tabelle und die Inhalte zugreifen kann.
Dass die Excel-Tabelle dafür schlecht geeignet ist, hat ebs17 ja schon geschrieben. Daraus schließe ich, dass es nicht unmöglich ist.
Gruß - Wolfgang
Hallo,
Zitataber ich sehe nichts was ich mit VBA in Verbindung bringen würde.
ich schon, wenn Du meine Postings liest: Die Importtabelle per Recordset öffnen, die einzelnen Feldname durchlaufen (--> ldfWert) und jeweils eine Insert-Abfrage ausführen.
ZitatstrSQL = " INSERT INTO tblWert ... und so weiter
Das ist doch (unfertiger) VBA-Code!?
ZitatZu der vorgeschlagenen Importtabelle hatte ich angemerkt, dass diese Tabelle immer geändert werden müsste, wenn eine neue Spalte mit "IdfWert" hinzukommen würde.
Habe ich auch in meinen Postings erwähnt ("neue Tabelle"!). Das macht eine Importfunktion mit Transferspeadsheet doch.. Oder wenn das Excel-Sheet verknüpft ist, ist es immer aktuell. Hierbei gibt es, wie auch schon erwähnt, möglicherweise Schreibkonflikte mit Excel.
ZitatLeider weiß ich nicht, ob und wie man direkt auf eine Excel-Tabelle und die Inhalte zugreifen kann.
Du kannst eine Excel-Datei
als Link im den Navibereich verknüpfen.
Du kannst mit Transferspeadsheet die Excel-Datei importieren oder verlinken.
"direkt" brauchst Du nicht, wenn Du das Sheet in eine Access-Tabelle importierst.
Zitathat ebs17 ja schon geschrieben. Daraus schließe ich, dass es nicht unmöglich ist.
Dann versuch doch wenigstens, diesen Weg mal anzugehen...
Was ist da jetzt insgesamt nicht zu verstehen?
Hallo,
@DF6GLvielen Dank für Deine ausführliche Antwort.
Grundsätzlich möchte ich anmerken, dass ich immer versuche die Vorschläge umzusetzen, allerdings gibt es da oft Verständnisschwierigkeiten und daraus resultierende Fehler.
Es ist das erste Mal, dass ich eine verknüpfte Excel-Tabelle verwendet habe. Dabei habe ich erst später bemerkt, dass diese nicht nur die geänderten Werte sofort anpasst, sondern auch hinzugefügte oder entfernte Spalten anpasst.
Anfangs bin ich davon ausgegangen, dass man bei neuen Spalten die verknüpfte Tabelle in Access neu erstellen müsste.
Nun habe ich das mit der realen Access- und Excell-Datei probiert. Hierbei ist mir aufgefallen, dass das Öffnen dieser Tabelle in Access ca. 1 Minute dauert. Ist das auch der Fall, wenn man die Daten direkt aus der Excel-Tabelle holt und in eine Access-Tabelle einfügt?
Bei meinen Versuchen scheiter ich daran, dass ich nicht weiß, wie ich die Daten am Schnittpunkt (Zeile mit dem Datum und der Spalte deren Feldnamen der IdfWert entspricht) einer Tabelle auslesen und in eine andere Tabelle einfügen kann.
Meine Versuche direkt die Excell-Datei anzusprechen begannen in etwa so:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblAccess", _
CurrentProject.Path & "Wert.xlsx", True, ??? (Schnittpunkt der entsprechenden Spalte und Zeile)
Ein Versuch eine Abfrage auf die verknüpfte Tabelle zu erstellen hat Acces fast zum Absturz gebracht. Das Programm hat ca. 5 Minuten mit Anzeige "keine Rückmeldung" rumgeschafft, ohne ein brauchbares Ergebnis.
Wie die Herkunftstabelle in Excell und Zieltabelle in Access aussieht habe ich im ersten Beitrag beschrieben.
Ich möchte Dich bitten, folgende Frage zu beantworten:
Ist es mit einem SQL-String aus VBA möglich Daten aus der Excel-Tabelle in die Access-Tabelle einzutragen ohne dass dabei lange Bearbeitungszeiten auftreten?
Wenn so ein Zugriff 1 Minute oder länger benötigt, würde ich mir die ganze Quälerei ersparen und die Daten auch in Zukunft von Hand eintragen.
Gruß - Wolfgang
Hallo,
Lad mal die DB und die Excel-Datei hier hoch, komprimiert/repariert und gezippt.
Zitatdass ich immer versuche die Vorschläge umzusetzen, allerdings gibt es da oft Verständnisschwierigkeiten und daraus resultierende Fehler.
Dann solltest Du auch konsequent diesen Weg verfolgen und nicht irgendetwas anderes versuchen, von dem wir hier keine Kenntnis haben. Bei Verständinsschwierigkeiten kannst Du konkret nachfragen.
Die lange Zugriffszeit kann ich jetzt nicht erklären, wenn die Excel-Datei nicht gerade tausende von Zeilen enthält.
Zitatdass ich nicht weiß, wie ich die Daten am Schnittpunkt (Zeile mit dem Datum und der Spalte deren Feldnamen der IdfWert entspricht) einer Tabelle auslesen und in eine andere Tabelle einfügen kann.
Habe ich erzählt und einen prinzipiellen Weg vorgeschlagen (Recordset).
ZitatIst es mit einem SQL-String aus VBA möglich Daten aus der Excel-Tabelle in die Access-Tabelle einzutragen ohne dass dabei lange Bearbeitungszeiten auftreten?
Ja, wobei "lange B.-zeiten" sehr relativ ist.
Alternativ:
https://www.access-o-mania.de/forum/index.php?topic=6969.0
Wenn beim Importieren oder Verknüpfen der Exceltabelle derartige Probleme entstehen, wird die "Tabelle" keine solche sein, die vom DB-zugriff erwartet wird, also keine Liste / intelligente Tabelle mit maximal 255 Spalten, sondern ein Gebilde aus Zeilen und Spalten, wie es gerne mal in Excel zusammengestellt wird.
Man sollte sich also das ganze Ding ansehen, nicht nur einen Ausschnitt, der einem gerade gefällt.
Hallo,
weder die Access-DB noch die Excel-Datei möchte ich hier öffentlich einstellen, das sind private Daten. Sorry.
Die Excel-Datei umfasst 8 Tabellenblätter, von denen die größte Tabelle knapp 1000 Spalten und etwa 4000 Zeilen enthält.
Für den direkten Zugriff aus Access hatte ich aber ein neues Tabellenblatt eingefügt, das besteht aus etwa 30 Spalten und 10 Zeilen. Aber auch hier dauert das Öffnen der verknüpften Tabelle in Access etwa 1 Minute.
Ich habe eine andere Excel-Datei erstellt, die lediglich die für Access benötigte Tabelle enthält und in Access als verknüpfte Tabelle eingefügt. Das Öffnen dieser Tabelle geht schnell (wenige Sekunden).
Was ich benötige und alleine nicht hinbekomme ist die VBA-Anweisung mit der die Daten aus der verknüpften Tabelle in die Access-Tabelle eingefügt werden. Ich habe eine Ansicht der Tabellen angefügt.
ExcelWert.jpg
Eine Ansicht der Excel-Tabelle, in der ersten Spalte das Datum, die oberste Zeile entspricht dem Feldnamen der verknüpften Tabelle.
Access.jpg
tbl_Wert ist die Entwurfansicht der Access-Tabelle, in die die neuen Werte eingetragen werden sollen.
tblWert ist die Entwurfansicht der verknüpften Tabelle.
Der Feldname in tblWert entspricht der Spaltenüberschrift in Excel und IdfWert der tbl_Wert.
Das Datum ist in VBA so deklariert
Dim dteDat As Date
Mit dem SQL-String in VBA möchte das Datum aus dteDat, dieIdfWert und den Wert am Schnittpunkt von Datum mit IdfWert der tblWert in die Tabelle tbl_Wert einfügen.
Die Excel-Datei Wert.xlsx ist unter:
D:\A&W-Dokumente\02 Wolfgang\Access
gespeichert. Im gleichen Pfad ist auch die Access-Datei gespeichert.
Ich hoffe, dass meine Angaben ausreichen um eine SQL-Anweisung zu erstellen.
Gruß - Wolfgang
Excel.jpg
Access.jpg