Update der SQL-Datenbank via Access

[Rainer_Zufall]

Hallo,

ich habe auf einem lokalen Server eine MySQL Datenbank angelegt, auf die ich mit einer Access-Datei zugreife.

In der Access-Datei habe ich unter anderem ein Listenfeld, in der die Namen bzw. Kürzel verschiedener Rechtsanwälte erscheinen.

Die Namen bzw. Kürzel der Rechtsanwälte sind in einer Tabelle "rae" angelegt. Eine weitere Tabelle "auftragsdaten" enthält diverse Auftragsdaten. Zu jedem Auftrag wird vom Nutzer ein Rechtsanwalt aus der Tabelle "rae" ausgewählt. Ich möchte nun, dass die entsprechende ID des Rechtsanwalts in der Tabelle auftragsdaten zu dem entsprechenden Auftrag hinterlegt wird.

Mein bisheriger Ansatz:

Code [Auswählen] Erweitern

Private Sub Rechtsanwalt_1_AfterUpdate()
Dim strSQL As String
Dim RA_ID As Integer
Dim ganummer_lokal As Integer
Dim RA As String


ganummer_lokal = Me!ganummer.Value
RA = Me!Rechtsanwalt_1.Text

strSQL = "UPDATE auftragsdaten SET RA_ID1 = (SELECT rae.RA_ID FROM rae WHERE rae.Kürzel_RA = '" & RA & "') WHERE GANUMMER =" & ganummer_lokal

DoCmd.SetWarnings False
DoCmd.RunSQL str1SQL
DoCmd.SetWarnings True

End Sub
Wenn ich das nun laufen lasse, verlangt Access von mir eine Eingabe:

"Parameterwert eingeben
RA_ID1 ___________"

Dabei habe ich den Wert RA_ID1 doch in der o.g. Abfrage definiert..
Selbst wenn ich nun die ID des Rechtsanwalts händisch eingebe, bekomme ich die Fehlermeldung:

"Laufzeitfehler '3073':
Operation muss eine aktualisierbare Abfrage verwenden."

Zu Testzwecken habe ich mir den fertigen SQL-String mal in einer Messagebox ausgeben lassen. Für einen Beispiel-Auftrag sieht der SQL-String dann so aus:

Code [Auswählen] Erweitern

UPDATE auftragsdaten SET RA_ID1 = (SELECT rae.RA_ID FROM rae WHERE rae.Kürzel_RA = 'RA Müller') WHERE GANUMMER =5703
Wenn ich diesen Befehl direkt auf dem SQL-Server eingebe, macht er problemlos was ich möchte. Die ID des Rechtsanwalts Müller wird also in der Tabelle "auftragsdaten" zu dem Auftrag 5703 in der Spalte RA_ID1 hinterlegt. Warum funktioniert der Befehl nicht, wenn ich ihn via Access ausführe?


Ich habe es anschließend noch mit einem Inner Join versucht.

Code [Auswählen] Erweitern

strSQL = "UPDATE auftragsdaten SET INNER JOIN rae ON rae.Kürzel_RA = '" & RA & "' Set auftragsdaten.RA_ID1 = rae.RA_ID WHERE auftragsdaten.GANUMMER =" & ganummer_lokal
Wenn ich das ausführe, bekomme ich einen "Syntaxfehler in UPDATE-Anweisung."

Hat jemand eine Idee, wie ich den Fehler behoben bekomme? Ich bin noch ziemlicher SQL-Neuling. Vielen Dank für jede Hilfe!

MfG

[PhilS]

Wenn ich diesen Befehl direkt auf dem SQL-Server eingebe, macht er problemlos was ich möchte. Die ID des Rechtsanwalts Müller wird also in der Tabelle "auftragsdaten" zu dem Auftrag 5703 in der Spalte RA_ID1 hinterlegt. Warum funktioniert der Befehl nicht, wenn ich ihn via Access ausführe?

Weil Access da leider dämlich ist und bei einer Unterabfrage immer davon ausgeht, dass die Abfrage nicht aktualisierbar ist.
In diesem Fall ist das falsch; dein SQL ist korrekt. - Hilft leider nicht weiter.

Ich habe es anschließend noch mit einem Inner Join versucht.
[...]
Wenn ich das ausführe, bekomme ich einen "Syntaxfehler in UPDATE-Anweisung."

Dieser Ansatz sollte generell funktionieren. Du hast tatsächlich einen Syntaxfehler in deinem SQL. Das SET-Schlüsselwort ist doppelt und vor dem INNER JOIN falsch.

[Rainer_Zufall]

Hallo,

vielen Dank für die Schnelle Antwort.


Meinst du so?

Code [Auswählen] Erweitern

str1SQL = "UPDATE auftragsdaten INNER JOIN rae ON rae.Kürzel_RA = '" & RA & "' SET auftragsdaten.RA_ID1 = rae.RA_ID WHERE auftragsdaten.GANUMMER =" & ganummer_lokal

Leider bekomme ich nun den Fehler:
"JOIN-Ausdruck nicht unterstützt."

Mit einem Beispiel-String hat es so funktioniert, wenn ich es direkt in SQL eingegeben habe. Bei Access wie gesagt der o.g. Fehler. Woran könnte das liegen?

Vielen Dank!
MfG

[markusxy]

Woran könnte das liegen?

Du solltest das Ergebnis ausgeben.
debug.print str1SQL
Ohne den endgültigen String zu sehen kann man dazu nichts sagen, da nicht zu sehen ist, ob du in der Variable Tabellen und Feldnamen korrekt übergibst.
Außerdem warum verwendest du ein Hochkomma? Das ist ja kein Parameter.

Abgesehen davon ist es sehr sonderbar, wenn ein Feldname als Variable übergeben werden muss.

[markusxy]

Übrigens persönlich schreibe ich bei Nutzung eines SQL Servers praktisch keine Aktions-Abfragen in Access.
Da verwende ich in der Regel eine Prozedur am Server oder stelle mitunter einen SQL-Text in VBA zusammen,
aber eben immer direkt im SQL Dialekt des Servers. Vor allem deshalb weil Access viele Dinge einfach nicht kann.
Die Abfragen kann man dann per DAO Passthrough oder per ADODB.Connection oder ADODB.Command ausführen.

[Rainer_Zufall]

Du solltest das Ergebnis ausgeben.
debug.print str1SQL

Code [Auswählen] Erweitern

UPDATE auftragsdaten INNER JOIN rae ON rae.Kürzel_RA = 'RA Müller' SET auftragsdaten.RA_ID1 = rae.RA_ID WHERE auftragsdaten.GANUMMER =5703

Außerdem warum verwendest du ein Hochkomma? Das ist ja kein Parameter.

Meinst du an der Stelle: ON rae.Kürzel_RA = 'RA Müller'
Müssen da keine Hochkommata hin? Ich dachte, dass SQL die braucht. Jedenfalls funktioniert die Abfrage so, wenn ich sie direkt auf dem SQL Server ausführe. Aber in Access wie gesagt der o.g. Fehler.

Übrigens persönlich schreibe ich bei Nutzung eines SQL Servers praktisch keine Aktions-Abfragen in Access.
Da verwende ich in der Regel eine Prozedur am Server oder stelle mitunter einen SQL-Text in VBA zusammen,
aber eben immer direkt im SQL Dialekt des Servers. Vor allem deshalb weil Access viele Dinge einfach nicht kann.
Die Abfragen kann man dann per DAO Passthrough oder per ADODB.Connection oder ADODB.Command ausführen.

Danke für den Hinweis! Mir war ehrlich gesagt nicht klar, dass die Abfragen bei meinem Vorgehen gar nicht von SQL sondern von Access ausgeführt werden. Bisher lag die komplette Datenbank einfach in einer Access-Datei im Firmen-Netzwerk. Die Mitarbeiter aus dem Home Office haben darauf dann per VPN zugegriffen. Allerdings führte das bei Abfragen zu extrem langen Ladezeiten von teilweise >1 min. Deshalb soll ich das ganze jetzt auf SQL migrieren, allerdings soll sich an der Access Oberfläche nichts großartig ändern. Mehr Vorgaben habe ich nicht. In der Firma gibt es leider auch keinen IT-Experten. Ich habe die Tabellen auf einem PHPmyAdmin Server hochgeladen und sie als linked Table per ODBC in die Access-Datei importiert.
Die Ladezeiten wenn man per VPN zugreift sind damit schon deutlich reduziert, aber immer noch nicht ideal. Würde das nochmal performanter werden, wenn man beispielsweise die Abfragen per DAO Passthrough ausführt? Die Datenbank enthält 8 Tabellen mit insgesamt ~40.000 Datensätzen. Wie sieht es mit der Sicherheit aus? Datenschutz ist auch ein großes Anliegen. Macht das einen Unterschied, ob die Abfragen in Access oder direkt auf dem SQL-Server ausgeführt werden?

