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

vorsortierte Daten aus Access per VBA importieren

vorsortierte Daten aus Access per VBA importieren
04.12.2019 13:22:49
Klaus
Hallo,
mit diesem Makro ziehe ich eine gesamte Access Datenbank in eine Exceltabelle. Hier kann ich _ die Tabelle mit Excel-Boardmitteln komfortabel sortieren und filtern.

Const pfad As String = "H:\access test"         'Access DB PFad
Const myAccessDB As String = "RRC_WIP.accdb"    'Access DB Dateiname
Const myDB As String = "RepairMiddle"           'DB-Tabellenname im Access
Const myTable As String = "Tabelle1"            'in dieser Tabelle stehen die Daten für Access
Sub Get_Access_Repairlist()
' Übernahme einer kompletten Access-Tabelle
Dim con As ADODB.Connection
Dim datei As String
Dim fld As ADODB.Field
Dim rs As ADODB.Recordset
Dim spalte As Long
Dim ws As Worksheet
Set ws = Sheets(myTable)
' (1) Verbindung zur Datenbank herstellen
datei = pfad & "\" & myAccessDB
Set con = New ADODB.Connection
con.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & datei
' (2) Recordset erstellen
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open Source:=myDB, _
ActiveConnection:=con, _
CursorType:=adOpenKeyset, _
LockType:=adLockReadOnly, _
Options:=adCmdTableDirect
' (3b) Spaltenüberschriften schreiben
spalte = 1
For Each fld In rs.Fields
ws.Cells(1, spalte) = fld.Name
spalte = spalte + 1
Next fld
' (3c) Sätze des Recordsets auflisten
ws.Range("A" & 2).CopyFromRecordset _
Data:=rs, _
MaxRows:=ws.Rows.Count - 1, _
MaxColumns:=ws.Columns.Count
' (4) Recordset und Verbindung schließen
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
End Sub

Jetzt habe ich eine Datenbank mit vielen tausend oder meinetwegen auch vielen million Einträgen, auf jedem Fall eine Menge die Excel überfordert. Kann ich die Access Datenbank auch gefiltert nach Excel holen? In Pseudocode stelle ich mir das so vor, um alle Artikel aus Holz zu kopieren:
for each item in Datenbank
if item.rs!Material = "Holz then item.AddTo Recordset
next item
ws.Range("A" & 2).CopyFromRecordset

Gibts da eine Möglichkeit? Vielleicht einen SQL Befehl?
Lg,
Klaus

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: vorsortierte Daten aus Access per VBA importieren
04.12.2019 14:03:13
Günther
Ja Klaus,
du kannst in Access eine (gefilterte) Abfrage erstellen und diese dann per VBA importieren.
Oder du nutzt die Möglichkeiten deiner Version, sprich Power Query. Da wird Excel auch bei mehr als 1 Mio Datensätzen nicht abschmieren sondern wie geschmiert laufen. ;-) Natürlich ohne VBA…
Gruß
Günther
AW: vorsortierte Daten aus Access per VBA importieren
05.12.2019 06:13:36
Klaus
Hallo Günther,
oh ja - Power Query, davon lese ich in letzter Zeit immer wieder. Muss ich unbedingt mal ausprobieren, da hast du Recht.
In diesem Fall ist mir eine VBA-Lösung aber lieber. Die Exceltabelle wird nicht von mir, sondern von meinen Technikern bedient, und die verstehen nichts von Excel. Da ist ein Button mit Makro der alles erledigt die bessere Wahl als eine manuelle, VBA-freie Lösung.
LG,
Klaus
Anzeige
AW: vorsortierte Daten aus Access per VBA importieren
04.12.2019 14:16:17
JoWE
Hallo Klaus,
warum erstellst Du Dir in Deiner Datenbank nicht einfach eine Abfrage, die Deine Kriterien berücksichtigt, eine neue Tabelle die in Deiner Datenbank gespeichert wird?
Die Daten der so erstellten Tabelle kannst Du dann in Excel via Daten, Daten abrufen, aus Datenbank, aus Microsoft Access Datenbank in ein leeres Tabellenblatt einer Arbeitsmappe laden. Und dort dann damit veranstalten was Dir nützt.
Gruß
Jochen
AW: vorsortierte Daten aus Access per VBA importieren
05.12.2019 06:14:53
Klaus
Hallo Jochen,
warum ich die Abfrage nicht in Access erstelle? Weil ich von Access so viel verstehe wie ein Fisch vom Fahrrad fahren ;-)
Ich habe mir ganz Script-Kiddie like die VBA Makros zur Fernsteuerung von Access kopiert ohne sie zu verstehen und nutze sie jetzt. Mir wäre es lieber das ganze in Excel zu lösen, wo ich die Kontrolle drüber habe und auch begreife was passiert.
LG,
Klaus M.
Anzeige
AW: vorsortierte Daten aus Access per VBA importieren
04.12.2019 15:36:54
volti
Hallo Klaus,
falls Du Dich mit PowerQuery oder der Erstellung von Abfragen in Access selbst nicht anfreunden kannst und lieber im Bereich Excel bleiben möchtest, ändere doch einfach Deine Recordseterstellung ein wenig ab.
Hier ein ungeprüftes Anschauungsbeispiel:
'rs.Open SQL, ConnectionString, adOpenKeyset=1, adLockReadOnly=1, adCmdText=1 ',adOpenForwardOnl
Sql = "SELECT * FROM [MeineTabelle] AS myTab WHERE myTab.Material like 'Holz'"
rs.Open Sql, ConnectionString, 1, 1, 1

