Datenaktualisierung scheitert

[Mokkie]

Hallo,

ich habe ein großes, ungelöstes Problem, mit einer verknüpften SQL Server Tabelle.
Einfach Die Tabelle öffnen in Access und Daten ändern, ist möglich.
Nutze ich aber das bestehende Formular dazu, kommt es , das ist auch seltsam, nicht bei allen DS zu Fehlern.
ODBC Tiemout usw. das Timeout habe ich schon angepasst.
Um das ganz zu umgehen habe ich den Zugriff auf ADODBC geändert, wie folgt

Code [Auswählen] Erweitern

Private Sub COMPANY_MEMO_AfterUpdate()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim lActId As Long
Dim strsql As String

 lActId = Forms![Hotline -> Terminallist]!COMPANY_ID
 
   ' Verbindung und Recordset wiederherstellen
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=MSOLEDBSQL;Server=AFC-MSSQL2016V;Database=FENG;Trusted_Connection=yes;"
   
    strsql = "SELECT  " _
            & " COMPANY_MEMO" _
            & " FROM data.COMPANY WHERE COMPANY_ID = " & lActId & ""
   
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strsql, conn, 1, 3 ' adOpenKeyset, adLockOptimistic

    If Not rs.EOF Then
        rs.Fields("COMPANY_MEMO").Value = Me!COMPANY_MEMO.Value ' Ändere das Feld
   
        rs.Update
    End If

 :
Und beim Update bekomme auch dann auch hier einen Fehler:

-2147217871(80040e31)
Query timeout expired

Ich weiß jetzt nicht weiter und bin für jeden Tipp dankbar.
Grüße Babsi

[Bitsqueezer]

Hallo Babsi,

ich empfehle, einen Blick in den SQL Server Profiler bzw. die XEvents in SSMS zu wagen, da wirst Du sehen, was da gerade zwischen Access und SQL Server passiert.

ADO zu verwenden, ist OK, aber warum so dermaßen umständlich? Du hast offensichtlich Early Binding, sonst könntest Du nicht "As ... ADODB. ..." verwenden.

1. Auch wenn es möglich ist, sollte man nie "As New" verwenden, da dies ein "Set ... = Nothing" am Ende verhindert. Immer getrennt schreiben als "As ADODB. ..." und dann "Set .. xx = New ADODB. ..." verwenden, am Ende dann mit "Set xx = Nothing" das Objekt entfernen.

2. Wenn Du dann sowieso (hier) schon "New" verwendest (bzw. mit Set ... = New ...), dann hast Du bereits ein passendes Objekt, warum verwendest Du dann noch die Late Binding Variante mit "Create Object"? Das ist überflüssig.

3. Warum ein Recordset mit SELECT erstellen, um es dann umständlich mit dem Recordset mit Update zu schreiben? Wenn Du schon ADO verwendest und damit in T-SQL arbeitest, kannst Du doch auch einfach einen ADODB.Command erstellen und dann mit

Code [Auswählen] Erweitern

cmd.Execute "UPDATE data.COMPANY SET COMPANY_MEMO = '" & Me.COMPANY_MEMO & "' WHERE COMPANY_ID = " & Forms![Hotline -> Terminallist]!COMPANY_ID
Den Update erledigen. Kein Recordset notwendig, nur das Connection Object und das Command Object.

Wenn aber beides nicht funktioniert, gibt es ja offensichtlich ein Problem auf SQL Server Seite, also am besten, wie gesagt, Profiler/XEvents verwenden und schauen, was da genau passiert.

BTW: "[Hotline -> Terminallist]" ist ein sehr schlechter Name für Objekte. In Objektnamen sollte man Leerzeichen, Sonderzeichen und Umlaute grundsätzlich vermeiden. Ein guter Name wäre z.B. "frmHotline_Terminallist".
Das gilt nicht nur für Formularnamen, sondern ALLE Namen. Nicht zu verwechseln mit Beschriftungen in Labeln etc.

Weitere Informationen zum Timeout:
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-timeouts

Gruß

Christian

[Mokkie]

Hallo Christian,

ich danke Dir für deine Hinweise.

Ich versuche jetzt erst mal die se ADODBC Verbindung richtig aufzubauebe und den Zugriff darauf.
Habe da bisher nicht mit gearbeitet, daher ist es so chaotisch.

Ich habe nun folgendes:

