Fehler im Code "Insert oder Update als Parameter-Abfrage"

[OnkelTom]

Hallo Foristen,

ich hoffe, das Forum wird noch besucht. Die meisten Einträge sind ja schon etwas älter.

Auf der Suche nach einer Problemlösung bin ich durch einen Hinweis auf die Code-schnipsel der Seite
http://wiki.access-codelib.net/Insert_oder_Update_als_Parameter-Abfrage
gestoßen.

Beim Versuch, den folgenden Code auszuführen

Code: Alles auswählen

Public Function ExecuteParamQdf(ByVal QueryDefName As String, _
                           ParamArray QueryParams() As Variant) As Long
 
   Dim qdf As DAO.QueryDef
 
   Set qdf = CurrentDb.QueryDefs(QueryDefName)
 
   Dim i As Long
   For i = 0 To UBound(QueryParams)-1 Step 2
      qdf.Parameters(QueryParams(i)) = QueryParams(i + 1)
   Next
   qdf.Execute dbFailOnError
   ExecuteParamQdf = qdf.RecordsAffected
   qdf.Close
 
End Function

erhalte ich einen Laufzeitfehler "3265". Getestet habe ich im Direktbereich meiner Access-Datenbank mit dem angegebenen Code:

Code: Alles auswählen

anzahlDS = ExecuteParamQdf("GespeicherteAbfrage" & _
                           "P1", "abc", _
                           "P2", 123, _
                           "P3", Now())

Der Name meiner Tabelle lautet "Tabelle", die Abfrage heißt wie im Beispiel "GespeicherteAbfrage".
Kann mir jemand sagen, wo der Fehler liegt? Ich habe ihn trotz intensiver Suche und Debugging nicht finden können.

mfG
Thomas

[Andreas Vogt]

Hallo,
dein Problem ist ein fehlendes Komma und überflüssiger Und-Operator im Prozeduraufruf nach dem QueryDefName:
anzahlDS = ExecuteParamQdf("GespeicherteAbfrage", _
"P1", "abc", _
"P2", 123, _
"P3", Now())

Bei Codeprobleme empfehle ich einen Haltepunkt zu setzen und mit F8 zeilenweise durch den fehlerhaften Code zu gehen, dann hättest du das sicherlich auch bemerkt dass der Ausdruck "P1" zum QueryDefName dazu addiert wurde üben den Und-Operator.

Gruß Andreas

Btw: da ist wohl ein Fehler im Beispiel in der Wiki?!

[OnkelTom]

Hallo Andreas,

danke für Deine schnelle Antwort. Es ist doch noch Leben auf dieser Seite
Was mich wundert, ist, dass die Code-Schnipsel nicht bereinigt sind.
Auch auf der Diskussions-Seite hat niemand eine Anmerkung dazu hinterlassen.

Den Tippfehler im Code - UND-Operator statt Komma - habe ich gestern abend auch noch entdeckt.
Der Laufzeitfehler verschwindet nach Korrektur dieses Fehlers allerdings leider nicht, sondern verlagert sich in die Anweisung innerhalb der For ... to Schleife:

Code: Alles auswählen

 qdf.Parameters(QueryParams(i)) = QueryParams(i + 1)

Bereits beim ersten Durchlauf, also mit i=0, steigt Access wieder mit der Fehlermeldung "Element in dieser Auflistung nicht gefunden" (Laufzeitfehler 3265) aus.
Im Debugger erhalte ich, wenn ich die Parameter QueryDefName und QueryParams() überwache, die korrekten Übergabewerte.

Ich verstehe die Fehlermeldung nicht. Beim ersten Durchlauf sollte doch folgende Zuweisung erfolgen:

Code: Alles auswählen

qdf.Parameters(P1) = "abc"

("abc" als Beispiel, wie im Aufruf dargestellt)
Was stimmt denn da nicht?

mfG
Thomas

[Andreas Vogt]

Hallo,
Wie lauten deine Parameter in der Abfrage genau?
Ist es eine Auswahlabfrage (Select...) oder eine Aktualisierungsabfrage etc?
Prüfe mal im Direktfenster wie die QueryParams(i) im Code lauten?
Habe es bei mir getestet und hat problemlos funktioniert.
Gruß Andreas

[Andreas Vogt]

Fehler in Wiki behoben.
Gruß Andreas

[OnkelTom]

Hallo Andreas,

ich glaube, in meiner Abfrage fehlt etwas.
Ich lade einfach mal die Abfrage

Ich habe einen Screenshot und meine beiden Beispieldateien, die Tabelle und die Abfrage, einmal angehängt.

Kann es sein, dass die Passage

Parameters P1 Text(255), P2 int, P3 date;
INSERT INTO Tabelle (T, Z, D) VALUES ([P1], [P2], [P3])

in meiner Datei "GespeicherteAbfrage" fehlt?

[OnkelTom]

Hallo Andreas,

