Record update fails due to locking

[morsagmon]

Hello.

In the below code, the rst.Update method fails with the following error:

Run-time error '3197':
The [COLOR=blue ! important][COLOR=blue ! important]Microsoft[/COLOR][/COLOR] Office Access [COLOR=blue ! important][COLOR=blue ! important]database [COLOR=blue ! important]engine[/COLOR][/COLOR][/COLOR] stopped the process
because you and another user are attempting to change the same
data at the same time.

I restarted the DB and Access machine - I'm sure there's no other process addressing this table, except for this subroutine!

Any ideas?

Public Sub UpdateSupplementsStock()
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strQuery As String
  Dim strSql As String
  Dim SuppStock As SupplementStock

  Set dbs = CurrentDb()
  strQuery = "SELECT tblFood_supplements.Supplement_Code, tblFood_supplements.Units_in_Stock, tblFood_supplements.Last_Stock_Take_Date FROM tblFood_supplements"
  
  ' Open recordset on the query
  Set rst = CurrentDb.OpenRecordset(strQuery, dbOpenDynaset)
  Do While Not rst.EOF
    SuppStock = SumSupplementStock(rst![Supplement_Code])
    rst.Edit
    rst![Units_in_Stock] = SuppStock.UnitsInStock
    rst![Last_Stock_Take_Date] = SuppStock.StockDate
    rst.Update
    rst.MoveNext
  Loop
  
  rst.Close
  dbs.Close
End Sub

 

[MSAccessRookie]

Hello.

In the below code, the rst.Update method fails with the following error:


I restarted the DB and Access machine - I'm sure there's no other process addressing this table, except for this subroutine!

Any ideas?

Public Sub UpdateSupplementsStock()
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strQuery As String
  Dim strSql As String
  Dim SuppStock As [COLOR=purple][B]SupplementStock[/B][/COLOR]
 
  Set dbs = CurrentDb()
  strQuery = "SELECT tblFood_supplements.Supplement_Code, tblFood_supplements.Units_in_Stock, tblFood_supplements.Last_Stock_Take_Date FROM tblFood_supplements"
 
  ' Open recordset on the query
  Set rst = CurrentDb.OpenRecordset(strQuery, dbOpenDynaset)
  Do While Not rst.EOF
    SuppStock = [COLOR=red][B]SumSupplementStock[/B][/COLOR](rst![Supplement_Code])
    rst.Edit
    rst![Units_in_Stock] = SuppStock.UnitsInStock
    rst![Last_Stock_Take_Date] = SuppStock.StockDate
    rst.Update
    rst.MoveNext
  Loop
 
  rst.Close
  dbs.Close
End Sub

It appears that there has to be at least a little more to it that just the above subroutine. For instance:

• SumSupplementStock (which is not included), appears to be a Function that returns a response the datatype SupplementStock and the definition for the datatype SupplementStock has not been included.

Is there anything else that you can add to the picture?

 

[morsagmon]

Rookie,

The SupplementStock data type:

Public Type SupplementStock
    UnitsInStock As Integer
    StockDate As Date
End Type

The called SumSupplementStock function:

Public Function SumSupplementStock(strSupplement As String) As SupplementStock
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strQuery As String
  Dim strSql As String
  
  Dim intUnits As Integer
  Dim dateStockDate As Date
    
  Set dbs = CurrentDb()
  strQuery = "SELECT * FROM tblProducts WHERE tblProducts.Supplement_Code = '" & strSupplement & "'"
  
  intUnits = 0
  
  ' Open recordset on the query
  Set rst = CurrentDb.OpenRecordset(strQuery, dbOpenSnapshot)
  Do While Not rst.EOF
    intUnits = intUnits + (rst![Bottles_in_Stock] * rst![Number_of_Units])
    dateStockDate = rst![Stock_Date]
    rst.MoveNext
  Loop
  
  SumSupplementStock.UnitsInStock = intUnits
  SumSupplementStock.StockDate = dateStockDate
  
  rst.Close
  dbs.Close

End Function

Thanks!

 

[boblarson]

Are you perhaps running this code from a bound form which is bound to one of the tables in the query? That is a common mistake and will give you the error you are getting.

 

[morsagmon]

Bob,

No, the form I'm calling this sub from is bound to a different table.

Thanks!