Code [Auswählen] Erweitern

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As Object
Dim lActId As Long
Dim strsql As String, sMemo As String

 lActId = Forms![Hotline -> Terminallist]!COMPANY_ID
 sMemo = IsEmpty(Me!COMPANY_MEMO.Value)
 
   ' Verbindung und Recordset wiederherstellen
    Set conn = CreateObject("ADODB.Connection")
        conn.Open "Provider=MSOLEDBSQL;Server=AFC-MSSQL2016V;Database=FENG;Trusted_Connection=yes;"
    Set cmd = CreateObject("ADODB.Command")
        cmd.Execute "UPDATE data.COMPANY SET COMPANY_MEMO = '" & sMemo & "' WHERE COMPANY_ID = " & lActId
    ' Set the command properties
bekomme aber die Fehlermeldung, dass die Vebindung nicht verwednet werden um diesen Vorgang auszuführen, sie ist entweder geschlossen oder oder in diesem zusammenhang unzulässig...
Vielleicht kannst Du noch mal schauen, ich google mal...

[Beaker s.a.]

Ich habe nun folgendes:

Dann hast du aber Christians Ausführungen zu "As New" bzw. Early-/Late-
Binding nicht gelesen.

[Bitsqueezer]

Hallo Babsi,

Auch hier verwendest Du wieder "CreateObject. Und rs, obwohl nicht mehr gebraucht.

Beispiel:

Code [Auswählen] Erweitern

Dim conn As ADODB.Connection
Dim cmd  As ADODB.Command
Dim lActId As Long
Dim strsql As String, sMemo As String

If CurrentProject.AllForms("[Hotline -> Terminallist]").IsLoaded Then
    lActId = Nz(Forms![Hotline -> Terminallist]!COMPANY_ID, 0)
 
sMemo = Nz(Me.COMPANY_MEMO)
 
' Verbindung und Recordset wiederherstellen
Set conn = New ADODB.Connection
conn.Open "Provider=MSOLEDBSQL;Server=AFC-MSSQL2016V;Database=FENG;Trusted_Connection=yes;"

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.Execute "UPDATE data.COMPANY SET COMPANY_MEMO = '" & sMemo & "' WHERE COMPANY_ID = " & lActId

conn.Close
Set cmd = Nothing
Set conn = Nothing
Sollte so funktionieren.

Gruß

Christian

[Mokkie]

Hallo Beaker,

nein bestimmt nicht, ich muss erst mal raffen was das ist.

Hallo Christian,

immer noch einen Fehler

Comandtest was not set for Command object

[markusxy]

Schaut alles nach viel Copy&Paste aus.
Command vielleicht vor der Nutzung mal ordentlich ansehen, wenn man aber eh Text per VBA zusammenstoppelt braucht man es nicht.

Code [Auswählen] Erweitern

Set conn = New ADODB.Connection
conn.Open "Provider=MSOLEDBSQL;Server=AFC-MSSQL2016V;Database=FENG;Trusted_Connection=yes;"
conn.Execute "UPDATE data.COMPANY SET COMPANY_MEMO = '" & sMemo & "' WHERE COMPANY_ID = " & lActId


[Mokkie]

Hallo markusxy,

ja, das ist zusammengestoppelt. Und alles per C&P, auch richtig.
Und ich muss mir das bestimmt noch gnauer ansehen.
Im moment will ich das einfach nur hinbekomme, das

conn.Execute

läuft nun. Also, es wird aber auch hier der selben Fehler geschmiessen, Abfragetimeout. Wenn dasnn etwas im Dbeugmodus gewartet wird,, ich auf F5 gehe , dann klappt es. Es hat wohl wirklich was mit der Tabelle auf dem server zu tun.

Ich danke euch! Ich werde mir das am Wochenende mal durchlesen.Habe irgendwo noch ein Buch...Hoffe darin etwas zu finden

@christian

BTW: "[Hotline -> Terminallist]" ist ein sehr schlechter Name für Objekte. In Objektnamen sollte man Leerzeichen, Sonderzeichen und Umlaute grundsätzlich vermeiden. Ein guter Name wäre z.B. "frmHotline_Terminallist".
Das gilt nicht nur für Formularnamen, sondern ALLE Namen. Nicht zu verwechseln mit Beschriftungen in Labeln etc.

Das ist alles so migriert worden, ich hätte niemals solche Namen verwendet, alles sehr alt.

[markusxy]

Und alles per C&P, auch richtig.

Das war auf das Beispiel von Christian bezogen, weil ein Command so einzusetzen ist eher dilletantisch.
Man verwendet es um bei Abfragen oder Prozeduren Parameter als Objekte zu übergeben und nicht per sql zusammenzusetzen.
Hat in erster Linie den Vorteil, dass das Objekt für die richtige Formatierung sorgt.