alles klar, ich habe den Fehler gefunden
Wer lesen kann, ist klar im Vorteil

Ich habe jetzt eine neue Abfrage auf der Basis der SQL aus dem Beispiel erstellt, siehe Screenshot.
Und jetzt funktioniert die Sache.

In meiner ursprünglichen Abfrage haben diese Elemente gefehlt, deswegen konnte

Code: Alles auswählen

qdf.Parameters(QueryParams(i))

wohl auch nichts zuweisen.
Das zeigt einmal mehr: man lernt am besten aus seinen Fehlern!

Danke für Dein Nachhaken.

[OnkelTom]

Hallo Andreas,

ich habe da noch eine Nachfrage, bei der Du mir vielleicht helfen kannst.
Die Beispiele im Wiki stellen ja nur Anfügeabfragen dar. Das gleiche Prinzip kann ich ja auf Aktualisierungsabfragen anwenden.

Dazu habe ich eine Tabelle tabTest und eine Aktualisierungsabfrage abfParaTestUpdate angelegt.

Über ein Formular will ich Datensätze in der Tabelle hinzufügen oder eben auch ändern. Hier die Eingabemaske:

Eingabemaske Testdatei Datum (2020-01-09).png (43.7 KiB) 80847-mal betrachtet

Wenn ich die Abfrage ausführe, werden natürlich alle Datensätze mit den angegebenen Werten P1 bis P8 geändert.

Hier der - vorläufige - SQL-Code der Aktualisierungsabfrage :

Code: Alles auswählen

PARAMETERS P1 Long, P2 Text ( 255 ), P3 Text ( 255 ), P4 DateTime, P5 DateTime, P6 DateTime, P7 DateTime, P8 DateTime;
UPDATE tabTest SET tabTest.D_InvNr = P1, tabTest.D_INTERPRET = P2, tabTest.D_TITEL_LP_CD = P3, tabTest.D_KAUFDATUM = P4, tabTest.D_LP_Reinigung = P5, tabTest.D_LP_DIGI = P6, tabTest.D_DIGI_FLAC_WAV = P7, tabTest.D_DIGI_MP3 = P8;
 

Ich möchte aber nur einen ausgewählten Datensatz ändern, z.B. den Datensatz mit der ID 236:

Datensatz zur Änderung.png (33.47 KiB) 80847-mal betrachtet

Dazu benötige ich ja noch einen Filter in Form einer WHERE ... Klausel. Und darauf zielt jetzt meine Frage ab.
Im VBA-Code arbeite ich ja mit Methoden, um die Werte der einzelnen Textfelder txtInvNr, txtINTERPRET usw. dem Datensatz zuzuweisen:

Code: Alles auswählen

  ...
 'Fall 2 "Ändern":
ElseIf Me.cmdÄndern.Enabled = True Then
                strSQL = ExecuteParamQdf("abfParaTestUpdate", _
                        "P1", Me.txtInvNr, "P2", Me.txtINTERPRET, "P3", Me.txtTITEL_LP_CD, "P4", Me.txtKAUFDATUM, "P5", Me.txtLP_Reinigung, _
                        "P6", Me.txtLP_DIGI, "P7", Me.txtDIGI_FLAC_WAV, "P8", Me.txtDIGI_MP3)
  ...

Um nur den gewünschten Datensatz zu ändern, habe ich mir überlegt, den Index D_ID aus meiner Tabelle dafür zu verwenden.
Der Index steht ja im Formular an folgender Stelle:

Code: Alles auswählen

Me.lstDokumente.Column(0)

Allerdings erhalte ich eine Fehlermeldung, wenn ich folgenden Code programmiere:

Code: Alles auswählen

WHERE D_ID = Me.lstDokumente.Column(0)

Damit kann Access nichts anfangen.
Kannst Du mir da prinzipiell weiterhelfen? Ich habe bei der Suche im Netz nichts Passendes gefunden.

Danke im Voraus.

Kleine Ergänzung: Es wäre für Leser des Wiki sicherlich hilfreich, hier auch ein Beispiel für den korrekten Aufbau einer Änderungsabfrage mit dem UPDATE-Befehl zu finden.

[Josef Pötzl]

Hallo!

Du könntest für den where-Ausdruck einen Parameter verwenden.

Code: Alles auswählen

PARAMETERS P1 Long, P2 Text ( 255 ), P3 Text ( 255 ), P4 DateTime, P5 DateTime, P6 DateTime, P7 DateTime, P8 DateTime, FilterID long;
UPDATE tabTest SET tabTest.D_InvNr = P1, tabTest.D_INTERPRET = P2, tabTest.D_TITEL_LP_CD = P3, tabTest.D_KAUFDATUM = P4, tabTest.D_LP_Reinigung = P5, tabTest.D_LP_DIGI = P6, tabTest.D_DIGI_FLAC_WAV = P7, tabTest.D_DIGI_MP3 = P8
WHERE tabTest.D_ID = [FilterID];

