Hello, I've got a small multi-user program which is generating intermittent (yet consistent) problems. Our developer is no longer available to assist, so I'm seeking some input from the group here (it's been more than a few years since I did some coding).
A few times/day we get into a cycle where "Error 3218; Could not update; currently locked" error appears. This is sometimes followed by error 2105 (Can't go to specified record). I think I've narrowed the problem line down to the CreateQueryDef line, where the insert into does not occur (as the Production Quality database does not get a new line, but rather it wants to overwrite an existing line)
Does anything jump out as a problem in the snippet?
Thanks,
Darren
Function GetNextQCRecord(w As Variant, k As Variant, c As Single, pqty As Variant) As Long
Dim myrs As Recordset
Dim mydef As QueryDef
Dim q As Long
If IsNull(w) Or IsNull(k) Then
GetNextQCRecord = 0
Exit Function
End If
init
Set myrs = Db.OpenRecordset("SELECT Max(ProductionQuality.QID) AS MaxOfQID FROM ProductionQuality;", dbOpenSnapshot)
If myrs.RecordCount = 0 Then
q = 1
Else
q = myrs!MaxOfQID + 1
End If
myrs.Close
If c = 0 Then
Set mydef = Db.CreateQueryDef("", "PARAMETERS [p].[QID] Long, [p].[Key] Long, [p].[WONo] Long, [p].[EntryDate] DateTime, [p].[WorkDate] DateTime; INSERT INTO ProductionQuality ( QID, [Key], WONo, EntryDate, WorkDate ) SELECT [p].[QID] AS NewQID, [p].[Key] AS NewKey, [p].[WONo] AS NewWONo, [p].[EntryDate] AS NewEntryDate, [p].[WorkDate] AS NewWorkDate;")
Else
Set mydef = Db.CreateQueryDef("", "PARAMETERS [p].[QID] Long, [p].[Key] Long, [p].[WONo] Long, [p].[EntryDate] DateTime, [p].[WorkDate] DateTime, [p].[CoilNo] IEEESingle, [p].[ProdQty] Long; INSERT INTO ProductionQuality ( QID, [Key], WONo, EntryDate, WorkDate, CoilNo, ProdQty ) SELECT p.QID AS NewQID, p.Key AS NewKey, p.WONo AS NewWONo, p.EntryDate AS NewEntryDate, p.WorkDate AS NewWorkDate, [p].[CoilNo] AS NewCoilNo, [p].[ProdQty] AS NewProdQty;")
mydef.Parameters("[p].[CoilNo]") = c
mydef.Parameters("[p].[ProdQty]") = pqty
End If
mydef.Parameters("[p].[QID]") = q
mydef.Parameters("[p].[Key]") = k
mydef.Parameters("[p].[WONo]") = w
mydef.Parameters("[p].[EntryDate]") = Format(Date, "mmm-d-yy")
mydef.Parameters("[p].[WorkDate]") = Format(Date, "mmm-d-yy")
mydef.Execute
mydef.Close
GetNextQCRecord = q
A few times/day we get into a cycle where "Error 3218; Could not update; currently locked" error appears. This is sometimes followed by error 2105 (Can't go to specified record). I think I've narrowed the problem line down to the CreateQueryDef line, where the insert into does not occur (as the Production Quality database does not get a new line, but rather it wants to overwrite an existing line)
Does anything jump out as a problem in the snippet?
Thanks,
Darren
Function GetNextQCRecord(w As Variant, k As Variant, c As Single, pqty As Variant) As Long
Dim myrs As Recordset
Dim mydef As QueryDef
Dim q As Long
If IsNull(w) Or IsNull(k) Then
GetNextQCRecord = 0
Exit Function
End If
init
Set myrs = Db.OpenRecordset("SELECT Max(ProductionQuality.QID) AS MaxOfQID FROM ProductionQuality;", dbOpenSnapshot)
If myrs.RecordCount = 0 Then
q = 1
Else
q = myrs!MaxOfQID + 1
End If
myrs.Close
If c = 0 Then
Set mydef = Db.CreateQueryDef("", "PARAMETERS [p].[QID] Long, [p].[Key] Long, [p].[WONo] Long, [p].[EntryDate] DateTime, [p].[WorkDate] DateTime; INSERT INTO ProductionQuality ( QID, [Key], WONo, EntryDate, WorkDate ) SELECT [p].[QID] AS NewQID, [p].[Key] AS NewKey, [p].[WONo] AS NewWONo, [p].[EntryDate] AS NewEntryDate, [p].[WorkDate] AS NewWorkDate;")
Else
Set mydef = Db.CreateQueryDef("", "PARAMETERS [p].[QID] Long, [p].[Key] Long, [p].[WONo] Long, [p].[EntryDate] DateTime, [p].[WorkDate] DateTime, [p].[CoilNo] IEEESingle, [p].[ProdQty] Long; INSERT INTO ProductionQuality ( QID, [Key], WONo, EntryDate, WorkDate, CoilNo, ProdQty ) SELECT p.QID AS NewQID, p.Key AS NewKey, p.WONo AS NewWONo, p.EntryDate AS NewEntryDate, p.WorkDate AS NewWorkDate, [p].[CoilNo] AS NewCoilNo, [p].[ProdQty] AS NewProdQty;")
mydef.Parameters("[p].[CoilNo]") = c
mydef.Parameters("[p].[ProdQty]") = pqty
End If
mydef.Parameters("[p].[QID]") = q
mydef.Parameters("[p].[Key]") = k
mydef.Parameters("[p].[WONo]") = w
mydef.Parameters("[p].[EntryDate]") = Format(Date, "mmm-d-yy")
mydef.Parameters("[p].[WorkDate]") = Format(Date, "mmm-d-yy")
mydef.Execute
mydef.Close
GetNextQCRecord = q
