AW: Datenbankabfrage (Access) via VBA Excel
21.07.2021 15:20:25
ChrisL
Hi Nep
Das Problem besteht nach meinem Verständnis darin zu ermitteln, welche Datensätze neu hinzugekommen sind. (Update von bestehenden Datensätze blende ich aus)
1.
Bevorzugt bringst du dich gar nicht erst in solch eine Situation d.h. die Bearbeitung erfolgt ausschliesslich in der Quelle.
2.
Wenn die Quellbearbeitung nicht geht (z.B. externe Quelle ohne Zugriff), dann sollten die Quelldaten unangetastet bleiben. Allfällige Daten-Ergänzungen erfolgen in einer separaten Tabelle und bei Bedarf werden die Ergänzung abschliessend mit den Quelldaten verbunden (z.B. über einen Primärschlüssel wie z.B. Kundennummer).
3.
Wenn obiges nicht geht und keine Bearbeitung von bestehenden Datensätzen stattfindet, dann kannst du ein Vorgehen wie von dir angedacht in Erwägung ziehen. Im Idealfall erfolgt der selektive Import dann über einen Timestamp (Datum-/Zeitangabe) des letzten Updates. D.h. du selektierst nur diejenigen Daten aus der Access-DB, welche neueren Datums sind.
4.
Wenn du keinen Zeitstempel zur Verfügung hast, dann benötigt es mindestens eine eindeutige Nummer für den Abgleich von neuen und bestehenden Datensätzen. Da Daten über 2 verschiedene DB's abgeglichen werden müssen, müssten die Daten entweder Zeilenweise abgeglichen werden oder alle Daten müssen zum Vergleich in eine gemeinsame DB kopiert werden.
Für grosse Datenmengen ist das Vorgehen somit ungeeignet.
Weil ich grad Zeit und Lust hatte, habe ich für Variante 4 einen Muster-Code erstellt. Aufbau:
Access-DB, Tabelle 1:
Spalte 1: ID (= eindeutige Nummer/Ganzzahl)
Spalte 2: MyName (= kurzer Text)
Excel:
Tabelle1 zur Bearbeitung (Zeile 1 hat Titel: ID;MyName)
Tabelle2 zur temporären Verwendung, kann ausgeblendet werden (Zeile 1 hat Titel: ID;MyName)
Vorgehen:
- Die ganze Tabelle1 aus Access wird nach Excel Tabelle2 kopiert
- Access wird geschlossen
- Anhand der Spalte ID erfolgt innerhalb Excel ein Vergleich von Tabelle1 (Arbeitstabelle) und Tabelle2 (Access-Kopie). neue Datensätze aus Tabelle2 werden unten an Tabelle1 angefügt
- Der Inhalt der temporären Excel-Tabelle2 wird wieder gelöscht
' Voraussetzung ist, dass in Tabelle1 und Tabelle2 jeweils in Zeile 1 die Titel (Datenfelder) bezeichnet sind
' Die beiden Tabellen müssen ein DB-Format aufweisen (keine verbundenen Zellen, Verzierungen, Schnickschnack etc.)
' Die Tabelle enthält 2 Datenfelder (Spalten): ID = eindeutige Nummmer, MyName = Text
' Es werden lediglich neu hinzukommende Datensätze (Zeilen) ergänzt, nicht aber bestehende Datensätze aktualisiert
Sub Abgleich()
Dim objCon As Object, objRS As Object, strSQL As String
' Verbindungsstring (je nach Excel-Version unterschiedlich)
Const strConnection As String = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source="
' Pfad der aktuellen Excel-Datei inkl. Erweiterung
Dim strPfadXL As String: strPfadXL = ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
' auf Tabelle1 findet die Bearbeitung statt und neue Datensätze werden ergänzt
Const strTB1 As String = "Tabelle1"
' auf Tabelle2 erfolgt temporär ein Import der ganzen Access-DB
Const strTB2 As String = "Tabelle2"
' Pfad der Access-DB
Const strPfadDB As String = "C:\Users\j7192\Desktop\Master\Excel\Excel_Abfrage.accdb"
' Name der Tabelle innerhalb der Access-DB
Const strTB3 As String = "Tabelle1"
' ADODB-Verbindung erstellt
Set objCon = CreateObject("ADODB.Connection")
' Access-DB öffnen resp. Verbindung herstellen
objCon.Open strConnection & strPfadDB
' SQL-Statement definieren
strSQL = "SELECT * FROM " & strTB3
' sämtliche Datensätze holen
Set objRS = objCon.Execute(strSQL)
' alle Daten in Excel-TB 2 einfügen
Worksheets(strTB2).Range("A2").CopyFromRecordset objRS
' Datensatz und Verbindung schliessen
objRS.Close
objCon.Close
' aktuelle Exceldatei öffnen resp. Verbindung herstellen
objCon.Open strConnection & strPfadXL
' SQL Statement definieren
' Muster ANTI JOIN:
'strSQL = "SELECT Tabelle2.ID, Tabelle2.MyName FROM Tabelle2 LEFT JOIN Tabelle1 ON Tabelle1.ID = Tabelle2.ID WHERE Tabelle1.ID IS NULL;"
strSQL = "SELECT [" & strTB2 & "$].[ID],[" & strTB2 & "$].[MyName] FROM [" & strTB2 & "$] LEFT JOIN [" & strTB1 & "$] ON [" & _
strTB1 & "$].[ID] = [" & strTB2 & "$].[ID] WHERE [" & strTB1 & "$].[ID] IS NULL;"
' neue Datensätze holen
Set objRS = objCon.Execute(strSQL)
' neue Datensätze unten an Tabelle1 anfügen
Worksheets(strTB1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).CopyFromRecordset objRS
' temporäre Tabelle2 wieder löschen
Worksheets(strTB2).Rows("2:" & Rows.Count).Delete
' Verbindungen schliessen, Variablen zurücksetzen
objRS.Close
objCon.Close
Set objRS = Nothing
Set objCon = Nothing
End Sub
cu
Chris