Nolocks not honored

[Guest]

I have a access form that has a dropdown box selecting one column its
connected to sql server using odbc. When scroled down halfway for instance if
a user leaves the form in that state without selecting anything a running
process is constantly left on the server. So if another process tries to
enter data in that table it cannot because a running select statement
is left on the server.

Does anyone have an idea how to stop this behaviour?

thanks for any help

Sammy

 

[Van T. Dinh]

Yes, I found that happens to some Tables in my databases (SQL Server 2000
BE)also.

I used ADO Recordset as the RowSource for the ComboBox and it seems to
resolve the problem. Code something like (from one of my databases):

********
Private Sub ProductMicroFilter()
'....

Dim strProdByCodeSQL As String
Dim strProdByDescSQL As String
Dim strCriteriaSQL As String
Dim rsa As ADODB.Recordset

On Error GoTo ProductMicroFilter_Err
With Me
strProdByCodeSQL = "SELECT P.ProductID, P.ProdCode, P.ProdDesc FROM
dbo.tblProduct P WITH (NOLOCK) "
strProdByDescSQL = "SELECT P.ProductID, P.ProdDesc, P.ProdCode FROM
dbo.tblProduct P WITH (NOLOCK) "

' Product Group criteria
Select Case .fraProdGroupID
Case 1 ' PVC Pipes (1, 6)
strCriteriaSQL = " WHERE (P.frg_ProdActiveID = 1) " & _
" AND (P.frg_ProdGroupID In (1, 6)) "
Case 3 ' PE / PP Pipes (3, 7)
strCriteriaSQL = " WHERE (P.frg_ProdActiveID = 1) " & _
" AND (P.frg_ProdGroupID In (3, 7)) "
End Select

' ProdConst criteria
If Not IsNull(.cboProdConstID) Then
strCriteriaSQL = strCriteriaSQL & _
" AND (P.frg_ProdConstID = " & .cboProdConstID & ") "
End If

' ProdAppln Criteria
If Not IsNull(.cboProdApplnID) Then
strCriteriaSQL = strCriteriaSQL & _
" AND (P.frg_ProdApplnID = " & .cboProdApplnID & ") "
End If

' NominalOD criteria
If IsNumeric(.txtNominalOD) Then
strCriteriaSQL = strCriteriaSQL & _
" AND ((P.NominalOD Is Null) Or " & _
" (ABS(Cast(P.NominalOD AS real) - " & .txtNominalOD & ") <= 0.1))
"
End If

' Contruct full SQLs
strProdByCodeSQL = strProdByCodeSQL & strCriteriaSQL & _
" ORDER BY P.ProdCode"
strProdByDescSQL = strProdByDescSQL & strCriteriaSQL & _
" ORDER BY P.ProdDesc"
End With

' Assign Recordset as Rowsource of ComboBox
Set rsa = New ADODB.Recordset
With rsa
.ActiveConnection = fnGetCnnSQL()
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.CacheSize = 16

.Open strProdByCodeSQL, , , , adCmdText
Set Me.cboProductID_Code.Recordset = rsa
.Close

.Open strProdByDescSQL, , , , adCmdText
Set Me.cboProductID_Desc.Recordset = rsa
.Close
End With

ProductMicroFilter_Exit:
On Error Resume Next
Set rsa = Nothing
Exit Sub

ProductMicroFilter_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: Form_frmPipeWeightCalc.ProductMicroFilter)",
_
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume ProductMicroFilter_Exit
End Sub
********

Note the "WITH (NOLOCK)" clause in the SQL String being passed to the SQL
Server.