Office: (Office 2003) Kriterium einer Access Abfrage in VBA ändern (s. Code)

MC_Chris · 27. Juli 2011

Hallo ans Forum,

ich lasse über meine Access-DB diverse Abfragen laufen und exportiere diese dann in eine einzige Excel Datei. Um das Kriterium (Schiffsname), das ich abfrage nicht für jede Abfrage ändern zu müssen, würde ich das gerne VBA-seitig lösen.

Ziel ist letztendlich alle Schiffnamen nacheinander über eine Excel-Liste einzulesen, das jeweilige Schiff in alle 8 Abfragen zu übernehmen, und das Ergebnis in einer Excel-Datei zu speichern.

Ich habe folgenden Ansatz gefunden:

Code:

Dim strSQL As String

strSQL = "INSERT INTO tbl (Feld1, Feld2) " & _
"SELCT tbl.Feld1, tblFeld2 " & _
"FROM tbl " & _
"WHERE ID = " & Me!ID
CurrentDb.Execute (strSQL), dbFailOnError
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

übertragen auf meine Abfrage habe ich versucht das so umzusetzen:

Code:

Dim SQL As String

strSQL = "SELECT Eschermann_max_Stichtag_Schiff.Schiffsname, tbl_Schiffsbetriebskosten.LetzteDockungJahr, tbl_Schiffsbetriebskosten.LetzteDockungKosten, tbl_Schiffsbetriebskosten.WährungLetzteDockung, tbl_Schiffsbetriebskosten.DockungskostenStatusArt_Bezugsjahr, tbl_Schiffsbetriebskosten.NächsteDockungJahr, tbl_Schiffsbetriebskosten.NächsteDockungKosten, tbl_Schiffsbetriebskosten.WährungNächsteDockung, tbl_Schiffsbetriebskosten.DockungskostenStatusArt_JahrPlus1, tbl_Liquiditätsentwicklung.Bezugsjahr " & _
"FROM Eschermann_max_Stichtag_Schiff INNER JOIN (tbl_Schiffsbetriebskosten INNER JOIN tbl_Liquiditätsentwicklung ON tbl_Schiffsbetriebskosten.ID_Liquiditätsentwicklung = tbl_Liquiditätsentwicklung.ID_Liquiditätsentwicklung) ON Eschermann_max_Stichtag_Schiff.ID_Struktur = tbl_Liquiditätsentwicklung.ID_Struktur " & _
"WHERE (((Eschermann_max_Stichtag_Schiff.Schiffsname)= '" & Me!Buxwind & "'") AND ((Eschermann_max_Stichtag_Schiff.MaxvonStichtag)=[tbl_Liquiditätsentwicklung].[Stichtag]))
ORDER BY Eschermann_max_Stichtag_Schiff.Schiffsname
CurrentDb.Execute strSQL, dbFailOnError
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Allerdings erhalte ich einen Kompilierungsfehler.

Nach der Abfrage würde ich gerne ein

Code:

DoCmd.TransferSpreadsheet acExport, 8, "Abfrage_Schiff", _
"S:\Analyseabteilung\Christian\IT\Schiffs-Tool\Master_Stapelverarbeitung.xls", True, "Dockung"
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

ausführen. Das funktioniert bereits - funktioniert das auch, wenn ich das Kriterium der Abfrage in VBA ändere? Wie kann ich die Abfrage dann ansteuern? Heißt die Abfrage dann quasi "strSQL?

Ideal wäre natürlich, wenn man das Kriterium wie bei einem Bericht ändern könnte - aber das geht glaube ich nicht, oder?

Code:

DoCmd.OpenReport "Abfrage_Schiff", acViewPreview, , _
"[Schiffname]='Buxwind'"
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Vielen Dank für eure Hilfe.

Grüße
Christian

:)

Thomas Möller · 29. Juli 2011

Hallo Christian,

übertragen auf meine Abfrage habe ich versucht das so umzusetzen:
...
Allerdings erhalte ich einen Kompilierungsfehler.
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

wenn Du den Code hierher kopiert hast, dann liegt es wohl am fehlenden Anführungszeichen am Ende des SQL-Strings.

