SQL Update Records Failed (1 Viewer)

waseem0888

Member
Local time
Today, 15:22
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
 

cheekybuddha

AWF VIP
Local time
Today, 15:22
Joined
Jul 21, 2014
Messages
2,237
I am able to update record directly into the table without any issue
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
What driver are you using? Can you post your connection string?
 

waseem0888

Member
Local time
Today, 15:22
Joined
Jul 25, 2020
Messages
51
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
 

Minty

AWF VIP
Local time
Today, 15:22
Joined
Jul 26, 2013
Messages
10,354
What happens if you simply run the SQL code in the query editor?
You might get a more meaningful error message?
 

sonic8

AWF VIP
Local time
Today, 16:22
Joined
Oct 27, 2015
Messages
998
Runtime error 3157: ODBC Update on a linked table " Table Name" failed -
[Microsoft][ODBC SQL Server Driver]Timeout expired(#0)
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');
 

waseem0888

Member
Local time
Today, 15:22
Joined
Jul 25, 2020
Messages
51
What happens if you simply run the SQL code in the query editor?
You might get a more meaningful error message?
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));
 

waseem0888

Member
Local time
Today, 15:22
Joined
Jul 25, 2020
Messages
51
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');
Sir do you mean here in SQL server below is the result.

1647924961176.png
 

sonic8

AWF VIP
Local time
Today, 16:22
Joined
Oct 27, 2015
Messages
998
Sir do you mean here in SQL server below is the result.
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.
 

waseem0888

Member
Local time
Today, 15:22
Joined
Jul 25, 2020
Messages
51
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.
 

sonic8

AWF VIP
Local time
Today, 16:22
Joined
Oct 27, 2015
Messages
998
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?
 

Minty

AWF VIP
Local time
Today, 15:22
Joined
Jul 26, 2013
Messages
10,354
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.
 

Users who are viewing this thread

Top Bottom