Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Access-DB mit SQL-Queries über VBA auslesen

Forumthread: Access-DB mit SQL-Queries über VBA auslesen

Access-DB mit SQL-Queries über VBA auslesen
02.02.2015 12:54:42
Gernot
Hallo Forumsmitglieder
Ich nutze eine Exceldatei, in der ich von Hand ca. 50 SQL-Abfragen von Access Datenbanken eingefügt habe. Beim Öffnen der Datei werden alle Abfragen aktualisiert, alles bestens.
Da ich immer wieder neue Datenbanken einpflegen oder eine Datei für neue Kunden komplett neu erstellen muss und ich dafür vorhandene SQL-Queries editiere, ausführe und in die Exceldatei einfüge, frage ich mich, ob es nicht mit einem VBA-Code eleganter ginge?
Die Datenbanken haben immer denselben Namen (Energie14.mdb). Nur die ID-Nummer der Projekte, die Teil des Verzeichnispfades sind, ist unterschiedlich. Ich würde gerne die ID-Nummern in einer Tabelle als Quelle hinterlegen und mit einem VBA-Code automatisch Daten (z.B. Monats- oder Tagesenergieverbräuche) aus der DB einlesen.
In den Datenbanken sind Tabellen für
  • Tages-, Wochen- und Monatswerte jeweils für drei oder sechs Zählereingänge (T_Kanal_1 für Tageswerte des Eingangs 1, T_Woche_Kanal_1 für Wochen- und T_Mon_Kanal_1 für Monatsdaten)

  • sowie eine Tabelle, die die Busadresse und Bezeichnungen des/der Energie-Zähler(eingänge) beinhalten (T_KanalNamen).

  • Die Tabelle T_Version frage ich nicht ab.

  • Hans Herber war so nett und hat das ZIP-File mit der Exceldatei, den Datenbanken und die benutzten Queries hochgeladen:
    https://www.herber.de/bbs/user/95427.zip
    Bitte das ZIP-File nach C:\ entpacken, damit die Pfadangaben zu C:\Energie\000?\Energie14.mdb in den Queries funktionieren.
    Vielen Dank für Ihre Zeit!
    Beste Grüße
    Gernot

    Anzeige

    14
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    mir ist nicht alles klar!? ...
    02.02.2015 14:15:01
    Tino
    Hallo,
    kannst Du mal erklären was genau Du wann abfragen möchtest, dass wird mir nicht so ganz klar.
    Also Du gibst in einer Zelle die ID-Nummern an zwecks Ordner wo die DB sich befindet.
    Und dann, wie soll es weitergehen?
    Welche Daten sollen wohin eingelesen werden?
    Gruß Tino

    Anzeige
    AW: mir ist nicht alles klar!? ...
    02.02.2015 14:42:46
    Gernot
    Hallo Tino
    Danke für dein Interesse!
    Wenn du die Exceldatei öffnest, siehst du darin die Ergebnisse der beiden Queryabfragen, die ich im ZIP-File auch als Textdatei beigefügt habe. Wenn das ZIP-File in C:\ entpackt wurde, findest du im Verzeichnis C:\Energie die Exceldatei, eine Dateiliste und die beiden Queriedateien.
    Die Querydateien habe ich beigefügt, damit ihr testen könnt, ob sie bei Doppelklick darauf Excel starten und das Ergebnis der Abfrage in Excel anzeigen.
    Ich hole mir mit jeder Query im jeweiligen Verzeichnis "C:\Energie\"ID-NummerW\Energie14.mdb aus der Tabelle T_KanalNamen die Adresse und Beschreibungen der Zählereingänge.
    Aus der Tabelle T_Mon_Kanal_1/..._2/ bis /T_Mon_Kanal_6 die Monatsdaten des Energieverbrauchs.
    Als Ergebnis sieht man in der Exceldatei dann die Zähler beschreibenden Informationen und darunter pro Kanal und Zähler 12 Monatswerte. Es können mehr als ein Zähler vorhanden sein. Dann hätte man pro Zähleradresse jeweils 12 Monatswerte als Ergebnis.
    Diese Daten, die per SQL-Abfrage gelesen werden, würde ich gerne für mehr als 50 Datenbanken aus entsprechend vielen unterschiedlichen Verzeichnissen "automatisiert" einlesen, indem mit VBA die Query für jedes Projekt die Daten aus der jeweiligen Energie14.mdb ausliest. Man müsste also in der Lage sein, per VBA eine Query, deren Quellpfad der Datenbank von DB zu DB durch die einzigartige ID-Nummer variiert, so oft auszuführen, wie es ID-Nummern in einer Tabellenspalte gibt.
    Im Zipfile gibt es für 2 ID-Nummern 2 Verzeichnisse, die jeweils eine Energie14.mdb Datenbank enthalten:
    c:\Energie\00081619\Energie14.mdb
    c:\Energie\00087444\Energie14.mdb
    In einer Excelspalte müsste man die beiden ID-Nummern 00081619 und 00087444 eintragen. In diese Spalte würde ich später, wenn es mit VBA geht, alle notwenigen ID-Nummern eintragen. Der VBA-Code sollte die Anzahl der Einträge in dieser Spalte zählen, um festzulegen, wie oft die Query ausgeführt wird. In der Query müsste für jeden VBA-Durchlauf mit VBA-Mitteln der Pfad angepasst werden. Die Ergebnisse sollten in einem Excelblatt untereinander eingefügt werden. Wenn es einfacher ist, könnten die Daten auch auf einem eigenen Tabellenblatt pro ID-Nummer stehen.
    Ich hoffe, das ich es verständlich beschrieben habe.
    Vielen Dank.
    Beste Grüße
    Gernot

    Anzeige
    vielleicht meinst Du so...
    02.02.2015 17:07:09
    Tino
    Hallo,
    die Abfrage erfolgt über Excel VBA
    Beim Öffnen der Datei wird eine Gültigkeit Liste erstellt (in A2)
    Bei Auswahl wird die entsprechende Abfrage gestartet.
    (Zip Datei kann überall entpackt werden, Pfad wird automatisch ermittelt)
    https://www.herber.de/bbs/user/95495.zip
    Gruß Tino

    Anzeige
    AW: vielleicht meinst Du so...
    02.02.2015 17:34:07
    Gernot
    Hallo Tino
    Danke für deine schnelle Hilfe.
    Leider passiert in der Exceldatei nichts, wenn ich eine der beiden IDs auswähle. Wenn ich deinen Code richtig verstehe, wird die Dropdown-Liste erst beim Öffnen der Datei auf Basis der vorhandenen Verzeichnisse erstellt, richtig?
    Beste Grüße
    Gernot

    Anzeige
    hast du vba zugelassen beim öffnen? oT.
    02.02.2015 17:51:08
    Tino

    AW: hast du vba zugelassen beim öffnen? oT.
    03.02.2015 12:31:56
    Gernot
    Hallo Tino
    VBA war zugelassen. In den ActiveX-Einstellungen war "Abgesicherter Modus (beschränkt den Zugriff des Steuerelements auf Ihren Computer)" aktiviert. Das habe ich deaktiviert. Ich habe noch Laufwerk C als sicheren Ort hinzugefügt. Dadurch ging das Makro dann. Danke dir!
    Beim Auswählen einer ID im Drop-Down-Fenster kommt Laufzeitfehler 76:
    In "Modul1" bleibt das Makro StartAbfrage(sPathID$) in Zeile
    "ChDir Left(sPath, Len(sPath) - InStrRev(sPath, "\"))" stehen.
    Die Variable sPath ist mit dem im Drop-Down ausgewählten Verzeichnispfad richtig gefüllt. Trotzdem kommt beim Klick auf Fortsetzen die Fehlermeldung Laufzeitfehler 76 (Pfad nicht gefunden).
    Beste Grüße
    Gernot

    Anzeige
    AW: angepasste Variante....
    03.02.2015 16:25:08
    Gernot
    Hallo Tino
    Vielen Dank für deine Lösung. Leider tut sich nichts, wenn ich das Dropdown nutze. Es kommt auch keine Fehlermeldung.
    Bitte bemühe dich nicht weiter, da Luschis Lösung bereits funktioniert und genau das macht, was ich benötigte.
    Ich danke dir herzlich für deine Hilfe! Es ist unglaublich, welchen Aufwand ihr Experten betreibt, um uns Laien zu helfen. Davor habe ich großen Respekt!
    Beste Grüße
    Gernot

    Anzeige
    ist Deine Entscheidung ;-)
    03.02.2015 21:01:41
    Tino
    Hallo,
    bin aber dennoch der Meinung das meine Variante sinnvoller ist als
    für jeden neuen Datensatz eine neue Abfrage zu generieren.
    Warum es bei dir nicht funktioniert weiß ich allerdings jetzt auch nicht, ich habe xl2010.
    Gruß Tino

    AW: ist Deine Entscheidung ;-)
    03.02.2015 23:50:12
    Gernot
    Hallo Tino
    Bei deiner Lösung wählt man eine Datenbank über die ID-Nummer, die das Verzeichnis festlegt und erhält dann das Ergebnis dieser einen Abfrage. So habe ich es verstanden, aber leider nicht gesehen.
    In der Lösung von Luschi gebe ich die IDs in einer Liste vor und seine Lösung liest alle Datenbanken aus und listet die Ergebnisse untereinander. Genau so wollte ich es haben. Es stimmt, dass für jede DB eine eigene Abfrage in Excel angelegt wird. Das hat aber den Vorteil, dass man die Daten beim Öffnen gleich aktualisiert bekommt.
    Danke nochmal!
    Gute Nacht
    Gernot

    Anzeige
    AW: Access-DB mit SQL-Queries über VBA auslesen
    03.02.2015 08:24:02
    Luschi
    Hallo Gernot,
    hier mal mein Versuch dazu. Infos findest Du in der Tabelle 'System'
    https://www.herber.de/bbs/user/95504.xlsm
    Gruß von Luschi
    aus klein-Paris

    AW: Access-DB mit SQL-Queries über VBA auslesen
    03.02.2015 15:34:43
    Gernot
    Hallo Luschi
    Vielen, vielen Dank. Deine Lösung funktioniert wirklich sehr gut! Ich habe sie gleich mit Originaldaten getestet und vier Objekte eingelesen. Das Ergebnis ist so, wie ich es mir erhofft hatte. Wirklich super Arbeit, danke!
    Perfekt wäre es, wenn man für jede neue Datenbank nicht immer den Access-Treiber im ersten Fenster und die Frage nach Benutzername und Passwort in einem zweiten Fenster manuell bestätigen müsste. Es ist immer der Access-Treiber, den man wählt und mit OK bestätigt und die zweite Abfrage kann man immer ohne irgendwelche Angaben mit OK bestätigen.
    Aber das ist nur noch ein Thema der Bequemlichkeit. Die 60 Märkte, die ich erstellen muss, kann ich damit einfach erledigen. Bin dir sehr dankbar!
    Liebe Grüße
    Gernot

    Anzeige
    AW: Access-DB mit SQL-Queries über VBA auslesen
    03.02.2015 18:42:05
    Luschi
    Hallo Gernot,
    das scheint etwas zu sein, wo M$ denkt, das es ein Sicherheitsapspekt ist, beim 1. Mal noch mal nachzufragen. Wahrscheinlich werden diese Informationen in der 'Registy' - der Windows-Datenbank hinterlegt. Und wen das eben beim 1. mal noch nicht drinsteht- wird lieber noch mal nachgefragt!
    Mein eigentlicher Verdacht ist aber der M$-Query-Treiber zur Datenbank, der nur dann aktiviert wird, wenn die Verbindung schon mal aufgebaut wurde
    Gruß von Luschi
    aus klein-Oaris

    Anzeige
    AW: Access-DB mit SQL-Queries über VBA auslesen
    03.02.2015 19:56:42
    Gernot
    Danke Luschi für deine klasse Lösung und die schnelle Hilfe.
    Beste Grüße
    Gernot
    ;
    Anzeige
    Anzeige

    Infobox / Tutorial

    Access-DB mit SQL-Queries über VBA auslesen


    Schritt-für-Schritt-Anleitung

    1. Vorbereitung: Stelle sicher, dass die Access-Datenbanken (Energie14.mdb) im Verzeichnis C:\Energie\000?\ vorhanden sind. Jede ID-Nummer hat ein entsprechendes Verzeichnis.

    2. ID-Nummern in Excel eintragen: Erstelle eine Liste der ID-Nummern in einer Excel-Spalte. Diese ID-Nummern werden später für die SQL-Abfragen verwendet.

    3. VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor zu öffnen.

    4. Modul erstellen:

      • Klicke auf Einfügen > Modul.
      • Füge den folgenden VBA-Code ein:
    Sub StartAbfrage(sPathID As String)
        Dim sPath As String
        sPath = "C:\Energie\" & sPathID & "\Energie14.mdb"
    
        ' Hier kannst Du Deine SQL-Abfrage definieren
        Dim sqlQuery As String
        sqlQuery = "SELECT * FROM T_Mon_Kanal_1" ' Beispiel für eine SQL-Abfrage
    
        ' Verbindungsaufbau zur Access-Datenbank
        Dim conn As Object
        Set conn = CreateObject("ADODB.Connection")
        conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";"
    
        ' Abfrage ausführen und Ergebnisse in Excel einfügen
        Dim rs As Object
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open sqlQuery, conn
    
        ' Ergebnisse in Excel einfügen
        Dim i As Integer
        For i = 0 To rs.Fields.Count - 1
            Cells(1, i + 1) = rs.Fields(i).Name
        Next i
        Cells(2, 1).CopyFromRecordset rs
    
        ' Verbindung schließen
        rs.Close
        conn.Close
    End Sub
    1. Makro ausführen: Führe das Makro mit den ID-Nummern aus, um die SQL-Abfragen durchzuführen und die Daten in Excel anzuzeigen.

    Häufige Fehler und Lösungen

    • Laufzeitfehler 76 (Pfad nicht gefunden):
      • Überprüfe, ob der Pfad korrekt ist und die Datenbank in dem angegebenen Verzeichnis existiert.
    • Fehlende Berechtigungen:
      • Stelle sicher, dass VBA in den Excel-Einstellungen aktiviert ist und keine Sicherheitswarnungen blockieren.
    • Keine Ergebnisse:
      • Prüfe die SQL-Abfrage auf Syntaxfehler oder ob die Tabellen in der Datenbank existieren.

    Alternative Methoden

    Eine alternative Methode zur Verwendung von VBA wäre, die Abfragen direkt in Access zu erstellen und die Ergebnisse dann in Excel zu importieren. Dies kann über das Menü Daten > Daten abrufen und transformieren in Excel erfolgen, um die Access-Datenbank zu verbinden, ohne VBA-Code zu schreiben.


    Praktische Beispiele

    1. Monatsdaten abfragen:

      • Verwende die SQL-Abfrage SELECT * FROM T_Mon_Kanal_1 um alle Monatsdaten für Kanal 1 abzurufen.
    2. Daten für mehrere ID-Nummern:

      • Erstelle eine Schleife in VBA, die jede ID-Nummer aus der Excel-Liste abruft und die Abfrage für jede ID ausführt.

    Tipps für Profis

    • Verwende Parameter in SQL-Abfragen: Dies ermöglicht eine flexiblere Handhabung der Abfragen.
    • Optimierung der Abfragen: Stelle sicher, dass die SQL-Abfragen effizient sind, um Ladezeiten zu minimieren.
    • Fehlerprotokollierung: Implementiere eine Fehlerbehandlung in Deinem VBA-Code, um Probleme beim Ausführen der Abfragen zu erkennen und zu dokumentieren.

    FAQ: Häufige Fragen

    1. Wie kann ich die ID-Nummern automatisch aus einer anderen Tabelle abrufen?
    Du kannst eine Schleife erstellen, die die ID-Nummern aus der Tabelle liest und die StartAbfrage-Funktion für jede ID aufruft.

    2. Welche Excel-Version benötige ich?
    Die oben genannten VBA-Codes sollten in Excel 2010 und höher funktionieren, achten Sie darauf, dass der Microsoft Access-Treiber installiert ist.

    3. Was ist der Vorteil von VBA gegenüber dem direkten Import aus Access?
    VBA ermöglicht eine automatisierte und flexible Handhabung der Abfragen, was besonders nützlich ist, wenn viele Datenbanken verwaltet werden müssen.

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Entdecke mehr
    Finde genau, was du suchst

    Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

    Suche nach den besten Antworten
    Unsere beliebtesten Threads

    Entdecke unsere meistgeklickten Beiträge in der Google Suche

    Top 100 Threads jetzt ansehen
    Anzeige