BTW:

Code: Alles auswählen

strSQL = ExecuteParamQdf(...)

"strSQL" als Variablenname finde ich nicht sehr aussagekräftig für die Anzahl der Datensätze.


Grüße
Josef

[OnkelTom]

Hallo Josef,

danke für die schnelle Antwort.

BTW:
CODE: ALLES AUSWÄHLEN
strSQL = ExecuteParamQdf(...)

"strSQL" als Variablenname finde ich nicht sehr aussagekräftig für die Anzahl der Datensätze.

Ja, Josef, das ist ein Überbleibsel meines ursprünglichen VBA-Programms.
Vielleicht in diesem Zusammenhang noch eine kurze Hintergrundinfo zum Arbeitsablauf:
Das Anfügen oder Ändern von Datensätzen an die bzw. in der Tabelle tabtest soll ausschließlich mit Hilfe eines Formulars erfolgen.
Die Formularmaske, welche ich für ein anderes Projekt im Rahmen eines Workshops vor einigen Jahren gebaut und jetzt für mein aktuelles Projekt angepasst habe, enthält dazu ein Listenfeld, welches die Datensätze der Tabelle tabtest auflistet. Damals wurde im VBA-Programm ein zusammengesetzter SQL-Code "gestrickt" . Das war nicht besonders professionell, sondern diente Übungszwecken. Und daher rührt auch der Variablenname, den ich aus Bequemlichkeit weiterverwendet habe.
Im Formular konnte ich einen Datensatz auswählen, den ich dann mittels "Ändern"-Button bearbeiten und abspeichern konnte.
Jetzt will ich aber das Formular und das VBA-Programm mit Eurem "Code-Schnipsel" professionalisieren.

Den Wert für FilterID müsste ich ja von Hand eingeben. Und in meinem Formular wird - bewußt - das Schlüsselfeld D_ID nicht angezeigt. Somit weiß ich nicht, welchen Wert ich für FilterID eingeben muss.
Ich könnte natürlich neben dem von Access generierten Schlüsselfeld D_ID auch das Feld D_InvNr verwenden, welches ebenfalls eindeutig ist. Hier müßte ich allerdings noch Vorkehrungen treffen, um Doppelvergaben u.ä. Fehler abzufangen.

Meine Idee für die neue Lösung mit der Funktion ExecuteParamSQL() bestand darin, die ID des ausgewählten Datensatzes automatisch in die WHERE-Klausel zu übergeben.

Ergänzung 9.1.2020, 11.35 Uhr:
Ich habe Deinen Lösungsansatz in mehreren Schritten umgesetzt.

Schritt 1:
Wenn ich nur die Änderungsabfrage abfParaTestUpdate ausführe und alle Parameter P1 bis FilterID von Hand eingebe, funktioniert die Sache.
Hier die Syntax der Abfrage:

Code: Alles auswählen

PARAMETERS P1 Long, P2 Text ( 255 ), P3 Text ( 255 ), P4 DateTime, P5 DateTime, P6 DateTime, P7 DateTime, P8 DateTime, FilterID Long;
UPDATE tabTest SET tabTest.D_InvNr = P1, tabTest.D_INTERPRET = P2, tabTest.D_TITEL_LP_CD = P3, tabTest.D_KAUFDATUM = P4, tabTest.D_LP_Reinigung = P5, tabTest.D_LP_DIGI = P6, tabTest.D_DIGI_FLAC_WAV = P7, tabTest.D_DIGI_MP3 = P8
WHERE tabTest!D_ID = [FilterID];
 

Schritt 2:
Gehe ich über das Formular, bringt Access mir zunächst einen Fehler, weil ich zuwenig Parameter an die Funktion ExecuteParamQdf übergebe.

Fehlermeldung 3061.png (2.57 KiB) 80842-mal betrachtet

Schritt 3:
Mit der Erweiterung

Code: Alles auswählen

"FilterID", Me.lstDokumente.Column(0))

im Aufruf der Funktion ExecuteParamQdf (den Namen der aufrufenden Variable habe ich in anzahlDS geändert ) funktioniert es dann:

Code: Alles auswählen

        anzahlDS = ExecuteParamQdf("abfParaTestUpdate", _
                        "P1", Me.txtInvNr, "P2", Me.txtINTERPRET, "P3", Me.txtTITEL_LP_CD, "P4", Me.txtKAUFDATUM, "P5", Me.txtLP_Reinigung, _
                        "P6", Me.txtLP_DIGI, "P7", Me.txtDIGI_FLAC_WAV, "P8", Me.txtDIGI_MP3, "FilterID", Me.lstDokumente.Column(0))

Damit wird automatisch die Indexnummer des einzigen, zu ändernden Datensatzes in die Funktion übernommen und nur dieser Datensatz wird auch aktualisiert.

Danke für Eure Unterstützung!