Sorry für die vielen Fragen und Danke für die Hilfsbereitschaft

[markusxy]

Code [Auswählen] Erweitern

auftragsdaten INNER JOIN rae ON rae.Kürzel_RA = 'RA Müller'

Wie soll das funktionieren?
Ein Join soll doch zwei Objekte verbinden. Im On Statement ist aber nur das Objekt rae enthalten und auftragsdaten fehlt 

[markusxy]

Mir war ehrlich gesagt nicht klar, dass die Abfragen bei meinem Vorgehen gar nicht von SQL sondern von Access ausgeführt werden.

Bei Nutzung verknüpfter Tabellen wird die Anweisung von der Jet abgearbeitet und eine neue von der Jet generiert Abfrage wird an den SQL Server geschickt. Der Code muss also dem jet SQL Standard entsprechen. Der Vorteil: es ist egal, was für eine Datenbank verwendet wird - der SQL String muss nicht angepasst werden. Dafür können komplexe Abfragen auch schnell mal um den Faktor 100 mehr an Zeit benötigten.

Passthrough macht der DB-Server direkt, ohne das die Jet dazwischen funkt. Man hat also die volle Power des Servers zur Verfügung.
Natürlich kann man auch Abfragen am Server erstellen und diese mit Access verknüpfen. Dann entsteht eine ähnliche Leistung.

Würde das nochmal performanter werden, wenn man beispielsweise die Abfragen per DAO Passthrough ausführt?

Das kommt auf die Abfrage an.
Genau das sind die Dinge, die man eigentlich verstehen, bzw. sich erarbeiten sollte, wenn man Access "Anwendungen" erstellt.

Wie sieht es mit der Sicherheit aus?

Welche Sicherheit meinst du?

[Rainer_Zufall]

Im On Statement ist aber nur das Objekt rae enthalten und auftragsdaten fehlt 

Ok, das muss ich mir wohl nochmal anschauen.

Bei Nutzung verknüpfter Tabellen wird die Anweisung von der Jet abgearbeitet und eine neue von der Jet generiert Abfrage wird an den SQL Server geschickt. Der Code muss also dem jet SQL Standard entsprechen. Der Vorteil: es ist egal, was für eine Datenbank verwendet wird - der SQL String muss nicht angepasst werden. Dafür können komplexe Abfragen auch schnell mal um den Faktor 100 mehr an Zeit benötigten.

Passthrough macht der DB-Server direkt, ohne das die Jet dazwischen funkt. Man hat also die volle Power des Servers zur Verfügung.
Natürlich kann man auch Abfragen am Server erstellen und diese mit Access verknüpfen. Dann entsteht eine ähnliche Leistung.

Ok, danke! Gut zu wissen.

Das kommt auf die Abfrage an.
Genau das sind die Dinge, die man eigentlich verstehen, bzw. sich erarbeiten sollte, wenn man Access "Anwendungen" erstellt.

Also von dem was ich so bisher gelesen hab, hab ich mitgenommen, dass vor allem bei komplexeren Abfragen mit Inner Joins die Performance leidet. Ich würde es daher gerne mit dem Pass-Throughs oder mit Routinen/Prozeduren auf dem Server umsetzen.

Welche Sicherheit meinst du?

Bezüglich Datendiebstahl. Macht das einen Unterschied, ob ich Passthrough oder linked Tables benutze?



Ich habe das mit der Pass Through Abfrage mal probiert. Erstmal nur "SELECT * FROM auftragsdaten". Er zeigt mir dann die gesamte Tabelle an und ich konnte die Datensätze zunächst sehen.
Wenn ich aber auf "letzter Datensatz" klicke, bekomme ich eine Fehlermeldung. Und dann steht in jedem Feld plötzlich nur noch "#NAME". Er zeigt mir auch nicht an, wieviele Datensätze in der Tabelle enthalten sind.
Wenn ich die Tabelle schließe und neu öffne, stehen die Daten wieder alle da. Ich kann auch durch die Daten durchscrollen. Nach einer Weile Scrollen, spätestens aber wenn ich auf "letzter Datensatz" klicke, bekomme
ich die Meldung und die Daten sind wieder weg. Auch wenn ich in der Tabelle einen bestimmten Datensatz suche/filtere, bekomme ich die Fehlermeldung.

ODBC-Aufruf fehlgeschlagen.

[MySQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.4.27-MariaDB](#0)


Die Microsoft Hilfe zu der Meldung war leider wenig hilfreich und eine Google-Suche ergab leider keine Treffer. Fehlt mir irgendein Treiber?


Das mit der Prozedur direkt auf dem Server habe ich auch mal ausprobiert. Ich habe auf dem MySQL Server eine Prozedur erstellt, die mir die Auftragsdaten zu einem bestimmten Auftrag herausgibt. Wenn ich in Access nun einen Pass-Through Query mit dem Inhalt "CALL `auftragsdaten_proc`(5703)" erstelle, bekomme ich den Datensatz zur Auftragsnummer 5703. Ich habe aber noch nicht verstanden, wie ich das nun parametrisieren kann, sodass ich über eine Access Form einen beliebige Auftragsnummer aufrufen kann. Habt ihr da einen Tipp oder einen weiterführenden Link?
Vielen Dank!

[Rainer_Zufall]

Ich habe mal versucht die Prozedur auf dem Server über eine ADO Verbindung auszuführen.


Die SQL-Prozedur heißt auftragsdaten_proc und das steht drin:

Code [Auswählen] Erweitern

BEGIN
SELECT * FROM auftragsdaten WHERE GANUMMER = uAuftragsnummer;
END

Mein VBA-Code:

Code [Auswählen] Erweitern

Private Sub Test()

Dim conn As Object, cmd As Object, rst As Object
Const adCmdStoredProc = 4, adParamInput = 1, adVarInt = 3
Dim wunschnummer As Integer

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

' DSN-LESS CONNECTION

conn.Open "Driver={MySQL ODBC 8.0 ANSI Driver};host=localhost;database=gutachtendatenbank;" _
                & "UID=root;PWD="
       

    ' CONFIGURE ADO COMMAND
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = conn
        .CommandText = "auftragsdaten_proc"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 15
    End With
   
   
   
' APPEND NAMED PARAM
    cmd.Parameters.Append cmd.CreateParameter("uAuftragsnummer", adVarInt, _
                                              adParamInput, 6, wunschnummer)
    Set rst = cmd.Execute

    ' FREE RESOURCES
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set conn = Nothing


End Sub


Leider bekomme ich diese Fehlermeldung:

"Laufzeitfehler '-2147467259 (80004005)':

[MySQL][ODBC 8.0(a) Driver] Access denied for user 'root'@'localhost' (using password: NO)"


Dabei habe ich bisher kein Passwort definiert. Ich logge mich nur mit dem Usernamen "root" auf dem PHPmyAdmin Server ein. Das Passwort-Feld lasse ich leer. Ich habe alternativ probiert hinter "PWD" noch ein Leerzeichen oder zwei Hochkommata zu setzen. Hat leider auch nicht geklappt. Habe ich den String hinter "conn.Open" falsch definiert?

Wenn ich in Access im Abfrageentwurf unter Pass-Through die ODBC-Verbindung festlege und teste bekomme ich "Connection Successfull". (Habe danach aber immer noch das Problem aus meinem vorherigen Post)
Anschließend steht im Feld "ODBC-Verbindung" der String:
ODBC;DSN=unfall;SERVER=localhost;UID=root;DATABASE=gutachtendatenbank;PORT=3307;COLUMN_SIZE_S32=1;DFLT_BIGINT_BIND_STR=1


Muss der String für die ADO-Verbindung evtl. auf diese Art definiert werden?

[markusxy]

Macht das einen Unterschied, ob ich Passthrough oder linked Tables benutze?

Nein, nicht wenn es um die Frage geht, ob Netzwerk-Traffic abhört werden kann.
Das geht es um die generelle Netzwerksicherheit.

[markusxy]

Fehlt mir irgendein Treiber?

Die Frage ist wohl eher ob du den optimalen Treiber einsetzt.
Mysql ist nicht meine Liga, aber das Web weiß alles.

Grundsätzlich zu Passthrough:
Ich schrieb nicht umsonst - für Aktionsabfragen.
Select ist ein anderes Thema. Da solltest du Abfragen am Server speichern und mit dem Frontend verknüpfen.
Passthrough liefert keine bearbeitbaren Daten.

[markusxy]

Dabei habe ich bisher kein Passwort definiert.

Dann ändere das.
Oder meinst du jetzt ernsthaft, andere sollen sich mit so was Unsinnigem beschäftigen, nur weil du kein PW festlegst?
Ansonsten frage ich mich, warum du verschiedene ODBC Strings einsetzt - ist doch unlogisch.