Oktober 22, 2020, 11:55:54

Neuigkeiten:

Wenn ihr euch für eine gute Antwort bedanken möchtet, im entsprechenden Posting einfach den Knopf "sag Danke" drücken!


Ist es mögliche bestimmte Zeilen aus Excel IN eine Access-DB zu exportieren?

Begonnen von derilzemer, Juni 12, 2020, 17:30:01

⏪ vorheriges - nächstes ⏩

derilzemer

Hallo,
ich habe eine funktionale Access-DB mit rund 3000 Datensätzen. Die DB ist ziemlich umfangreich und ist für ein Sammlerhobby entworfen (Spielkarten).
Die DB hat eine Funktion, dass man einzelne Karten mit ihren Werten dokumentieren kann. Dies mache ich aber in Excel, weil es einfacher ist als wenn man das in Access das über das Userinterface mit vielen Klicken bewerkstelligen muss.
Bisher habe ich die Daten immer in eine Exceltabelle (3 Tabellenreiter, tblQuartette, tblQuKarten und tblKartenmerkmale) geklopft und dann nach einer bestimmten Menge die Tabellen in Access importiert . Alte Tabelle in Access gelöscht, aktuelle Tabelle importiert. Man kann ja auch Datensätze importieren und an bestehender Tabelle "anfügen". Genau diese Funktion wäre mir eigentlich am liebsten. In Excel bereitet man die Daten vor und exportiert dann aus Excel die Datenzeilen in eine bestehende Tabelle in Access. Geht das überhaupt, oder muss man immer den Weg über Import in Access gehen? Wenn das geht, gibt es dafür schon definierte Funktionen in Form von Script oder ähnlichem, was man dann nur noch für seine eigenen Zwecke wie Pfad, Tabellenname etc. "anpassen" muss?

Vielen Dank vorab für die Info
Andreas
Grüße und Dank im voraus
Andreas

ebs17

Ordentliche Tabellen in Excel kann man auch in eine DB (temporär) verknüpfen. Dann könntest Du Dir mit Abfragen sehr viele Wünsche erfüllen, ob nun mit allen Datensätzen oder vielen bestimmten Datensätzen oder mit einzelnen Datensätzen.
Mit freundlichem Glück Auf!

Eberhard

Sebi

Hallo,

Ja das geht. Egal ob dauerhaft oder temporär.
Du kämmst dir in Access auch ein Formular in Datenblattansicht erstellen und die Daten direkt eingeben.
An sich genau wie Excel.
Lieben Gruß
Liebe Grüße Sebi

derilzemer

Hi zusammen,
ich habe mir davor wohl nicht genug Gedanken bzw. habe das wohl was falsch gemacht.
Ich habe die Tabellen tblQuartette, tblQuKarten und tblKartenMerkmale in der Access DB mit dem Zusatz _old umbenannt, weil Access immer meckerte wenn ich verknüpfen wollte. Dann habe ich über das Kontextmenü zu den umbenannten Tabellen die externe Exceldatei (.xslm ... also mit Makros) mit den Tabellenblättern tblQuartette, tblQuKarten und tblKartenMerkmale verknüpft. Hat alles funktioniert und gut. Aber wenn ich die Verknüpfung mal lösen sollte, dann sind auch die Daten weg. Das ist ja nicht unbedingt was ich will. Ich will ja durch das verknüpfen nur eine "erleichterte" Dateneingabe. Ich kann in der xslm mit 2 Makros viele Sachen vordefinieren, deswegen "erleichtert".
Deshalb habe ich die Verknüpfung noch einmal aufgelöst und nun geht meine Excel mit dem Makro nicht mehr  :-\.
Es kommt immer die Meldung "Excel kann leider keine zwei Arbeitsmappen mit gleichem namen zugleich öffnen". Danach habe ich eine Sicherung der Datenbank wieder hergestellt, auch von der Exceldatei, aber die Meldung kommt weiterhin. Google ist zur Fehlermeldung wenig hilfreich, zumindest keine Lösungsansätze.
Was ist passiert, hat irgend jemand einen Rat?

Danke vorab und Gruß
Andreas
Grüße und Dank im voraus
Andreas

Sebi

Hallo,
An sich versucht du wohl die Datei zu öffnen. Vermutlich per Makro.
Und wenn excel  2 mal die Datei mit identischem Namen öffnet kommt die Meldung.
Liebe Grüße Sebi

ebs17

Zitatxslm

Damit ist klar, dass VBA eingesetzt werden kann. Da würde ich doch einen einfachen Zugriff auf die Datenbank per DAO empfehlen. ADODB geht natürlich auch.

=> Setzen Verweis auf Microsoft Office XX.0 Access Database Engine Object
Sub EinfachAnfuegen()
   Dim db As DAO.Database
   Dim sSQL As String

   Set db = OpenDatabase("X:\irgendwo\Deine.accdb")
   sSQL = "INSERT INTO TabelleX (Feld1, Feld2, Feld3)" & _
          " SELECT Spalte1, Spalte2, Spalte3 FROM" & _
          " [excel 12.0 xml;hdr=yes;imex=1;DATABASE=" & ThisWorkbook.FullName & "].[Tabelle1$]"
   db.Execute sSQL, dbFailOnError
   MsgBox "Es wurden " db.RecordsAffected & " Datensätze angefügt."
   db.Close
   Set db = Nothing
   
End Sub


So, nun könnte man noch etwas mehr Entwicklerintelligenz in die SQL-Anweisung einfließen lassen. Es können auch mehrere Abfragen nacheinander ausgeführt werden.
Mit freundlichem Glück Auf!

Eberhard

derilzemer

Hallo Eberhard,
der Verweis in Access sitzt schon, siehe Bild .
Das bedeutet, ich lege in Excel je ein Makro an und teste mal (wie man das in einem machen könnte weiß ich nicht). Das würde dann so aussehen

Sub AnfuegenQuartette()
   Dim db As DAO.Database
   Dim sSQL As String

   Set db = OpenDatabase("C:\Users\linus\Familie\Quartette\Datenbank\Access-DB\QuartettDB.accdb")
   sSQL = "INSERT INTO tblQuartette (QuartettID, SpielID_F, QuartettKz, QuartettBezeichnung)" & _
          " SELECT Spalte1, Spalte2, Spalte3, Spalte4 FROM" & _
          " [excel 12.0 xml;hdr=yes;imex=1;DATABASE=" & ThisWorkbook.FullName & "].[tblQuartette$]"
   db.Execute sSQL, dbFailOnError
   MsgBox "Es wurden " db.RecordsAffected & " Datensätze angefügt."
   db.Close
   Set db = Nothing
   
End Sub

Sub AnfuegenQKarten()
   Dim db As DAO.Database
   Dim sSQL As String

   Set db = OpenDatabase("C:\Users\linus\Familie\Quartette\Datenbank\Access-DB\QuartettDB.accdb")
   sSQL = "INSERT INTO tblQuartette (QuKartenID, QuartettID_F, KartenNr, Kartenbezeichnung, ModellTyp, Druckdatum, BildFlaggen)" & _
          " SELECT Spalte1, Spalte2, Spalte3, Spalte4, Spalte5, Spalte6, Spalte7 FROM" & _
          " [excel 12.0 xml;hdr=yes;imex=1;DATABASE=" & ThisWorkbook.FullName & "].[tblQuKarten$]"
   db.Execute sSQL, dbFailOnError
   MsgBox "Es wurden " db.RecordsAffected & " Datensätze angefügt."
   db.Close
   Set db = Nothing
   
End Sub

