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
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:
Zitat-2147217871(80040e31)
Query timeout expired
Ich weiß jetzt nicht weiter und bin für jeden Tipp dankbar.
Grüße Babsi
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
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
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:
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...
ZitatIch habe nun folgendes:
Dann hast du aber Christians Ausführungen zu "As New" bzw. Early-/Late-
Binding nicht gelesen.
Hallo Babsi,
Auch hier verwendest Du wieder "CreateObject. Und rs, obwohl nicht mehr gebraucht.
Beispiel:
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
Hallo Beaker,
nein bestimmt nicht, ich muss erst mal raffen was das ist.
Hallo Christian,
immer noch einen Fehler
ZitatComandtest was not set for Command object
:'(
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.
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
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
Zitatconn.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
@christianZitatBTW: "[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.
Zitat von: Mokkie am Mai 16, 2025, 14:38:48Und 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.
Hallo Babsi,
sorry, war aus dem Kopf, so sollte es gehen:
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
Hallo Christian,
Zitatsorry, 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.
AbfragetiemoutIch 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.
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
Zitat von: Bitsqueezer am Mai 16, 2025, 14:50:29Generell 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 :D.
Zitat von: Mokkie am Mai 16, 2025, 15:10:50Ich 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.
Hallo knobbiee8,
also ich benutze dies ODBC-Datenquelle:
ZitatMicrosoft 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.
Sorry, das war jetzt doppeltHallo markusxy,
ZitatZitat von: Bitsqueezer am Mai 16, 2025, 12:50:29
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
Ist das so gemeint, dass ich eine StoreProcedur auf dem SQL Server ausführen sollte und dieser dann eben die entsprechenden Paramter übergeben sollte?
Hallo Markusxy,
ZitatZitat von: Bitsqueezer am Mai 16, 2025, 12:50:29
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
Ist das so gemeint, dass ich das über eine StoredProcedur auf dem SQL server und in Access nur die entsprechenden Paramter an diese übergeben sollte?
Das ist aber eine Menge Arbeit... ::)
Und es ist eben komisch, das es mal super klappt und dann wieder kommt dieser Fehler.
Ich kenne mich mit dem Acitivity Monitor nicht so gut aus.
Beim Ausführen der Abfrage, und wenn diese hängen bleibt bekomme ich für Abfrae folgende Auswertung:
Logical Read = 3
Elapsed Time = 23070
was sagt mir das? Logical Read ist bei anderen Abfragen höher....
Hallo,
eine Stored Procedure ist auf jeden Fall die sicherste und performanteste Methode, insbesondere bei Änderung von vielen Datensätzen, weil alles auf dem Server stattfindet.
Du kannst hier jeden Parameter vor der Verwendung in jedweder Form überprüfen, Daten in z.B. mehreren Tabellen ändern usw.
Sicherheitstechnisch kann die Stored Procedure (deren Ausführung) an Rechte gebunden werden und per "EXECUTE AS" kann man der SP selbst höhere Rechte geben, als der User hat, der sie ausführt. Somit kann die SP dann z.B. auf Systemabfragen zugreifen, die der User selbst vielleicht nicht abrufen kann, oder Tabellen ändern, die der User direkt nicht ändern kann oder der die Tabelle gar nicht zu sehen bekommt.
Außerdem kannst Du umfangreiche Rückgaben in der SP zur Verfügung stellen, vom Return-Code (immer ein int) bis zu komplexen Abfragen, oder auch OUTPUT-Parameter.
Mit Access kann das alles per ADO abgefragt/ausgeführt werden.
Eine SP zur Datenänderung würde ich aber nicht für jede kleine Tabellenänderung machen, sonst hast Du am Ende einen großen Verwaltungsaufwand. Eine SP empfiehlt sich, wenn es um komplexere Parameter/Tabellenzusammenhänge/Rechte usw. geht.
Allerdings zum Thema SQL Injection: Hier gilt natürlich das gleiche in Sachen Sicherheit: Wenn Du einen Dynamic SQL String in der SP zusammensetzt und mit EXECUTE ausführst, hast Du das gleiche Problem, als ob Du einen SQL-String in VBA zusammensetzt. Also auch hier aufpassen und wenn es geht, Dynamic SQL besser vermeiden.
Gruß
Christian
Hallo,
das besagt, daß die Abfrage 23 Sekunden gebraucht hat (sind immer Millisekunden). Was schon relativ lange ist. Logical Reads:
Hier eine Liste, was die Angaben bedeuten:
https://knowledgebase.apexsql.com/how-to-use-and-interpret-the-sql-server-activity-monitor/
Ich würde erst mal im SQL Profiler schauen bzw. XEvents, was da eigentlich genau passiert.
Aber auch der Activity Monitor sollte Dir schon Angaben zeigen, wo z.B. sich Prozesse blockieren.
Gruß
Christian
Hallo Babsi,
bist du sicher, daß das der aktuelle ODBC-Treiber ist? Und dann solltest du auch einen entsprechenden Connectionstring verwenden:
https://www.connectionstrings.com/microsoft-odbc-driver-17-for-sql-server/ (https://www.connectionstrings.com/microsoft-odbc-driver-17-for-sql-server/)
https://learn.microsoft.com/de-de/sql/integration-services/import-export-data/connect-to-an-odbc-data-source-sql-server-import-and-export-wizard?view=sql-server-ver16 (https://learn.microsoft.com/de-de/sql/integration-services/import-export-data/connect-to-an-odbc-data-source-sql-server-import-and-export-wizard?view=sql-server-ver16) (siehe Option 2)
Gruß Knobbi38
Hallo knobbie38,
oh je, dann muss ich das auch noch mal prüfen, Danke
Hallo Christian,
ZitatEine SP zur Datenänderung würde ich aber nicht für jede kleine Tabellenänderung machen, so
ok, deswegen meine Anmerkung, dass es manchmal nur ein Feld ist, was eben geändert wird. und da hängt es dann, aber eben auch nicht immer :'(
ZitatDu einen SQL-String in VBA zusammensetzt. Also auch hier aufpassen und wenn es geht, Dynamic SQL besser vermeiden.
Ursprünglich hatte das Formular die Tabelle(VOMPANY) als Recordsource. Die Tabelle lässt sich auch öffnen und nachdem ich dieses Constraint(Spalte:Memo) entfernt hatte, auch bearbeiten, wenn man jetzt als Bsp. einfach dieses Memo Fel nimmt.
Der Fehler der dann eben manchmal, nicht immer, auftrat unterscheidet sich nicht zu dem Fehler der nun geworfen wird. Immer Abfragetime .
Hallo Christian,
ZitatIch würde erst mal im SQL Profiler schauen bzw. XEvents, was da eigentlich genau passiert.
Aber auch der Activity Monitor sollte Dir schon Angaben zeigen, wo z.B. sich Prozesse blockieren.
Danke, ich werde mich da jetzt dran machen.
ich melde mich wieder, vielen Dank
Zitat von: Mokkie am Mai 21, 2025, 11:59:19Ist das so gemeint, dass ich das über eine StoredProcedur auf dem SQL server und in Access nur die entsprechenden Paramter an diese übergeben sollte?
Nein, bezüglich SQL Injection hat dynamic SQL keinen Nachteil, es sei denn man macht es so wie gezeigt.
Persönlich verwende ich zu 90% dynamic SQL und zu 10% Prozeduren. Aber ich verwende wie bereits erwähnt auch bei dynamic SQL das Parameter Objekt. Ich käme nie auf die Idee den per UI übergebenen Wert per String Operation zu übergeben. Ich entwickle zwar schon seit Jahren nichts neues mit VBA aber auch bei ADO.Net gilt das selbe Prinzip - nur gibts weit mehr Möglichkeiten.
Grundsätzlich solltest du dich mehr mit der Dokumentation beschäftigen, damit du zumindest die Möglichkeiten und Parameter kennst um dann von Fall zu Fall zu entscheiden, was du nehmen willst. Christian hat da ja schon einige wertvolle Hinweise gegeben. Vor allem muss man sich in einer Multi User Umgebung immer fragen, wie man gleichzeitige Zugriffe handelt.
Hallo markus xy,
ich habe mich am Wochenende eingelesen und das alles geändert auf:
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim lActId As Long
Dim sCUmsatz As Variant ' Variant erlaubt auch Nullwerte
On Error GoTo errormessage
' Werte aus Formular lesen
lActId = Nz(Forms![Hotline -> Terminallist]!COMPANY_ID, 0)
sCUmsatz = Me.COMPANY_DoUmsStat
' Überprüfen
If IsNull(sCUmsatz) Then
sCUmsatz = 0 ' Falls Null, dann False (0)
ElseIf sCUmsatz = True Then
sCUmsatz = 1 ' Falls True, dann 1
Else
sCUmsatz = 0 ' Falls False oder anderweitig, dann 0
End If
' Verbindung aufbauen
Set conn = New ADODB.Connection
With conn
.ConnectionString = "Provider=MSOLEDBSQL;Server=AFC-MSSQL2016V;Database=FENG;Trusted_Connection=yes;"
.Open
End With
' Kommando vorbereiten
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = conn
.CommandType = adCmdText
.CommandTimeout = 60 ' Timeout in Sekunden
' SQL mit Parametern
.CommandText = "UPDATE data.COMPANY SET COMPANY_DoUmsStat = ? WHERE COMPANY_ID = ?"
' Parameter hinzufügen
.Parameters.Append .CreateParameter("COMPANY_DoUmsStat", adBoolean, adParamInput, , sCUmsatz)
.Parameters.Append .CreateParameter("COMPANY_ID", adInteger, adParamInput, , lActId)
' Ausführen
.Execute
End With
' Aufräumen
conn.Close
Set cmd = Nothing
Set conn = Nothing
der Fehler ist immer noch. Ich habe hier auch extra noch mal auf diese ollen Bit Felder egprüft, da ich erfahren habe, dass das wohl oft ein Problem ist.
Auf allen diesen Felder liegt aber ein Contraint :
ALTER TABLE [data].[COMPANY] ADD CONSTRAINT [DF__COMPANY__COMPANY__57F47C82] DEFAULT ((0)) FOR [COMPANY_isINSO]
Die OBDC Verbindung habe ich auch angepasst.
Ich schaue nun wieder serverseitig :-(
Hallo knobbie38,
ich habe die ODBC verknüfung nun korrigiert,
danke für den Hinweis.