none
Acc2010 Datenänderung per VBA nicht möglich (Fehler 3146)

    Frage

  • Hallo,

    ich komme über das Access2010-Forum auf diese Seite: http://answers.microsoft.com/de-de/office/forum/office_2010-access/acc2010-datenänderung-per-vba-nicht-möglich-fehler/1c889dda-d898-4014-b20f-8da3d918d4da.

    Ich möchte über Access2010 Daten auf meiner MySQL-Datenbank ändern. Die Datenbank hat über 1.000.000 Datensätze und ich bin definitv der einzige, der aktuell auf die Datenbank zugreift und bekomme beim Versuch einen Datensatz über ein VBA-Script zu ändern immer die Fehlermeldung: "Laufzeitfehler 3197 - 'Das Microsoft Access-Datenbankmodul hat den Vorgang angehalten, da Sie und ein weiterer Benutzer gleichzeitig versuchen, dieselben Daten zu ändern'". Wenn ich Felder über die Access-Funktionen ändere klappt dies Problemlos.

    In der MySQL-Datenbank habe ich ein Index-Feld im Format Double - Nicht Null und Auto Increment angelegt.

    Das VBA-Script unter MS-Access2010 sieht folgendermaßen aus:

    Sub MeinScript()
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim txtDateiPfad As String
        Dim intEB As Integer
        Dim strAB As String
        Dim strAW As String
        Dim intI As Integer
        Dim intUmstellung As Integer
        
        Dim strSQL As String
        Dim Wahl As String
        Set db = CurrentDb()
        
        Set rs = db.OpenRecordset("t-dateiuebersicht", dbOpenDynaset)
        
       
        rs.MoveFirst
        
        Do
            If (Len(rs.Fields("Dateityp").Value)) < 1 Then
                intUmstellung = intUmstellung + 1
                Debug.Print intUmstellung & " - " & rs.Fields("MeinIndex").Value
                txtDateiPfad = rs.Fields("Dateipfad").Value
                intEB = Len(txtDateiPfad)
                
                For intI = 1 To 10
                    strAW = Right(txtDateiPfad, intI)
                    If intI = 10 Then strAW = ""
                    If Left(strAW, 1) = "." Then Exit For
                Next
                
                rs.Edit
                    rs.Fields("Dateityp").Value = LCase(strAW)
                rs.Update
            End If
            
            rs.MoveNext
        Loop Until rs.EOF
    End Sub

    Danke

    Freitag, 2. September 2011 11:17

Antworten

  • Hallo,

    zunächst Danke an Alle die an diesem Eintrag mitgearbeitet haben. Ich bin nun nach einigen schlaflosen Nächten auf den Fehler gekommen. Der Fehler war, dass mein Feld, in dieses geschrieben werden soll, eine Länge von bis zu 5 Zeichen akzeptiert und einige Rückgabewerte in meiner großen Datenbank hätten lt. Formel eine Länge von mehr als fünf Zeichen. Wenn ich diese Werte zuvor abfange, dann funktioniert es „fast“ problemlos. Aber nun habe ich das Problem, dass mein Rechner - aufgrund der Menge an Daten – sehr oft die Meldung „keine Rückmeldung“ fabriziert. Hier behelfe ich mich dass ich mit dem Befehl: „DoCmd.RunSQL Update…….“ arbeite. Nun bekomme ich die Fehlermeldung „keine Rückmeldung“ nicht mehr sehr oft – aber es funzt.

    Ich sollte vielleicht den MySQL-Server nicht auf dem gleichen Arbeitsplatzrechner laufen lassen als meine Access-Datenbank J.

    Danke nochmal an alle.

    Gruß

    Donnerstag, 15. September 2011 15:53

