Accessing data in sql server without a record lock

Can you post your code please.

Txt_order is a textbox with the order num to test.
TmpImprim is a local variable that will stock the latest value of the field and negate it (the underlying field is a boolean).

V_DOCENTETE_PHI is a read only view. I created it using this  code I found online as an example :
create view  table_view
as
select * from table_name
union all
select * from table_name where 1=0

this code will only try to switch the value of DO_Imprim from false to true or from true to false.

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("SQL_passthrough_select")
    qdf.SQL = "select DO_Imprim from V_DOCENTETE_PHI where DO_Piece='" & Txt_order.Value & "'"
    Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    qdf.Close
    Set qdf = Nothing
    If rs.RecordCount > 0 Then
        If rs(0) = 0 Then tmpImprim = 1 Else tmpImprim = 0
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
        DoEvents
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("SQL_passthrough_update")
        qdf.SQL = "UPDATE F_DOCENTETE SET F_DOCENTETE.DO_Imprim = " & tmpImprim & _
            " WHERE DO_Piece=' " & Txt_order.Value & "'"
        qdf.Execute
        qdf.Close
        Set qdf = Nothing
end if

Select allOpen in new window

This code won't work and doesn't update the value. When I remove the first query and don't open the recordset on that line before, I can update the value.

My guess as to why you have a problem is this:

1. You open db.
2. You open qdf.
3. You open rs, based on qdf.
4. You close and set qdf to Nothing (but rs is still open?).
5. You access rs (which is based on qdf, which is now closed).
6. You may or may not close rs, depending on whether the IF statement is true.
7. You may or may not close db, depending on whether the IF statement is true.

Things are the wrong way wrong. Close and set items to Nothing from the inside out, i.e. close rs before qdf, and then db. And make sure they are closed, regardless of the results of the IF statement.

That is not the problem. I put a value that will always give a result in the txtorder textbox. Beside, my problem is that when I am in this If statement, I close the recordset and try to update. the update doesn't work. The update is also in the IF statement. So if the If statement is not true there is no update and no problem.

Just to be complete here is the code with the close in the else

Set db = CurrentDb()
    Set qdf = db.QueryDefs("SQL_passthrough_select")
    qdf.SQL = "select DO_Imprim from V_DOCENTETE_PHI where DO_Piece='" & Txt_order.Value & "'"
    Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    qdf.Close
    Set qdf = Nothing
    If rs.RecordCount > 0 Then
        If rs(0) = 0 Then tmpImprim = 1 Else tmpImprim = 0
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
        DoEvents
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("SQL_passthrough_update")
        qdf.SQL = "UPDATE F_DOCENTETE SET F_DOCENTETE.DO_Imprim = " & tmpImprim & _
            " WHERE DO_Piece=' " & Txt_order.Value & "'"
        qdf.Execute
        qdf.Close
        Set qdf = Nothing
else
        rs.close
        set rs=nothing
end if

Select allOpen in new window

Should I only close QDF after I am done with the linked recordset? It doesn't seem to make a problem but I will modify my code and see if it changes anything.

Updated version :

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("SQL_passthrough_select")
    qdf.SQL = "select DO_Imprim from V_DOCENTETE_PHI where DO_Piece='" & Txt_order.Value & "'"
    Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
    If rs.RecordCount > 0 Then
        If rs(0) = 0 Then tmpImprim = 1 Else tmpImprim = 0
        rs.Close
        Set rs = Nothing
        qdf.Close
        Set qdf = Nothing
        db.Close
        Set db = Nothing
        DoEvents
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("SQL_passthrough_update")
        qdf.SQL = "UPDATE F_DOCENTETE SET F_DOCENTETE.DO_Imprim = " & tmpImprim & _
            " WHERE DO_Piece=' " & Txt_order.Value & "'"
        qdf.Execute
        qdf.Close
        Set qdf = Nothing
        
        Set qdf = db.QueryDefs("SQL_passthrough_select")
        qdf.SQL = "select DO_Imprim from V_DOCENTETE_PHI where DO_Piece='" & Txt_order.Value & "'"
        Set rs = qdf.OpenRecordset
        qdf.Close
        Set qdf = Nothing
    Else
        MsgBox "This order does not exist"
        launched = False
        qdf.Close
        Set qdf = Nothing
        rs.Close
        Set rs = Nothing
    End If

Select allOpen in new window

I still have the same problem. Also, as before, when I try to update the value without opening the first recordset then it can be updated. It only stops working when I open the line before. So it really is a record lock probably.

I can use a stored procedure to call the data I need without locking the records? What I need to do is a select without locking the data, and from what I remember from the classes 10 years ago stored procedure are more like programs that you call.

I won't worry about SQL injection because this is a tool that I am using for myself, that is on my computer only. It won't be used by anyone else. Also we are a small company and I'm pretty sure nobody knows what SQL injection is except me. Maybe my boss knows too but He wouldn't use it.

Thanks for the link. I'll try to look at this today if I have the time.

I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.

I think my answer was sufficient to answer the question.

The question was how to prevent locks, and I have suggested several ways to prevent the locks.

If vanroybel thinks that my answer was not sufficient to answer his question, then he should post such.

Thanks for your answers Phillip, I was away from work as I was sick last week.
Your answer was sufficient for the problem, I now need a bit of time to put everything in stored procedure and see if my problem is resolved.