Die (optionalen Parameter 1,1,1) müsstest Du ggf. googlen. Ich habe kein Access mehr.
SQL ist quasi eine eigene mächtige Sprache, da kann man sich auch lange mit beschäftigen :-)
CopyFromRecordSet kopiert sehr schnell einen kompletten Block ins Excel.
Zur Ergänzung, man kann aber auch für z.B. spezielle Fälle die Daten im Einzelschritt aus dem Recordset holen. Ist halt deutlich langsamer...
Daten per Schleife aus Recordset holen (Beispiel)
Zeile = 2: Spalte = 1
 With rs
  rsSpaltenAnzahl = .Fields.Count                                       'Spaltenanzahl ermitteln
  .MoveLast: rsZeilenAnzahl = .RecordCount: .MoveFirst                  'Zeilenanzahl ermitteln
   Do While Not rs.EOF                                                  'Daten in einer Schleife abarbeiten
     If rs.Fields(0).Value Like "Will ich" Then
      For i = 0 To rsSpaltenAnzahl - 1
        ActiveSheet.Cells(Zeile, i + Spalte).Value = rs.Fields(i).Value  'Daten in die Spalten schreiben
      Next i
      Zeile = Zeile + 1
     End If
   Loop
 End With

Vielleicht hilft es Dir ja bei Deiner Entscheidungsfindung weiter.
viele Grüße
Karl-Heinz
Anzeige
AW: vorsortierte Daten aus Access per VBA importieren
05.12.2019 06:15:52
Klaus
Hallo Karl-Heinz,
vielen Dank - das probiere ich heute im Laufe des Tages aus, auf genau so eine Antwort habe ich gehofft. Melde mich dann nochmal!
LG,
Klaus M.
AW: vorsortierte Daten aus Access per VBA importieren
05.12.2019 12:56:00
Klaus
Sehr geil - es funktioniert, und das blitzschnell!
Hier mein Code:
Option Explicit
Const pfad As String = "C:\herber\Access\Datenbanken"   'Access DB PFad
Const myAccessDB As String = "Datenbank.accdb"          'Access DB Dateiname
Const myDB As String = "Artikel"                        'DB-Tabellenname im Access
Const myTable As String = "Tabelle2"                    'in diese Tabelle
Sub Test()
Call Gefiltert_laden_SQL("Material", "Holz")
End Sub
Sub Gefiltert_laden_SQL(SearchIn As String, SearchFor As String)
On Error GoTo hell
Const APPNAME = "mod_Access / Gefilter_laden_SQL"
'ACCESS Tabelle per SQL Kommando filtern und gefilterte laden
Dim cmd As ADODB.Command
Dim con As ADODB.Connection
Dim datei As String
Dim fld As ADODB.Field
Dim rs As ADODB.Recordset
Dim sql As String
Dim ws As Worksheet
Dim spalte As Long
datei = pfad & "\" & myAccessDB
Set con = New ADODB.Connection
con.Open ConnectionString:= _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & datei & ";" & _
"Mode=Share Exclusive"
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
'rs.Index = "Primarykey"
sql = "SELECT * FROM " & myDB & " AS myTab WHERE myTab." & SearchIn & " like '" & SearchFor &  _
rs.Open sql, _
ActiveConnection:=con, _
CursorType:=adOpenStatic, _
LockType:=adLockPessimistic, _
Options:=adCmdTableDirect
Set ws = Sheets(myTable)
With ws
.UsedRange.ClearContents
.Range("A2").CopyFromRecordset _
Data:=rs, _
MaxRows:=ws.Rows.Count - 1, _
MaxColumns:=ws.Columns.Count
End With
'*** Fehlerbehandlung
Err.Clear
hell:
If Err.Number = -2147467259 Then Resume     'Datenbank wird bereits verwendet
If Err.Number  0 Then MsgBox "Fehler in Sub """ & APPNAME & """" & vbCrLf _
& "Fehlernummer: " & Err.Number & vbLf & Err.Description
Err.Clear
Set cmd = Nothing
con.Close
Set con = Nothing
End Sub
Alle Fragen wie "filtere Material = Holz UND Preis Eine Kleinigkeit: Die Überschriften habe ich mir bisher so geholt:

Dim fld As ADODB.Field
Dim rs As ADODB.Recordset
spalte = 1
For Each fld In rs.Fields
ws.Cells(1, spalte) = fld.Name
spalte = spalte + 1
Next fld

Das funktioniert in der SQL Version nicht mehr. Ist aber nicht schlimm die Überschriften bekomme ich notfalls auch woanders her.
'rs.Index = "Primarykey"
Dies musste ich ausklammern, da es eine Fehlermeldung gemacht hat. Ich weiß nicht was der Befehlt macht, ich weiß nur er ist wichtig :-) Über "Primärschlüssel in Datenbanken" habe ich mal in der Schule was gelernt, das ist aber schon über 20 Jahre her ... der ist immer eindeutig und beschreibt damit den Datensatz, richtig? Dann vermute ich, der ist eher beim BE-schreiben der Datenbank notwendig als beim auslesen und lasse die Zeile mit gutem Gewissen weg.
Vielen Dank, Karl-Heinz, du hast mir massiv geholfen!
LG,
Klaus M.
Anzeige
hihi ....
05.12.2019 13:33:37
Klaus
... ich hab grad damit meinen Chef beeindruckt und darf mir jetzt zur Belohnung einen "SQL Einsteiger" Kurs aussuchen :-) Sehr schön.
AW: hihi ....
05.12.2019 14:42:11
volti
Glückwunsch Klaus zu Deinem Erfolg,
wie gesagt ist meine Access-Zeit schon etwas her, aber ich hatte zehn Jahre lang fast alles via VBA und von Excel aus gemacht. Ich meine sogar, dass meine Kunden gar kein Access haben mussten, um Daten via SQL und VBA aus Access-Datenbanken zu extrahieren.....
Du kannst hier mit * alle Spalten oder kommagetrennt die gewünschten Spalten rausziehen, Deine Daten gruppieren, sortieren und sonst noch was machen.
In der Where-Klausel geht es logisch zu, wie Du schon vermutet hattest
"WHERE (myTab.Material = 'Holz' or myTab.Material like 'Glas%') and myTab.Jahr > '2018'" usw.
PS: Die Kopfzeilen holst Du, wie vorher auch mit der Schleife, die sind nicht im CopyFromRecordSet mit drin.
Übrigens, myTab spricht eine Tabelle an. Da es hier nur eine gibt, könnte es auch weggelassen werden. Aber Du siehst, da kann man 'ne Menge machen.
Viel Erfolg dabei
VG KH
Anzeige
einen Hab ich noch!
07.12.2019 21:45:56
Klaus
Hallo Karl-Heinz,
noch hatte ich die Fortbildung leider nicht :-)
eine SQL Frage sei mir daher noch gestattet. Gegeben sei folgender Datensatz:
ID- Auft. - St- Datum
1 - 1111- A- Oktober
2 - 2222- A- Oktober
3 - 1111- B- November
4 - 1111- C- November
5 - 3333- A- November
Ich möchte nun alle Aufträge sehen, die im November liefen - aber komplett. Das Problem: Es müssen nicht alle Stationen im November erfolgt sein, ich möchte aber von allen Aufträgen im November jede Station sehen.
Aus obriger Liste müssten also die ID 1/3/4/5 heraus kommen. 3/4/5 weil sie im November liegen, und 1 weil der Auftrag 1111 im November an mindestens einer Station bearbeitet wurde. 2 wäre ausgeschlossen, da Auftrag 2222 im November nicht bearbeitet wurde. ID ist PrimaryKey.
Unter Excel kann ich das, mit einer Zählenwenns-Hilfsspalte und dem Autofilter. Aber wie kann ich per SQL diese Kombination direkt aus der Datenbank abfragen?
LG,
Klaus M.
Anzeige

326 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige