SQL Update Records Failed

W

waseem0888

Member
Local time
Today, 00:29
Joined
Jul 25, 2020
Messages
51
Hi,

I have linked Access frontend to SQL server backend through ODBC DSN file my table is having primary key and I am able to update record directly into the table without any issue but when I am trying to update in VBA I am getting below message.

Runtime error 3157: ODBC Update on a linked table " Table Name" failed -
[Microsoft][ODBC SQL Server Driver]Timeout expired(#0)

I have tried below different ways to update records but got the same error.

1st
Dim strSQL As String
strSQL = "UPDATE RFIA_Register SET Current = 'No' WHERE ID =" & Me.draftNo & ""
Debug.Print strSQL
CurrentDb.Execute strSQL, dbSeeChanges

2nd
Dim strSQL As String
strSQL = "UPDATE RFIA_Register SET Current = 'No' WHERE ID =" & Me.draftNo & ""
DoCmd.RunSQL strSQL

3rd

Dim db As DAO.Database, rec As DAO.Recordset
Dim strSQL As String
strQruery = "SELECT Current FROM RFIA_Register WHERE ID=" & Me.draftNo & ""
Set db = CurrentDb()
Set rec = db.OpenRecordset(strQruery, dbOpenDynaset, dbSeeChanges)
With rec
.MoveFirst
Do
rec.Edit
rec!Current = "No"
rec.Update
.MoveNext
Loop Until .EOF
.Close
End With
db.Close
Set rec = Nothing
Set db = Nothing
 
waseem0888 said:
I am able to update record directly into the table without any issue
Click to expand...
Are you updating the table in Access here or via another method like SSMS?

I have linked Access frontend to SQL server backend through ODBC DSN
Click to expand...
What driver are you using? Can you post your connection string?
 
I am updating the linked tables in Access from the SQL server backend.

I have used SQL Server driver in to set up a data source in ODBC it gives an error so then I relink my tables in ODBC Driver 17 for SQL Server. Both drivers have the same issue.
1647867617698.png
 
What happens if you simply run the SQL code in the query editor?
You might get a more meaningful error message?
 
waseem0888 said:
Runtime error 3157: ODBC Update on a linked table " Table Name" failed -
[Microsoft][ODBC SQL Server Driver]Timeout expired(#0)
Click to expand...
My bet: This is a locking issue. The record you try to update is locked by another process.
You can run the following SQL directly on SQL Server to check whether I'm right:
SQL: 
SELECT *
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id = OBJECT_ID(N'RFIA_Register');
 
Minty said:
What happens if you simply run the SQL code in the query editor?
You might get a more meaningful error message?
Click to expand...
Sir do you mean query editor in Access or Sql server i have tried both i am able to update the record. i tried in access update query and record updated.

In SQL server
1647924841404.png


In Access
UPDATE RFIA_Register SET RFIA_Register.[Current] = "No"
WHERE (((RFIA_Register.ID)=9540));
 
sonic8 said:
My bet: This is a locking issue. The record you try to update is locked by another process.
You can run the following SQL directly on SQL Server to check whether I'm right:
SQL: 
SELECT *
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id = OBJECT_ID(N'RFIA_Register');
Click to expand...
Sir do you mean here in SQL server below is the result.

1647924961176.png
 
waseem0888 said:
Sir do you mean here in SQL server below is the result.
Click to expand...
Yes, that's what I meant, and the result looks like I was wrong.
However, to get a meaningful result, you must run that statement immediately before and after your update statement failed. - Only if there are no locks right then, it proves I'm wrong.
 
Please someone can check my code and correct where I am wrong it's giving me the below error

Run-time error '3129'"
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SElECT', or 'UPDATE'.

My Code

Private Sub btnWIR_Save_Click()
On Error GoTo Save_Click_Error
Dim strSQLNew As String
Dim strSQLUpdate As String
Dim strSQLDraft As String
Validation
If ValValue <> 1 Then
Exit Sub
Else
If MsgBox("Do you want to Save: WIR-" + Me.Series, vbYesNo + vbExclamation, "Confirmation!") = vbYes Then
getSeries

If FormType = "createWIR" Or FormType = "reviseWIR" Then
DoCmd.SetWarnings (False)
strSQLNew = "INSERT INTO RFIA_Register(Primary_Attribute,Series,Revision,Document_Number,Description,WIR_Title,BOQ_No,Submitted_Date," & _
"Status,Drawing_Number,Submitted_By,Location,Quantity,Specification_Ref,Inspection_Date_Time,Discipline,Co_Ordination," & _
"Current,Submission,UserID,Time_Stamp)" & _
" VALUES('" & Me.PA & "', '" & Me.Series & "', '" & Me.Revision & "', '" & Me.Document_Number & "', '" & Me.Description & "', '" & Me.WIR_Title & "', '" & Me.BOQ_No & "', '" & Me.Submitted_Date & "', " & _
" 'UR','" & Me.Drawing_Number & "','" & Me.Submitted_By & "','" & Me.Location & "','" & Me.Quantity & "','" & Me.Specification_Ref & "', '" & _
Me.Inspection_Date_Time & "', '" & Me.Discipline & "', '" & Me.Co_Ordination & "','Yes','Pending','" & GUID & "', '" & Now & "')"

If FormType = "reviseWIR" Then
'update current status for old record
strSQLUpdate = "UPDATE RFIA_Register SET [Current] = 'No' WHERE ID= " & Me.draftNo & ""
End If
End If
ElseIf FormType = "draftWIR" Then
strSQLDraft = "UPDATE RFIA_Register SET Description='" & Me.Description & "',WIR_Title='" & Me.WIR_Title & "', Primary_Attribute='" & Me.PA & "', Document_Number='" & Me.Document_Number & "', Series='" & Me.Series & "', Location='" & Me.Location & _
"', Revision='" & Me.Revision & "', BOQ_No='" & Me.BOQ_No & "', Submitted_Date='" & Me.Submitted_Date & "', Status='UR', Inspection_Date_Time='" & Me.Inspection_Date_Time & "', Submitted_By='" & Me.Submitted_By & "', Drawing_Number='" & Me.Drawing_Number & _
"', Current='Yes', Submission='Pending', Discipline='" & Me.Discipline & "', Co_Ordination='" & Me.Co_Ordination & "', Comments='', UserID='" & GUID & "', Time_Stamp='" & Now() & "' WHERE ID=" & draftNo & ";"
End If

If MsgBox("WIR-" & Me.Series & "Created ...!" & vbNewLine & " Do you want to print this WIR?", vbYesNo, "WIR Created Successfully...!") = vbYes Then
DocNumber = "" 'set this value in print Report
DoCmd.OpenForm "frmPrintBox", acNormal, , , acFormEdit, acDialog
Else
DocNumber = ""
End If


Debug.Print strSQLNew, strSQLUpdate, strSQLDraft
DoCmd.RunSQL strSQLNew
DoCmd.RunSQL strSQLUpdate
DoCmd.RunSQL strSQLDraft
DoCmd.Close acForm, "frmWIR", acSaveNo
End If
Save_Click_Error: If Err.Number > 0 Then MsgBox Err.Number & Err.Description
End Sub




When i just use one line to RunSQL command like only DoCmd.RunSQL strSQLNew my code is working without error but even its gives error its inserts records but shows the above error. Can someone tell me if i am missing something in If-else statements or where to use DoCmd.RunSQL lines.
 
1.) The DateTime values in your SQL Statements have a huge potential of causing errors. See the "SQL Strings with Date Values" section in my my VBA SQL String Tutorial.

2.) You always execute all of your SQL statements (strSQLNew, strSQLUpdate, strSQLDraft) but some of them might not be set to a valid SQL statement depending on the IF evaluation of FormType. - This is most likely what is causing the error message you are seeing.