strSQL = "SELECT Eschermann_max_Stichtag_Schiff.Schiffsname, tbl_Schiffsbetriebskosten.LetzteDockungJahr, tbl_Schiffsbetriebskosten.LetzteDockungKosten, tbl_Schiffsbetriebskosten.WährungLetzteDockung, tbl_Schiffsbetriebskosten.DockungskostenStatusArt_Bezugsjahr, tbl_Schiffsbetriebskosten.NächsteDockungJahr, tbl_Schiffsbetriebskosten.NächsteDockungKosten, tbl_Schiffsbetriebskosten.WährungNächsteDockung, tbl_Schiffsbetriebskosten.DockungskostenStatusArt_JahrPlus1, tbl_Liquiditätsentwicklung.Bezugsjahr " & _
"FROM Eschermann_max_Stichtag_Schiff INNER JOIN (tbl_Schiffsbetriebskosten INNER JOIN tbl_Liquiditätsentwicklung ON tbl_Schiffsbetriebskosten.ID_Liquiditätsentwicklung = tbl_Liquiditätsentwicklung.ID_Liquiditätsentwicklung) ON Eschermann_max_Stichtag_Schiff.ID_Struktur = tbl_Liquiditätsentwicklung.ID_Struktur " & _
"WHERE (((Eschermann_max_Stichtag_Schiff.Schiffsname)= '" & Me!Buxwind & "'") AND ((Eschermann_max_Stichtag_Schiff.MaxvonStichtag)=[tbl_Liquiditätsentwicklung].[Stichtag]))
ORDER BY Eschermann_max_Stichtag_Schiff.Schiffsname
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Du kannst übrigens den Code deutlich übersichtlicher gestalten, wenn Du für die Tabellen jeweils einen Buchstaben als Alias verwendest. Der SQL-String wird dadruch deutlich kürzer, übersichtlicher und damit leichter erständlich.

CU

ebs17 · 29. Juli 2011

-> Die Execute-Methode macht nur Sinn bei Aktionsabfragen, Du hast jedoch eine Auswahlabfrage.
-> TransferSpreadsheet benötigt eine gespeicherte Abfrage als Datenquelle, eine SQL-Anweisung reicht da nicht. Du könntest aber eine temporäre gespeicherte Abfrage aus Deiner SQL-Anweisung erzeugen, siehe CreateQueryDef (VBA-Hilfe).
-> Bei Deinem Vorhaben wäre auch die CopyFromRecordset-Methode sehr geeignet. Hier wird ein Recordset, das man auch aus einer SQL-Anweisung erzeugen kann, direkt nach Excel kopiert (wie der Name sagt). Dazu findest Du auch reichlich Beispiele.

Marsu65 · 29. Juli 2011

Ergänzend zu Eberhards 2. Vorschlag
kannst du eine Parameterabfrage erstellen,
per VBA die Parameter einstellen (FAQ 6.16: Parameterabfrage per VBA öffnen)
und dann wie beim 3. Vorschlag die CopyFromRecordset-Methode verwenden.

Ideal wäre natürlich, wenn man das Kriterium wie bei einem Bericht ändern könnte - aber das geht glaube ich nicht, oder?
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Du kannst auch auf deine Abfrage eine Abfrage erstellen.
Select * From Abfragename Where ...

MC_Chris · 1. August 2011

Hallo,

danke erstmal. Leider erhalte ich bei der Umsetzung die Fehlermeldung "Element in dieser Auflistung nicht enthalten".

Folgende "Verbessungen" haben leider auch nicht geholfen:
Code:

qdf.Parameters("Schiffname")= "Buxwind"
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Code:

Parameter1="Schiffsname" : qdf.Parameters!Parameter1 = "Buxwind"
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

In Summe habe ich versucht es so umzusetzen:

Code:

Option Compare Database

Private Sub Befehl0_Click()

Dim iCols As Integer
Dim rs As Recordset
Dim ws As Object
Dim wb As Object
Dim ExcelApp As Object
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("Eschermann_Charter_3")
qdf.Parameters!Schiffsname = "Buxwind"

'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Dim qdf As DAO.QueryDef

'Set db = CurrentDb
'Set qdf = db.QueryDefs("Meine_Parameter_Abfrage")
'qdf.Parameters!MeinParameter1 = "Wert_für_Parameter1_in_Anführungszeichen_falls_er_ein_Text_ist"
'qdf.Parameters!MeinParameter2 = Wert_für_Parameter2