Alle Antworten

  • Gut das du diesen Fehler hast, ansonsten wärst du spätesten bei Datensatz Nummer 32768 in einen Fehler gelaufen. Und wenn ich es richtig sehe, willst du Dateiextensionen extrahieren. Also würde ich das ganze umschreiben (Luftcode):

    Const SQL As String = _
      "UPDATE [t-dateiuebersicht] " & _
      "SET [Dateityp] = ExtractFileExtension([DateiPfad]) " & _
      "WHERE Len(Trim([Dateityp] & '')) = 0 ;"
    
    CurrentDb.Execute SQL
    

    mit
    Public Function ExtractFileExtension(AFileName As Variant) As Variant
     
    Dim Char As String
    Dim Count As Long

     ExtractFileExtension = Null

     If Not IsNull(AFileName) Then For Count = Len(AFileName) To 1 Step -1
    Char = Mid(AFileName, Count, 1)
    If Char = "." Then ExtractFileExtension = Mid(AFileName, Count + 1) Exit For End If If Char = "\" Then Exit For End If Next Count End If End Function

     

     


    Freitag, 2. September 2011 11:55
    Moderator
  • Hallo Stefan,

    Stefan Hoffmann [MVP] wrote:

    Public Function ExtractFileExtension(AFileName As Variant) As Variant

      Dim Char As String
      Dim Count As Long

      ExtractFileExtension = Null

      If Not IsNull(AFileName) Then     For Count = Len(AFileName) To 1 Step -1 ...

    Das sollte eigentlich per InStrRev schneller gehen:

    Public Function ExtractFileExtension(AFileName As Variant) As Variant
        Dim lngCount As Long
    
      ExtractFileExtension = Null
    
      If Not IsNull(AFileName) Then
            lngCount = InStrRev(AFileName, ".")
            If lngCount > 0 Then
                ExtractFileExtension = Mid(AFileName, lngCount + 1)
                Exit Function
            End If
        End If
    End Function

    Ich nehme an, du nimmst mir die Umbenennung der Variablen nicht uebel? ;-)

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Freitag, 2. September 2011 23:55
    Moderator
  • Arg, ich wusste da wa noch was :) Allerdings muss trotzdem auf den backslash getestet werden, da Verzeichnisse wie C:\Temp\domain.tld\index.html möglich sind. Wenn dann keine Extension vorhanden ist, liefert es sonst das falsch ergebnis.
    Samstag, 3. September 2011 08:22
    Moderator
  • Hallo Stefan,

    Stefan Hoffmann [MVP] wrote:

    Allerdings muss trotzdem auf den backslash getestet werden, da
    Verzeichnisse wie C:\Temp\domain.tld\index.html möglich sind. Wenn dann
    keine Extension vorhanden ist, liefert es sonst das falsch ergebnis.

    Darauf wolltest du hinaus, hatte mich schon gewundert. ;-)

    Na dann ...

    Public Function ExtractFileExtension(AFileName As Variant) As Variant
        Dim lngCount As Long
        Dim lngBSl As Long
    
      ExtractFileExtension = Null
    
      If Not IsNull(AFileName) Then
            lngCount = InStrRev(AFileName, ".")
            If lngCount > 0 Then
                lngBSl = InStrRev(AFileName, "\")
                If lngBSl > lngCount Then
                Else
                    ExtractFileExtension = Mid(AFileName, lngCount + 1)
                End If
            End If
        End If
    End Function

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Samstag, 3. September 2011 09:47
    Moderator
  • Hallo sbrand

    sbrand wrote:

    ich komme über das Access2010-Forum auf diese Seite:
    http://answers.microsoft.com/de-de/office/forum/office_2010-access/acc2010-datenänderung-per-vba-nicht-möglich-fehler/1c889dda-d898-4014-b20f-8da3d918d4da.

    Ich möchte über Access2010 Daten auf meiner MySQL-Datenbank ändern. Die
    Datenbank hat über 1.000.000 Datensätze und ich bin definitv der einzige,
    der aktuell auf die Datenbank zugreift und bekomme beim Versuch einen
    Datensatz über ein VBA-Script zu ändern immer die Fehlermeldung:
    "Laufzeitfehler 3197 - 'Das Microsoft Access-Datenbankmodul hat den
    Vorgang angehalten, da Sie und ein weiterer Benutzer gleichzeitig
    versuchen, dieselben Daten zu ändern'". Wenn ich Felder über die
    Access-Funktionen ändere klappt dies Problemlos.

    In der MySQL-Datenbank habe ich ein Index-Feld im Format Double - Nicht
    Null und Auto Increment angelegt.

    Der Fehler, den Du beschreibst, tritt in der Regel auf, wenn sich etwas am Datensatz (vermeintlich) geändert hat.
    Es kann verschiedene Gründe geben, der häufigste ist, dass "ungünstige" Datentypen verwendet werden, meist Dezimalzahlen oder Datumswerte. Beide haben den Nachteil, dass diese gerundet werden können. Wenn Du also ein Double verwendest, der von MySQL unterschiedliche gehandhabt wird, als von Access/DAO, dann kann es sein, dass Access, wenn es den Wert wegschreibt ganz hinten irgendwie rundet (oder eben nicht) und MySQL dann eben nicht den gleichen Wert vorfindet, wie Access, weil er dort eben in den letzten Stellen anders aussieht.
    Access macht (wenn es keinen TimeStamp findet, wobei ich nicht weiss, ob es sowas in MySQL gibt) einen Update auf die Tabelle bei der es alle Felder des Datensatzes vergleicht, also z.B.:
    Update DeineTabelle
      Set DeinFeld1 = "irgendwas"
     Where DeinFeld1 = vermeintlicherOriginalWert
      And DeinFeld2 = vermeintlicherOriginalWert
      And ...
      AND DeinFeldx = vermeintlicherOriginalWert

    Wenn nun also sagen wir mal im MySQL ein Datumswert mit hh:nn:ss.sss (mit tausendstel) abgelegt ist, wird Access diesen Wert runden und daraus hh:nn:ss machen. Wenn es dann den Update macht, dann ist eben der Originalwert nicht identisch mit dem was Access meint, es sei der OriginalWert und schickt dann ein Statement mit einer Whereklausel:
     ..
     AND DeinFeldy = "hh:nn:ss"
     ..
    Den Datensatz gibt's aber in MySQL nicht, weil dort eben "hh:nn:ss.sss" drin steht und MySQL gibt einen NRF (No Record Found) zurück, woraus Access schliesst, dass der Datensatz bereits von jemand anderem verändert worden sei.

    Wie bereits geschrieben, wird mit dem MS SQL Server jeweils ein TimeStamp Feld verwendet. Dieses Feld ist ein Zahlenfeld, welches sich bei jedem Update ändert. Wenn Access so ein Feld vorfindet, dann weiss es, dass es nicht alle Felder vergleichen muss, sondern sich auf den TimeStamp verlassen kann und es wird nur dieses Feld für die Where Bedingung benutzen, um sicherzustellen, dass der Datensatz noch nicht geändert worden ist.

    Ich bin nicht sicher, ob MySQL auch sowas kennt. Falls ja, dann solltest Du mal dieses Feld so einbauen. Falls nein, dann solltest Du die Datentypen so ändern, dass diese auf beiden Seiten identisch sind. Alternative wäre dann, den Update per CurrentDB.Execute "UPDATE ...." selber zu programmieren.

    Was könnte es sonst noch sein. z.B. ein Trigger auf MySQL, der beim AfterUpdate abfeuert und daher dann den Datensatz verändert. In diesem Fall musst Du einen Me.Refresh beim Form_AfterUpdate Ereignis einbauen, damit der Datensatz nach dem Update neu eingelesen wird.

    Und schliesslich könnte es noch die fehlende Option dbSeeChanges sein, die Du beim éffnen des Recordsets vergessen hast.

    Set rs = db.OpenRecordset("[t-dateiuebersicht]", dbOpenDynaset,
    dbSeeChanges)

    Dass ein Minus-Zeichen (-) im Namen einer Tabelle nichts zu suchen hast, sei hier auch noch angemeckert. Operatoren sind äusserst ungünstige Bezeichner, da diese oft zu Fehlern in der Interpretation führen, wenn [] vergessen wird (wie in Deinem Code)

    Gruss
    Henry

    Montag, 5. September 2011 02:49
  • Gakki Stefan

    Stefan Hoffmann [MVP] wrote:

    Gut das du diesen Fehler hast, ansonsten wärst du spätesten bei Datensatz
    Nummer 32768 in einen Fehler gelaufen. Und wenn ich es richtig sehe,
    willst du Dateiextensionen extrahieren. Also würde ich das ganze
    umschreiben (Luftcode):

    Nur mit dem gemeldeten Fehler hat enicht viel zu tun, oder?

    Gruss
    Henry

    Montag, 5. September 2011 02:50
  • Hallo,

    zunächst Danke für Rückmeldung.

    Mein Code bzw. der Code von Peter funktioniert problemlos, solange ich die Daten nicht ändern lasse (rs.edit / rs.update). Wenn ich über debug.print oder msgbox eine Auswertung anzeigen lasse funzt es problemlos.

    Könnte evtl. ein MSSQL-Server abhilfe schaffen?

    MFG

    Montag, 5. September 2011 16:11
  • Hallo,

    sbrand wrote:

    [...] funktioniert problemlos, solange ich die Daten nicht ändern lasse
    (rs.edit / rs.update).

    Inwieweit MySQL da eine Rolle spielt, weiss ich nicht. Normalerweise ist
    die typische Ursache, dass ueber 2 Wege auf den gleichen DS zugegriffen
    wird, z.B. ueber ein gebundene Formular, und eine Prozedur, die den
    gleichen DS aendern will. Kann das bei dir der Fall sein?

    Ansonsten, bez. Access/MSSQL, die Kombination kommt oefter vor und damit
    kannst du auf mehr Knowhow in den Foren zurueckgreifen. Fuer MS-SQL ist
    allerdings die Timestamp-Geschichte sehr wichtig, siehe Henry's Antwort.

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Montag, 5. September 2011 17:21
    Moderator
  • Hast Du irgendwelche Trigger auf dem MySQL oder sonst was, was Du uns verschweigst? Irgendwas muss da ja laufen, während Du die MsgBox anzeigen lässt. Du könntest z.B. statt der MsgBox auch ein DoEvents() reinsetzen und dann schauen ob es geht. Allerdings: Wenn Du bereits hier Probleme hast, dann ist MySQL evt. nicht die richtige Backend.

    Es ist in jedem Fall einfacher von Access mit dem MSSQL-Server als Backend zu arbeiten, da beides aus dem gleichen Guss kommt. Genau genommen war sogar die SQL Server Gruppe lange Zeit für die Jet Engine, welche hinter Access werkelt verantwortlich. Das passt einfach viel besser zusammen.

    Gruss

    Henry

    Dienstag, 6. September 2011 02:33
  • Hallo zusammen,

    Nur als Neben-Information. MS Datenbanken zeigen ziemlich viele Fälle für:  

    Access + SQL + „Error 3197: The Microsoft jet database engine stopped the process because you and another user are attempting to change the same data at the same time.”

    Meistens handelte sich um korrupte Dantebanken. Kann man das Problem mit einer neuen SQL Datenbank/Tabelle mit gleichem Felder nachstellen?

    Grüße,

    Bogdan


    Ich bin gerne bei den Foren. Es kommt von Herzen. Es wird aber keine implizite oder sonstige Garantie für die geposteten Antworte / Informationen gewährt
    Mittwoch, 7. September 2011 15:17
    Moderator
  • hi Peter,

    die korrekten Begriff sind +Access +MySQL +"Error 3197"

    Hier ist es tatsächlich so, das die Methode wie Access die mögliche Änderung der Daten abfragt (siehe Henrys Antwort) zu diesem Phänomen führt.
    Hier gibt es nur die Möglichkeit von Recordset-Manipulationen abzusehen, und mittels Execute eine SQL-DML direkt auszuführen.

    Mittwoch, 7. September 2011 15:24
    Moderator
  • Hallo,

    zunächst Danke an Alle die an diesem Eintrag mitgearbeitet haben. Ich bin nun nach einigen schlaflosen Nächten auf den Fehler gekommen. Der Fehler war, dass mein Feld, in dieses geschrieben werden soll, eine Länge von bis zu 5 Zeichen akzeptiert und einige Rückgabewerte in meiner großen Datenbank hätten lt. Formel eine Länge von mehr als fünf Zeichen. Wenn ich diese Werte zuvor abfange, dann funktioniert es „fast“ problemlos. Aber nun habe ich das Problem, dass mein Rechner - aufgrund der Menge an Daten – sehr oft die Meldung „keine Rückmeldung“ fabriziert. Hier behelfe ich mich dass ich mit dem Befehl: „DoCmd.RunSQL Update…….“ arbeite. Nun bekomme ich die Fehlermeldung „keine Rückmeldung“ nicht mehr sehr oft – aber es funzt.

    Ich sollte vielleicht den MySQL-Server nicht auf dem gleichen Arbeitsplatzrechner laufen lassen als meine Access-Datenbank J.

    Danke nochmal an alle.

    Gruß

    Donnerstag, 15. September 2011 15:53
  • Wenn ich das richtig verstehe, dann ist "keine Rückmeldung" ein Command Timeout, weil Du zu viele Daten auf's mal abarbeitest.

    Zuerst mal: Wieso verwendest Du "DoCmd.RunSQL" und nicht die CurrentDb() Instanz, die Du bereits hast? Also einfach:

    Dim db As Database
    Set db = CurrentDb()
    db.Execute "UPDATE ..."
    Set db = Nothing
    


    Noch schneller düfte es sein, wenn Du den Update in eine Passtrough Abfrage packst und komplett dem MySQL Server überantwortest. Dies sollte eigentlisch schneller sein, als das von Access aus zu machen, da Access ja nicht nur den Update macht, sondern noch andere Dinge überwacht, wie z.B. die Korrektheit des SQL Statements, etc.

    Weiterer Ansätze:
    Nicht alle auf's mal, sondern Portionenweise, wobei Du die Portionen z.B. über den Primärschlüssel filtern könntest.
    Im MySQL wird es vermutlich eine Option geben, um den Command Timeout heraufzusetzen. Vergrössere diesen auf einen ausreichenden Wert, damit der Timeout nicht mehr auftritt.
    Schlussendlich solltest Du Dir mal die Tabellen Struktur anschauen. Sind da die Felder Indexiert, über die Du den Update filterst (also die Felder in der WHERE Condition des Updates). Falls nein, wäre es Zeit, die Index Strukturen ein bisschen genauer unter die Lupe zu nehmen.

    Gruss
    Henry


    Freitag, 16. September 2011 03:23
  • Hallo,

    zunächst Danke für die Rückmeldung.

    Jetzt habe ich das ganze "Portioniert" und einen Index auf das zu ändernde Feld und den Timeout-Wert für das erweiterte Netzwerk erhöht und nun funktioniert wesentlich besser ´..)). Nur die Menge an Datensätzen erlaubt derzeit kein schnelleres Arbeiten ...))).

    Gruß

    Mittwoch, 21. September 2011 06:42
  • Hallo,

    einen Index auf das zu ändernde Feld anzulegen ist keine gute Idee. Damit wird die Performance schlechter, weil nun nebst des Datensatzes auch noch der Index geändert werden muss. Du solltest nur einen Index über die Felder anlegen, die in der WHERE Bedingung drin sind und nicht das zu ändernde Feld betreffen. Sonst wird der Index kaum benutzt werden können. Ich weiss zwar nicht wie das MySQL handhabt, aber auch MySQL kocht nur mit Wasser.

    Gruss

    Henry

    Mittwoch, 21. September 2011 07:08
  • Hallo,

    in meiner "DoCmd.RunSQL"-Anweisung ist dies das gleiche. Ich selektiere nach allen leeren Feldern und lasse diese durch einen Standardwert ersetzen.

    Es funzt aber nur wirklich akzeptabel :)

    Danke

    Mittwoch, 21. September 2011 07:51
  • Hallo,

    sbrand wrote:

    in meiner "DoCmd.RunSQL"-Anweisung ist dies das gleiche. Ich selektiere
    nach allen leeren Feldern und lasse diese durch einen Standardwert
    ersetzen.

    Es funzt aber nur wirklich akzeptabel :)

    Du solltst in diesem Fall dringend auf eine Stored Procedure ausweichen, die Du direkt auf dem MySQL Server laufen lässt, oder dann über eine Passthrough Query arbeiten.

    Was macht Access vermutlich in Dienem Fall:

    Es holt sich vermutlich die komplette Tabelle via Tablescan (oder mindestend die betroffenen Records) rüber in den Tempstorage, der ausgeht und daher auf Platte gespeicher werden muss. Dann schreibt es die Werte gemäss Deiner Anweisung in die einzelnen Records und schreibt diese dann wieder in die Datenbank zurück. Da es kein Timestamp Feld findet, muss es jetzt, um sicherzustellen, dass niemand den Datensatz zwischenzeitlich geändert hat, alle Felder auf Gleichheit prüfen.
    Das ist dermassen viel Overhead, der nicht nötig ist, dass das nie richtig akzeptabel (in wenigen Sekunden oder Sekundenbruchteilen) laufen wird.

    Mit einer Passthrough Abfrage passiert folgendes:
    Access nimmt mit dem MySQL Server Kontakt auf und schiebt das MySQL UPDATE STatement rüber. Allenfalls übergibt es noch Parameter, falls Du welche übergibst. Dann überlässt es die komplette Arbeit inkl. Parsing des SQL Statements dem MySQL Server. Dieser kann nun selber entscheiden, ob er einen Index verwenden wird oder nicht, die Records (oder Tabelle) während des Updates sperren und die Werte da reinschreiben. Dannach die Locks wieder freigeben und Access melden ob alles richtig gelaufen ist oder nicht. Das ist wesentlich schneller und Access muss sich nun nicht durch "fremde" Daten wühlen.

    Versuch' es doch einfach mal mit einer Passtrough Abfrage und vergleiche die Antwortzeiten. Du kannst dass zuerst ja mal händisch im Abfrage Editor machen. Wenn's läuft, kannst Du Das SQL Statement einfach per VBA erzeugen, in die Abfrage abfüllen und diese abschicken. Oder Du verwendest eben Parameter, dann musst Du nur die Parameter setzen, bevor Du die Abfrage ausführst.

    Gruss
    Henry

    Donnerstag, 22. September 2011 09:25
  • Hallo,

    sbrand wrote:

    in meiner "DoCmd.RunSQL"-Anweisung ist dies das gleiche. Ich selektiere
    nach allen leeren Feldern und lasse diese durch einen Standardwert
    ersetzen.

    Es funzt aber nur wirklich akzeptabel :)

    Du hast Henrys Hinweis auf .Execute ignoriert, das auch nochmal schneller
    ist, weil ...

    - DoCmd startet den Makro-Interpreter, der das Statement ausfuehrt.
    - Es fuellt u.U. vor dem eigentlichen Update den Undo-Speicher.

    Dagegen arbeitet die .Execute-Methode direkt mit dem DB-Objekt. Beispiel:

    Dim Db As DAO.Database
    Set Db = CurrentDb

    Db.Execute strSQL, dbFailOnError

    Gruss - Peter


    Mitglied im http://www.dbdev.org
    FAQ: http://www.donkarl.com

    Donnerstag, 22. September 2011 09:59
    Moderator