If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nolocks not honored
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 |
#2
|
|||
|
|||
Nolocks not honored
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. -- HTH Van T. Dinh MVP (Access) "Sammy" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|