What happened to the entirely different problem you started this thread with?
 
To reiterate what @sonic8 said, you only need one strSQL, as you are only ever going to perform any one of these three actions at any time.

Therefore your code could be simplified to;
Code: 
Private Sub btnWIR_Save_Click()
    On Error GoTo Save_Click_Error
    Dim strSQL As String
    
    Validation
    
    If ValValue <> 1 Then Exit Sub
        
    If MsgBox("Do you want to Save: WIR-" + Me.Series, vbYesNo + vbExclamation, "Confirmation!") = vbYes Then
        getSeries
    End If
    
    Select Case FormType
        Case "createWIR", "reviseWIR"
            strSQL = "INSERT INTO RFIA_Register(Primary_Attribute,Series,Revision,Document_Number,Description,WIR_Title,BOQ_No,Submitted_Date," & _
                "Status,Drawing_Number,Submitted_By,Location,Quantity,Specification_Ref,Inspection_Date_Time,Discipline,Co_Ordination," & _
                "Current,Submission,UserID,Time_Stamp)" & _
                " VALUES('" & Me.PA & "', '" & Me.Series & "', '" & Me.Revision & "', '" & Me.Document_Number & "', '" & Me.Description & "', '" & Me.WIR_Title & "', '" & Me.BOQ_No & "', '" & Me.Submitted_Date & "', " & _
                " 'UR','" & Me.Drawing_Number & "','" & Me.Submitted_By & "','" & Me.Location & "','" & Me.Quantity & "','" & Me.Specification_Ref & "', '" & _
                Me.Inspection_Date_Time & "', '" & Me.Discipline & "', '" & Me.Co_Ordination & "','Yes','Pending','" & Guid & "', '" & Now & "')"
            Debug.Print strSQL
            CurrentDb.Execute strSQL
            If FormType = "reviseWIR" Then
                'update current status for old record
                strSQL = "UPDATE RFIA_Register SET [Current] = 'No' WHERE ID= " & Me.draftNo & ""
                Debug.Print strSQL
                CurrentDb.Execute strSQL
            End If
        Case Else
            strSQL = "UPDATE RFIA_Register SET Description='" & Me.Description & "',WIR_Title='" & Me.WIR_Title & "', Primary_Attribute='" & Me.PA & "', Document_Number='" & Me.Document_Number & "', Series='" & Me.Series & "', Location='" & Me.Location & _
                "', Revision='" & Me.Revision & "', BOQ_No='" & Me.BOQ_No & "', Submitted_Date='" & Me.Submitted_Date & "', Status='UR', Inspection_Date_Time='" & Me.Inspection_Date_Time & "', Submitted_By='" & Me.Submitted_By & "', Drawing_Number='" & Me.Drawing_Number & _
                "', Current='Yes', Submission='Pending', Discipline='" & Me.Discipline & "', Co_Ordination='" & Me.Co_Ordination & "', Comments='', UserID='" & Guid & "', Time_Stamp='" & Now() & "' WHERE ID=" & draftNo & ";"
    End Select

    If MsgBox("WIR-" & Me.Series & "Created ...!" & vbNewLine & " Do you want to print this WIR?", vbYesNo, "WIR Created Successfully...!") = vbYes Then
        DocNumber = "" 'set this value in print Report
        DoCmd.OpenForm "frmPrintBox", acNormal, , , acFormEdit, acDialog
    End If

    DoCmd.Close acForm, "frmWIR", acSaveNo
    Exit Sub
    
Save_Click_Error:
    If Err.Number > 0 Then MsgBox Err.Number & Err.Description

End Sub

Please indent your code and use code tags it makes seeing the flow so much easier.
 
You must log in or register to reply here.

Similar threads

K
'Next n' Looping rather than moving to the next record
Replies
16
Views
414
arnelgp
arnelgp
N
How to ensure the open recordset include also any new record entered
Replies
9
Views
242
nector
N
K
strSql errors
2
Replies
26
Views
433
cheekybuddha
cheekybuddha
A
Match two numbers and then update
Replies
10
Views
199
Gasman
Gasman
mloucel
Solved Sql Update Error
Replies
3
Views
147
mloucel
mloucel

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom