Fehler bei Update über VBA "Operation muss eine ausführbare Abfrage verwenden"

[brueninghoff]

Hallo liebe Schwarmintelligenz!

Ich will das Schnittgewicht in meiner Tabelle dbo_Einstallung_ES01_TAB über ein Update-Statement aus den Daten der verknüpften Tabelle dbo_Einstallung_ES02_TAB neu berechnen. Dazu verwende ich folgenden Code, welcher auch auf dem SQL Server so prima funktioniert:

Code [Auswählen] Erweitern

update dbo_Einstallung_ES01_TAB
set ES01_SCHNITTGEWICHT=(select AVG(ES02_GEWICHT) from dbo_Einstallung_ES02_TAB where ES02_ES01_GEN=@ES01_GEN)
where ES01_GEN=@ES01_GEN

Wenn ich diesen Code aber über Access ausführe erhalte ich immer diese Fehlermeldung:

Operation muss eine ausführbare Abfrage verwenden

Ich baue den Select in VBA zur Laufzeit zusammen, damit ich die variable GEN verwenden kann:

Code [Auswählen] Erweitern

Private Sub Schnittgewichtberechnen(ESnr As String)
   ' SQL-Abfrage erstellen
    Dim strSQL As String
    strSQL = "UPDATE dbo_Einstallung_ES01_TAB " & _
         "SET ES01_SCHNITTGEWICHT = (SELECT AVG(ES02_GEWICHT) FROM dbo_Einstallung_ES02_TAB WHERE ES02_ES01_GEN = " & ESnr & ") " & _
         "WHERE ES01_GEN = " & ESnr
   
    ' Datenbank öffnen
    Dim db As DAO.Database
    Set db = CurrentDb
   
    ' Abfrage ausführen
    On Error GoTo ErrorHandler
    db.Execute strSQL, dbFailOnError
       
    Exit Sub
   
ErrorHandler:
    ' Fehlerbehandlung: Wenn ein Fehler auftritt, wird eine Fehlermeldung angezeigt
    MsgBox "Fehler beim Ausführen des SQL-Statements: " & Err.Description, vbExclamation, "Fehler"
    ' Datenbank schließen
    db.Close
End Sub
Was muss ich ändern, damit die Operation auch in Access funktioniert?

Vielen Dank schonmal im Voraus!

[markusxy]

Dazu verwende ich folgenden Code, welcher auch auf dem SQL Server so prima funktioniert

Bei Updates hat Access leider einige Unzulänglichkeiten.
Verwendest du jetzt Access oder eine andere Datenbank als Backend?

[brueninghoff]

Wir verwenden Access als Frontend und die Datenbanken selbst laufen auf einem MSSQL-Server

[markusxy]

Datenbanken selbst laufen auf einem MSSQL-Server

Na ja, dann solltest du dir mal konzeptuelle Gedanken machen.
Wenn du Abfragen auf verknüpfte Tabellen aufsetzt, dann verschenkst du ja den Großteil des technischen und Performance-Vorteils des Servers.

Grundsätzlich sollte man versuchen statt Abfragen in Access zu schreiben, am Server Views zu erstellen.
Ausnahmen sind simple Abfragen ohne Aggregierung mit einer oder höchstens zwei beteiligter Tabellen.
Dynamisches SQL kann man auch in VBA schreiben und per DAO Pass-Through, oder per ADO an den Server übermitteln.
Und dann solltest du dich natürlich auch noch mit den Prozeduren und Funktionen am Server beschäftigen.

Wenn man so was mit einem Access Backend lösen will setzt man für die Ermittlung des Durchschnitts die Domänenfunktion DAVG ein, oder verzichtet ganz auf das Speichern, da man den Wert ja mit einer Abfrage jederzeit ermitteln kann. Auch hier ist es eine Frage des Arbeitskonzeptes - da man solche Werte in der Regel ja nicht speichert, da sie von anderen Tabellen abhängig sind.