[Bitsqueezer]

Hallo Babsi,

sorry, war aus dem Kopf, so sollte es gehen:

Code [Auswählen] Erweitern

    Set conn = New ADODB.Connection
    conn.Open "Provider=MSOLEDBSQL;Server=AFC-MSSQL2016V;Database=FENG;Trusted_Connection=yes;"

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    cmd.CommandText = "UPDATE data.COMPANY SET COMPANY_MEMO = '" & sMemo & "' WHERE COMPANY_ID = " & lActId
    cmd.Execute
   
    conn.Close
    Set cmd = Nothing
    Set conn = Nothing
@markusxy : Die Variante mit "conn.Execute" kannte ich allerdings auch noch nicht. Unter einem Connection-Object nach Execute zu suchen, kam mir nie in den Sinn..

Die cmd-Variante hat den Vorteil, daß man sie gezielter auch bei SQL Server einsetzen kann, wenn es darum geht, z.B. eine Stored Procedure auszuführen, je nach CommandType. Aber für Dynamic SQL ist das conn.Execute auch sicher eine Variante (nie probiert, keine Ahnung, welche Vor- oder Nachteile es hat).

Generell gilt: Diese Vorgehensweise bei Verwendung von "Execute" mit Dynamic SQL hat in jedem Fall den Nachteil, daß es für SQL Injection anfällig ist, da man im Gegensatz zu Access SQL bei T-SQL auch Befehele mit ";" trennen kann und damit gerade bei Änderung von Textfeldern sehr leicht das Textfeld mit "'" schließen und "böse" Befehle einfügen kann. Darüber hinaus kann ein "'" im Text ganz simpel auch zu Fehlern führen.

Wenn eine verlinkte Tabelle vorhanden ist, die man mit Access bearbeiten kann, sollte eine DAO-Query (also eine UPDATE-Query!) mit "PARAMETERS" ebenso funktionieren, dann kann man eine QueryDef erstellen, Parameters-Collection sicher befüllen (auch wenn ein "'" im Text enthalten ist) und ausführen.
ADO wird zur Ausführung dann nicht gebraucht.

Alternativ erstellt man eine Stored Procedure auf dem SQL Server, die man per cmd-Objekt sicher mit Parametern befüllen kann und die dann ausgeführt werden, dabei auch gesichert werden kann durch Ausführungsrechte für Stored Procedures.

Gruß

Christian

[Mokkie]

Hallo Christian,

sorry, war aus dem Kopf, so sollte es gehen:

Ich bitte Dich...

Es funktioniert, genau wie der das von markusxy. Allerdings hat er was zum Command geschrieben.

Egal wie ich es mache, ich laufe dauernd auf den selben Fehler.
Abfragetiemout


Ich habe die Tabelle schon untersucht, jetzt werde ich mich doch wohl Serverseitig auf die Suche machen müssen.
 
Ich habe hier schon ein Constraint gelöscht, das auf dem Feld Memo lag, da konnte ich selbst auf dem Server das Feld nicht aktualisieren.

Habt vielen Danke für eure Bemühungen, und Erläuterungen.

Ich melde mich wieder und hoffe dass ich den Fehler finde.

[knobbi38]

Hallo Babsi,

noch ein Nachtrag:
In deinen Beispielen wird ein OLEDB-Treiber verwendet und kein ODBC, wie du mehrfach fälschlicherweise angenommen hast.

Nur mal so am Rande ...

Gruß Knobbi38

[markusxy]

Generell gilt: Diese Vorgehensweise bei Verwendung von "Execute" mit Dynamic SQL hat in jedem Fall den Nachteil, daß es für SQL Injection anfällig ist

Und warum zeigst du es dann so?
Wie wäre es mit bei dynamic sql Parameter zu verwenden?
Das wäre für mich wie bereits erwähnt der übliche Weg .

[markusxy]

Ich melde mich wieder und hoffe dass ich den Fehler finde.

Viel Glück bei der Suche, die Hinweise aus der ersten Antwort werden dich vielleicht weiter bringen.

[Mokkie]

Hallo knobbiee8,

also ich benutze dies ODBC-Datenquelle:

Microsoft SQL Server ODBC-Treiber Version 10.00.20348

Verbindungstests laufen...

Versuch, die Verbindung aufzubauen
Verbindung aufgebaut
Bestätige Optionseinstellungen
Verbindung mit dem Server wird getrennt

TESTS ERFOLGREICH ABGESCHLOSSEN.