none
Endlos-Formular mit ADO-Recordset: letzte Zeile verschwindet bei neuem Datensatz

    Frage

  • Hallo,

    ich verwende MS Access 2016 unter Windows 10 mit einer MySQL-DB über ODBC. Es zeigte sich, dass das Arbeiten mit ADODB-Recordsets einige Vorteile bietet, so habe ich sämtliche Formulare auf ADODB umgestellt (Im Open-Event Set Me.Recordset = ...). In Endlos-Formularen habe ich jetzt ein sonderbares Phänomen: wenn ich einen neuen Datensatz anlege verschwindet der zuvor letzte Datensatz aus der Anzeige (alle Felder leer bis auf das erste, dort erscheint #Fehler). Nach Abschluss der Neuanlage werden wieder alle Daten korrekt angezeigt.

    Ich habe mir die Mühe gemacht, das Problem in einer neuen Access-DB nachzustellen. Für die Test-Anwendung habe ich eigens eine neue Tabelle auf einer mySQL-DB angelegt. Das Problem tritt auf, sobald Text-Felder ins Spiel kommen (VARCHAR()). Hier der Code für die Test-Anwendung:

    ----------------------------------------DB-Modell----------------------------------------------'
    Land VARCHAR(5) PK, NN, UQ, default 'de'
    Bezeichnung VARCHAR(45) default 'Null'
    ---------------------------------------Source-Code---------------------------------------------
    Option Compare Database
    Option Explicit

    Const adoConStr = "ODBC;Provider=MSDASQL;Driver={MySQL ODBC 5.3 Unicode Driver};Server=127.0.0.1;Port=3306;Database=sw_kalender;Uid=xxxxxx;Pwd=xxxxxx;Option=3"
    Private adoConn As ADODB.Connection

    Private Sub Form_Open(Cancel As Integer)

        If Not Me.Recordset Is Nothing Then
            Me.Recordset.Close
            Set Me.Recordset = Nothing
        End If
        Set Me.Recordset = OpenADORec("kc_general.sw_countries")

    End Sub

    Private Sub Form_Close()

        If Not Me.Recordset Is Nothing Then
            Me.Recordset.Close
            Set Me.Recordset = Nothing
        End If
        If Not adoConn Is Nothing Then
            adoConn.Close
            Set adoConn = Nothing
        End If

    End Sub

    Private Function OpenADORec(ByVal tbl As String, _
                                Optional ByVal flt As String = "", _
                                Optional ByVal ord As String = "", _
                                Optional ByVal grp As String = "") As ADODB.Recordset
    On Error GoTo Err_OpenADORec

        Dim rec As ADODB.Recordset
        Dim sql As String

        If adoConn Is Nothing Then
            Set adoConn = New ADODB.Connection
            adoConn.Errors.Clear
            adoConn.Open adoConStr
        End If

        If Not adoConn Is Nothing Then
            If adoConn.State = adStateOpen Then
                Set rec = New ADODB.Recordset
                sql = "SELECT * FROM " & LCase(tbl) & _
                      IIf(Len(flt) > 0, " WHERE " & flt, "") & _
                      IIf(Len(grp) > 0, " GROUP BY " & grp, "") & _
                      IIf(Len(ord) > 0, " ORDER BY " & ord, "") & ";"
                With rec
                   Set .ActiveConnection = adoConn
                   .Source = sql
                   .LockType = adLockOptimistic
                   .CursorType = adOpenStatic
                   .CursorLocation = adUseClient
                   .Open
                End With
           End If
        End If

    Exit_OpenADORec:
        Set OpenADORec = rec
        Exit Function

    Err_OpenADORec:
        MsgBox "OpenADORec:" & Chr(13) & Chr(10) & Err.Description
        Resume Exit_OpenADORec

    End Function

    Interessant ist vielleicht noch zu erwähnen: wenn ich auf die DB über eine verknüpfte Tabelle mittels DAO zugreife, habe ich das Problem nicht.

    Hat jemand eine Idee, woran das liegen könnte?

    Herzlichen Dank vorab für jeden Tipp.

    Mittwoch, 12. Oktober 2016 16:55

Alle Antworten

  • Hallo Peter,

    auch wenn ich mich seit längerem von Access verabschiedet habe:

    Das ist ein Klassiker in den Fällen, wenn Access die Zeile nach dem Einfügen nicht wiederfindet, dann taucht "Gelöscht" oder "Fehler" in den Spalten auf. Ursache gibt es mehrere, hier könnten es die Standardwerte auf MySQL Seite sein von denen Access nichts "weiß".  Einige weitere Tipps mehr findet man unter: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html (am wichtigsten wäre der richtige Primärschlüssel und eine Timestamp Spalte in allen Problemfällen).

    Wobei Du i. a. über verknüpfte Tabellen arbeiten solltest, anstatt die Recordsets selber zu erstellen. Wenn doch sollte man die CurrentConnection verwenden. Auch das "*" sollte man meiden, sondern explizit die verwendeten Spalten angeben (denn sonst werden alle Spalten abgerufen). Am besten erstellt man sich eine Abfrage und verwendet sie als Formulardatenquelle.

    Auch solltest Du überlegen, ob Du nicht auf ADO verzichtest und durchweg DAO einsetzt. Es ist mittlerweile abgekündigt (und wirklich gut funktioniert hat es abseits von ADPs nie ;)

    Gruß Elmar

    P.S. Option=3 ist veraltet, siehe https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-combos

    Donnerstag, 13. Oktober 2016 07:25
  • Hallo Elmar,

    sorry für die späte Reaktion - ich habe Deine Antwort erst jetzt gesehen (dachte, ich werde automatisch per Email benachrichtigt...).

    Das mit ADO als depricated schockt mich jetzt, da in der Access-Dokumentation zu DAO ja ausdrücklich darauf hingewiesen wird, dass die Option "Pass-Through" ab Access 2013 nicht mehr unterstütz wird und man statt dessen ADO verwenden sollte. Die Access-Abfragen von DAO sind leider nicht wirklich performant, vor allem, wenn man über's Internet arbeitet (entfernter SQL-Server). DAO führt meist ein Full-Table-Scan aus und filtert dann lokal.

    Das Mit der Option 2 bringt leider keine Veränderung. In meinen Tabellen habe ich auch überall einen Timestamp sowie Primary-Auto-Key. Leider tritt das Problem dennoch auf.

    Gruß,

    Peter.

    Freitag, 25. November 2016 15:10
  • Hallo Peter,

    veraltet ist ODBC Direkt (RDO in VB Classic) seit 2010; Pass-Through Abfragen über [ACE]DAO gibt es weiterhin, siehe z. B. QueryDef.Connect. Die Entscheidung basiert auf der Entscheidung, den SQL Server OleDb Provider nicht mehr weiterzuentwickeln (wobei dies bisher für SQL Server 2016 noch der Fall ist). Unabhängig davon sind beide nicht für aktualisierbare Datenquellen geeignet.

    Ob eine Abfrage von Access via ODBC auf dem Server ausgewertet wird, hängt von mehreren Faktoren ab, nicht davon ob man DAO oder ADO einsetzt. So sollte immer ein Primärschlüssel existieren. Ebenso sollte die Datentypen soweit als möglich kompatibel sein, ggf. eine Timestamp Spalte hinzufügen, damit Access Änderungen erkennen kann. Des weiteren sollte man VBA Funktionen in Abfragen möglichst vermeiden, da sie logischerweise nicht in SQL übersetzt werden können.

    Um zu analysieren, wie die Abfrage an den Server gesendet wird, schalte ODBC Tracing ein, siehe Trace MS Access DB activity. (Registry Eintrag entsprechend Access Version).

    Mehr könnte man evtl. sagen, wenn die Abfrage selbst und die dahinter liegende(n) Tabellen sehen würde. Für die oben genannte Tabelle probiere einen Integer Primary Key (mit Auto_Increment), und definiere Land (VARCHAR) als zusätzlichen Unique Key.

    Gruß Elmar

    Freitag, 25. November 2016 16:25
  • Hallo Elmar,

    das mit DAO und Pass-Through werde ich mal austesten. Danke für den Tipp.Gruß,

    Peter.

    Freitag, 25. November 2016 17:30
  • Hallo Elmar,

    allerdings gab es einen Grund, warum ich auf ADO umgestiegen war: ich baue die Verbindung zu der MySQL-DB über einen SSH-Tunnel auf. Wenn die Verbindung unterbrochen wird, funktioniert die bestehende Connection zur DB nicht mehr ("MySQL Server has gone away (#2006)"). Es gibt leider keine Möglichkeit bei DAO, die Connection des Workspace(0) neu aufzubauen.

    Mit ADO kann ich den Fehler abfangen, den SSH-TUnnel sowie die Connection neu aufbauen und alles läuft prima.

    Hast Du auch eine Idee, wie ich das mit DAO lösen kann?

    Gruß, Peter.

    Freitag, 25. November 2016 22:01
  • Am 25.11.2016 schrieb Peter Künemann:

    allerdings gab es einen Grund, warum ich auf ADO umgestiegen war: ich baue die Verbindung zu der MySQL-DB über einen SSH-Tunnel auf. Wenn die Verbindung unterbrochen wird, funktioniert die bestehende Connection zur DB nicht mehr ("MySQL Server has gone away (#2006)"). Es gibt leider keine Möglichkeit bei DAO, die Connection des Workspace(0) neu aufzubauen.

    Mit ADO kann ich den Fehler abfangen, den SSH-TUnnel sowie die Connection neu aufbauen und alles läuft prima.

    Wenn Du einen Connectionstring hast, sollte der nach so einem Fehler
    eigentlich auch leer sein, oder nicht? Falls ja, auf das Leer sein abprüfen und wieder neu herstellen. Beispiel aus meiner Access-DB:

    If strConnect = "" Then strConnect = GetODBC

    GetODBC holt aus einer lokalen Tabelle den Connectionstring:

    GetODBC = DLookup("fldKeyWert", "tblKeys", "fldKey= 'ODBCVerbindungsstring'")

    Servus
    Winfried


    Access-FAQ: http://www.donkarl.com/AccessFAQ.htm Access-Stammtisch: http://www.access-muenchen.de
    NNTP-Bridge für MS-Foren: http://communitybridge.codeplex.com/
    vbeTwister: http://www.vbetwister.com/

    Samstag, 26. November 2016 09:28
  • Hallo Winfried,

    das Problem ist die Connection, die Access für DAO-Objekte intern hält im Workspace(0) - darauf hat mal leider keinen Zugriff. Näheres zu diesem Problem findest Du unter einem Threat in der MS-Community: https://answers.microsoft.com/de-de/msoffice/forum/msoffice_access-mso_win10/access-dao-connection-neu-aufbauen/c16cadc7-554a-4f65-baf9-db540c5822cf

    Gruß,
    Peter.

    Samstag, 26. November 2016 10:39
  • Am 26.11.2016 schrieb Peter Künemann:

    das Problem ist die Connection, die Access für DAO-Objekte intern hält im Workspace(0) - darauf hat mal leider keinen Zugriff. Näheres zu diesem Problem findest Du unter einem Threat in der MS-Community:

    Das ist mit ein Grund, weshalb ich angefangen habe alles von
    verknüpften Tabellen auf Tableless umzustellen. Abfragen sind fast
    alle auf PT-Abfragen (die eine SP auf dem MS-SQL aufrufen) umgestellt.
    Damit man dort die DB und den Server aktualisieren kann, hab ich diese Funktion erstellt:

    Dim db As DAO.Database
    dim qdf As DAO.QueryDef
    Dim AnzQDF As Integer
    Dim i As Integer
    
            If strConnect = "" Then
                    strConnect = GetODBC()
            End If
            Set db = CurrentDb
            AnzQDF = db.QueryDefs.Count - 1
            For i = AnzQDF To 0 Step -1
                    If Left(db.QueryDefs(i).Name, 6) = "qrySP_" Then
                            db.QueryDefs(i).Connect = strConnect
                    End If
    
            Next i
            Set qdf = Nothing
            Set db = Nothing
    

    Schon hab ich alle PT-Abfragen mit dem aktuellen Connectionstring
    versorgt. Wie lange das mit 90 eingebundenen Tabellen dauert, weiß ich
    nicht.

    Servus
    Winfried


    Access-FAQ: http://www.donkarl.com/AccessFAQ.htm Access-Stammtisch: http://www.access-muenchen.de
    NNTP-Bridge für MS-Foren: http://communitybridge.codeplex.com/
    vbeTwister: http://www.vbetwister.com/

    Samstag, 26. November 2016 12:40
  • Hallo Winfried,

    interessanter Ansatz. Wie vermeidest Du, dass User und Passwort mit im Connect-String gespeichert werden (Sicherheits-Aspekt). Und wie erfolgt der Zugriff auf die MySQL-DB in Forms für Neuanlage und Ändern?

    Bei verknüpften Tabellen werden User und Passwort nicht mitgespeichert. Vielmehr hält Access diese Zugangsdaten aus dem ersten Connect mit der Datenquelle im eigenen Workspace(0)(0) vor. Und Neuanlagen und Änderungen sind für ADODB-Recordsets kein Problem. Wie schaut es da mit DAO-Pass-Through-Queries aus?

    Gruß,
    Peter

    Sonntag, 27. November 2016 18:27
  • Am 27.11.2016 schrieb Peter Künemann:

    interessanter Ansatz. Wie vermeidest Du, dass User und Passwort mit im Connect-String gespeichert werden (Sicherheits-Aspekt). Und wie erfolgt der Zugriff auf die MySQL-DB in Forms für Neuanlage und Ändern?

    User und PW wird nicht gespeichert, ich arbeite innerhalb der Windows
    Domain, dessen Admin ich auch bin, mit TrustedConnection = True. Die
    Benutzer die auf die MS SQL DB zugreifen können sollen, sind in einer
    Sicherheitsgruppe vom AD eingetragen, die Gruppe ist auf die DB
    berechtigt. Bei Neuanlage gibt es nur das AD, sonst nichts.

    Bei verknüpften Tabellen werden User und Passwort nicht mitgespeichert. Vielmehr hält Access diese Zugangsdaten aus dem ersten Connect mit der Datenquelle im eigenen Workspace(0)(0) vor. Und Neuanlagen und Änderungen sind für ADODB-Recordsets kein Problem. Wie schaut es da mit DAO-Pass-Through-Queries aus?

    User + PW hab ich nicht, deshalb reicht der Connection String mit
    TrustedConnection = True bei mir aus.

    Servus
    Winfried


    Access-FAQ: http://www.donkarl.com/AccessFAQ.htm Access-Stammtisch: http://www.access-muenchen.de
    NNTP-Bridge für MS-Foren: http://communitybridge.codeplex.com/
    vbeTwister: http://www.vbetwister.com/

    Sonntag, 27. November 2016 19:28
  • Am 27.11.2016 schrieb Peter Künemann:

    Wie schaut es da mit DAO-Pass-Through-Queries aus?

    Hier noch mein Connection String für die MS SQL DB:

    ODBC;DRIVER=SQL Server;SERVER=Server\Instanz;DATABASE=MeineDB;Trusted_Connection=Yes

    Vermutlich kannst Du den Weg mit Trusted_Connection=Yes gar nicht
    gehen, oder vielleicht doch?

    Servus
    Winfried


    Access-FAQ: http://www.donkarl.com/AccessFAQ.htm Access-Stammtisch: http://www.access-muenchen.de
    NNTP-Bridge für MS-Foren: http://communitybridge.codeplex.com/
    vbeTwister: http://www.vbetwister.com/

    Sonntag, 27. November 2016 19:31
  • Hallo Winfried,

    unsere Anwendung liegt auf einem DB-Server im RZ und der Zugriff erfolgt übers Internet - Trusted ist da also keine Option. Das war mit ein Grund, warum die QueryDefs ausgeschieden waren. Im Übrigen habe ich das Problem mit der Lost-Connection auch bei den QueryDefs, da auch diese die interne Connection aus der Workspace(0) verwenden, sobald der Connection-String bereits einmal verwendet wurde.

    Gruß,
    Peter.

    Dienstag, 29. November 2016 19:34