'Set qdf = db.QueryDefs("Abfrage1")
' qdf.Parameters("Bitte id eingeben:") = 3

' Neue Excel Datei, Arbeitsmappe und Worksheet erstellen
Set ExcelApp = CreateObject("Excel.Application")
Set wb = ExcelApp.Workbooks.Add
Set ws = ExcelApp.Worksheets.Add
' Recordset des Unterformulars kopieren,
' hier muss der Name des Steuerelements, das das Unterformular enthält, angepasst werden
'---------------
Set rs = qdf.OpenRecordset(dbOpenDynaset)

'---------------
' Nur was machen, wenn Daten vorhanden sind
If Not rs.EOF Then
rs.MoveFirst
' Daten nach Excel kopieren
For iCols = 0 To rs.Fields.Count - 1
ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1), _
ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs
End If
' Excel sichtbar machen
ExcelApp.Visible = True

' Alles resetten
Set ws = Nothing
Set wb = Nothing
Set ExcelApp = Nothing

qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
Set db = Nothing

End sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Danke für eure Hilfe.
Christian

Thomas Möller · 1. August 2011

Hallo Christian,

Leider erhalte ich bei der Umsetzung die Fehlermeldung "Element in dieser Auflistung nicht enthalten".
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

die Fehlermeldung besagt, dass es keinen Parameter mit dem von Dir angegebenen Namen in der Abfrage gibt. Überprüf doch bitte noch einmal den Namen des Parameters.
Wenn Du so nicht zum Ziel kommst poste uns hier die WHERE-Klausel aus dem SQL-String Deiner Abfrage.

CU

Marsu65 · 1. August 2011

Hallo Christian,
hat die Abfrage denn Schiffsname als Parameter?
Hiermit ist nicht das Feld gemeint, sondern das Kriterium als Parameter!

Das heißt, du trägst
im Abfrageentwurf
in der Spalte Schiffsname z.B.
[aktSchiff] als Kriterium ein (mit den eckigen Klammern).
Dann mit rechter Maustaste auf den Abfrageentwurf (Tabellenbereich) und und im Kontextmenü Parameter... den Parameter einzutragen.

Im VBA-Code kannst du dann
qdf.Parameters("[aktSchiff]") = "DeinSchifname"
setzen.

Suche mal in der Accesshilfe nach dem Stichwort Parameterabfrage. Da sollte es reichlich Infos geben.

MC_Chris · 24. August 2011

Danke Marsu,

das war genau das Puzzle-Stück was mir fehlte. Der Code geht hier gerade richtig durch die Decke. Danke Dir.

Mein aktuelles -eher trivialerers Problem- findet ihr unter

http://www.ms-office-forum.net/forum...=1#post1398980

War im Urlaub als Vorbereitung auf die 100km Cyclassics. Daher erst jetzt meine Antwort. War n geiles Rennen. =)

LG
Christian

Marsu65 · 24. August 2011

Der Code geht hier gerade richtig durch die Decke.
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Dann wollen wir mal hoffen, das der Dachstuhl stabil genug ist *wink.gif*

Wenn dieses Problem zur Zufriedenheit gelöst ist, bitte das Thema als ERLEDIGT kennzeichnen.

Office: (Office 2003) Kriterium einer Access Abfrage in VBA ändern (s. Code)

Kriterium einer Access Abfrage in VBA ändern (s. Code)

Kriterium einer Access Abfrage in VBA ändern (s. Code)

Kriterium einer Access Abfrage in VBA ändern (s. Code)

Kriterium einer Access Abfrage in VBA ändern (s. Code)

Kriterium einer Access Abfrage in VBA ändern (s. Code) - Similar Threads - Kriterium Access Abfrage

Abfrage: Anzahl von Kriterium >0

Kombination von TEILERGEBNIS und SUMMEWENN mit ODER Kriterium kombinieren

zählen INTERVAL_COUNT: Intervalle (optional nach Kriterium) zählen

Datenexport wenn Kriterium Datum gleich, oder kleiner als erfüllt ist...

If-Then-Else...Verständnisfrage zu Kriterium String > Zahl

Minwenns - 2 Datum als Kriterium

Zählenwenns mit Datum als Kriterium