Sub AnfuegenKartenMerkmale()
   Dim db As DAO.Database
   Dim sSQL As String

   Set db = OpenDatabase("C:\Users\linus\Familie\Quartette\Datenbank\Access-DB\QuartettDB.accdb")
   sSQL = "INSERT INTO tblQuartette (KartenMerkmalID, QuKartenID_F, MerkmalID_F, Eintrag)" & _
          " SELECT Spalte1, Spalte2, Spalte3, Spalte4 FROM" & _
          " [excel 12.0 xml;hdr=yes;imex=1;DATABASE=" & ThisWorkbook.FullName & "].[tblKartenMerkmale$]"
   db.Execute sSQL, dbFailOnError
   MsgBox "Es wurden " db.RecordsAffected & " Datensätze angefügt."
   db.Close
   Set db = Nothing
   
End Sub

Eberhard, werfe doch mal bitte einen prüfenden Blick darüber, da ich kein VBA Held bin :-[ .
Die Spalte1 (AutoWert) muss man ja auch unbefüllt/leer mitgeben, die setzt dann Access automatisch, oder? Setze ich die 3 Makros auf die jeweilige Tabelle oder auf DieseArbeitsmappe im VBA Editor? Egal wo ich das setze, es kommt ein Fehler beim Kompilieren von VBA Project.
Fehler beim kompilieren:
Benutzerdefinierter Typ nicht definiert und markiert dabei db As DAO.Database.

Dann bleibt noch eine Excelspezifische Frage zu Makros.
Nehmen wir an die Excel-Datei liegt auf dem Desktop. Nun integriere ich ein Makro, teste es und geht. Ich habe mir dazu zwei Makrobuttons in den Symbolleisten von Excel angelegt, siehe Bild . Wenn ich die Datei nun an einen anderen Ort verschiebe, dann gehen die Buttons nimmer! Es reicht schon wenn ich die Datei umbenenne. Beim verschieben oder umbenennen kommt die Fehlermeldung Wir konnten C:\Users\linus\Desktop\tblQuKarten.xslm nicht finden. Wurde das Objekt vielleicht verschoben, umbenannt oder gelöscht?
Ich kann aber nirgendwo etwas finden, dass ich den Pfad anpassen kann oder so etwas. Nach dem verschieben an einen anderen Ort habe ich die Makros entfernt und wieder hinzugefügt, aber die Fehlermeldung bleibt dann die gleich. Was übersehe ich?
Gruß und Danke
Andreas

Grüße und Dank im voraus
Andreas

ebs17

ZitatSetze ich die 3 Makros auf die jeweilige Tabelle oder auf DieseArbeitsmappe im VBA Editor?

Eigentlich in einem Standardmodul.

Mit ThisWorkbook.FullName erfolgt doch eine Referenzierung auf sich selber. Da dürfte es keine Probleme mit einer Dateiverschiebung geben. Es wird ja nur der vollständige Pfad ermittelt und eingesetzt. Ist denn der genannte richtig?
Mit freundlichem Glück Auf!

Eberhard

derilzemer

Hallo Eberhard,
ich habe jetzt die 3 Makros in ein Standardmodul eingefügt. Weiterhin habe ich dann noch Verweise setzen müssen, um die Fehlermeldung wegen den benutzerdefinierten Typen weg zubekommen.
Jetzt erhalte ich aber beim kompilieren einen weiteren Syntaxfehler. Dabei springt er auf diese Zeile

[b][i] MsgBox "Es wurden " db.RecordsAffected & " Datensätze angefügt."[/i][/b]

Kannst du mir da noch mal unter die Arme greifen? Und auch noch die Frage, ob ich bei
" SELECT Spalte1, Spalte2, Spalte3, Spalte4, Spalte5, Spalte6, Spalte7 Spalte1 usw. lassen kann, oder ob ich die Spalten in der Excel benamen muss, um einen Bezug zu schaffen?

Wenn ich also einen Namen für die Spalte definiere, wie z.B. auch den Feldnamen in der Access-DB QuKartenID, müsste ich anstatt Spalte1 QuKartenID einsetzen, richtig?

Sorry wenn ich solche Fragen stell, aber ich bin nun mal nicht der Excel-Access Gott vor dem Herrn, auch wenn ich den Titel Access-Meister habe. Den bekommt man ja wohl, wenn man viele Beiträge hat.
Gruß Andreas


Grüße und Dank im voraus
Andreas

ebs17

Frage 1: Da fehlt noch ein & bei der Stringzusammensetzung. Das sollte man aber womöglich alleine erkennen können.

Frage 2: Bei einer "ordentlichen" Tabelle geht man auch in Excel davon aus, dass es da Spaltennamen in der ersten Zeile gibt, idealerweise ohne Sonderzeichen.
Dann genügt aber die einfache Nennung bei der Zieltabelle und der Quelltabelle. Die Zuordnung erfolgt dann der Reihenfolge nach, die Namen müssen also nicht gleich sein.
Mit freundlichem Glück Auf!

Eberhard

derilzemer

Hi,
danke für die Hinweise.

ZitatDa fehlt noch ein & bei der Stringzusammensetzung. Das sollte man aber womöglich alleine erkennen können.


Das soll jetzt nicht motzig sein, aber das ist wie mit meinem erlernten Beruf (Zimmermann). Wüsstest du jetzt, wie man einen Kehlsparren anreißt und ausarbeitet? Wenn du nicht vom Fach bist, dann eher nicht. Und genau deswegen wende ich mich an solch ein Forum, denn auch in meinem Beruf gibt es Gesellen, die solche Sachen nie lernen oder begreifen, dafür sind dann die Meister da.

Ich habe jetzt auf Verdacht hin irgendwo, wo es aus meienr Sicht Sinn macht ein & gesetzt und konnte danach auch kompilieren.
MsgBox "Es wurden " & db.RecordsAffected & " Datensätze angefügt."

Gruß Andreas
Grüße und Dank im voraus
Andreas

derilzemer

Hallo Eberhard,
ich habe jetzt mal nach den Anpassungen einen ersten Test gemacht. Folgendes Makro habe ich zur Ausführung gebracht:

Sub AnfuegenQuartette()
   Dim db As DAO.Database
   Dim sSQL As String

   Set db = OpenDatabase("C:\Users\linus\Familie\Quartette\Datenbank\Access-DB\QuartettDB.accdb")
   sSQL = "INSERT INTO tblQuartette (QuartettID, SpielID_F, QuartettKz, QuartettBezeichnung)" & _
          " SELECT SpalteA, SpalteB, SpalteC, SpalteD FROM" & _
          " [excel 12.0 xml;hdr=yes;imex=1;DATABASE=" & ThisWorkbook.FullName & "].[tblQuartette$]"
   db.Execute sSQL, dbFailOnError
   MsgBox "Es wurden " & db.RecordsAffected & " Datensätze angefügt."
   db.Close
   Set db = Nothing
   
End Sub


Dabei trat folgender Fehler auf.
Laufzeitfehler '3061':
4 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben.

Der Debug springt dann in im Makro in diese gelb markierte Zeile
db.Execute sSQL, dbFailOnError

Das ganze habe ich auch mit Spalte1, Spalte2 usw. versucht, Fehler bleibt gleich. Da es von 4 Parameter spricht, habe ich dann die Zeile
" SELECT SpalteA, SpalteB, SpalteC, SpalteD FROM" & _
mit den Spaltenüberschriften versehen.
" SELECT QuartettID, QuartettKz, SpielID_F, QuartettBezeichnung FROM" & _

Beim erneuten ausführen kommt dann
Laufzeitfehler '3071':
Dieser Ausdruck wurde falsch eingegeben, oder er ist zu komplex, um ausgewertet zu werden. Beispielsweise kann ein numerischer Ausdruck zu viele komplizierte Elemente enthalten. Vereinfachen sie den Ausdruck, indem sie Teile des Ausdrucks Variablen zuweisen.

Der Debug springt dann auch hier im Makro in diese gelb markierte Zeile
db.Execute sSQL, dbFailOnError

Der connect zur DB scheint ja zu klappen, aber ich weiß jetzt noch nicht mal was der eigentliche Fehler ist, die Bezeichnung der Spalten?

Ich habe auch mal die Excel mit den Makros als zip angehängt, da xlsx nicht akzeptiert wird.

Danke und Gruß
Andreas
Grüße und Dank im voraus
Andreas

ebs17

MsgBox: Das war nur Zierrat und könnte ebenso ersatzlos gestrichen werden. Bei einem richtigen Arbeiten stören Meldungen über Selbstverständlichkeiten  dann eher und sind nur Unterbrechungen.

Zitat4 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben.

So etwas rührt z.B. von nichterkannten Bezeichnungen für Felder und Tabellen (Tippfehler, nicht zugreifbar) oder auch von Datentypfehlern bei der Zuordnung Quell- zu Zielfeld her. Zum Vergleich bräuchte man also auch die DB.
Zusätzlich kennt Excel selber keine Datentypen für ganze Spalten, SQL benötigt so etwas aber zwingend. Daher werden solche Datentypen nach ersten Inhalten interpretiert, was zu Überraschungen führen kann. Was dann bei einer total leeren Spalte passiert, liegt nicht in meinen Erfahrungen.

Nebenbei: Die hier gewählte Interaktion zwischen Excel und Access ist ein Stück außerhalb des üblichen Standards => selbstgewählte Erhöhung der Schwierigkeit. Das zu kombinieren mit dem Kokettieren mit eigenem (Noch)Nichtwissen ist ungünstig.

ZitatDer connect zur DB scheint ja zu klappen

Was wurde ausgeführt, damit dieser allgegenwärtige Fehler auf einmal klaglos verschwand?
Mit freundlichem Glück Auf!

Eberhard

derilzemer

Hallo Eberhard,
danke für deine Analyse.
Dass ich gesagt habe, dass der connect an sich zur DB klappt, entnahm ich der Fehlermeldung, dass nur etwas an den Dateninfos wie SpalteA etc. der Excel nicht passt, also nur eine Annahme.

Wenn ich die Daten aus der Excel "markiere" und via copy und Paste in die Tabelle der Access-DB einfüge, dann geht das ohne weitere Probleme. Von daher ging ich die ganze Zeit davon aus, daß KEIN Problem bezüglich den Datentypen vorlag, sondern die Fehlermeldung schlicht und einfach darin liegt, dass beim select aus der Excel mit z.B Spalte1 oder SpalteA einfach "nur" den falsche Namen der Spalte nutze, dass dort eben der Hund begraben liegt.

Um die Fehlerfindung voranzutreiben, kann ich höchstens noch die Feldtypen mit ihrem Allgemeinen Einstellungen liefern, sowie die der Exceldatei.
Wenn das nicht ausreichen würde kann ich natürlich die Excel und eine leere DB zur Verfügung stellen um dem Fehler auf die schliche zu kommen. Ich selbst sehe mich da aktuell nicht mehr in der Lage den Fehler herauszufinden :-\
Welche Option wäre denkbar, in welcher du mich unterstützen könntest?

Gruß und Danke
Andreas
Grüße und Dank im voraus
Andreas

ebs17

"die Excel und eine ... DB" (mit vollständigen vorhandenen Tabellendefinitionen, Indizes, Beziehungen) mit einigen belastbaren wenigen Beispieldaten wäre natürlich schön, dass man selber sehen und probieren könnte statt sich Inhalte aus Erzählungen zusammenreimen zu müssen.

Daneben hätte ein solches "komplettes" Projekt auch einen Informations- und Nutzungswert für Dritte.
Mit freundlichem Glück Auf!

Eberhard