July 9, 2003 at 8:51 am
Hello,
I received a Write Conflict error. I believe it has something to do with a record being opened for a length of time than the record is being updated, because I am the only user updating the record.
I am using ACCESS 2000 as FE and SQL SERVER 7.0 as BE.
I am executing two stored procedures within a procedure and then requery the subform that is when the error occurred.
Thanks in advance for your assistance!
Here is my code:
Sub save_shipped_input()
Dim save_shipping_history As New ADODB.Command
Dim clear_temp_fields As New ADODB.Command
Me!shipping_sched_list_subform.Form!cust_ord_qty.Locked = False
' sp adds and deletes a record if an error occurred rollback trans
With save_shipping_history
.ActiveConnection = CurrentProject.Connection
.CommandText = "spUpdate_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@t1", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@t2", adInteger, adParamOutput)
.Execute , , adExecuteNoRecords
End With
‘sp clears some fields
With clear_temp_fields
.ActiveConnection = CurrentProject.Connection
.CommandText = "spClear_temp_shipping_fields"
.Execute , , adExecuteNoRecords
End With
‘if the return value is greater than one an error has occurred
If save_shipping_history.Parameters("ret_val").Value > 0 Then
If Me!shipping_sched_list_subform.Form!shipped_qty_temp <> "" Then
Me!shipping_sched_list_subform.Form!shipped_qty_remaining = Me!shipping_sched_list_subform.Form!shipped_qty_temp
MsgBox "There is an error occurred in the record you have entered. Please write down the work order number and contact the IT Department.", vbInformation
End If
Me!shipping_sched_list_subform.Form!shipment_complete = False
End If
‘Write conflict occurred in the subform
Me!shipping_sched_list_subform.Requery
Call enable_disable_form(True)
Call prep_sched_input
Me!input_shipped_qtys.Caption = "Enter Shipped &Qty's"
Set save_shipping_history = Nothing
Set clear_temp_fields = Nothing
End Sub
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim sq As Long, sqrm As Long, sqt As Long, update_qty_remaining As Long
If Not IsNull(Me!shipped_qty) Then
If IsNull(Me!shipped_date) Then
MsgBox "You entered a shipped quantity but did enter date it shipped on.", vbExclamation
Me!shipped_date.SetFocus
Cancel = True
Exit Sub
End If
Else
Me!shipped_date = Null
Me!reason_code = Null
Me!shipment_complete = False
End If
If update_qty_remaining = True Then
If IsNull(Me!shipped_qty) Then
sq = 0
Else
sq = Me!shipped_qty
End If
If IsNull(Me!shipped_qty_remaining) Then
sqrm = 0
Else
sqrm = Me!shipped_qty_remaining
End If
If IsNull(Me!shipped_qty_temp) Then
sqt = 0
Else
sqt = Me!shipped_qty_temp
End If
Me!shipped_qty_remaining = sqrm - (sq - sqt)
Me!shipped_qty_temp.Value = Me!shipped_qty.Value
update_qty_remaining = False
End If
'write conflict occurred after executing the next calling sub routine
Call tts_reason_code
End Sub
‘’’’’’’’’’’’’’’’’’’’’’’’’
Sub tts_reason_code()
If Me!shipped_date.Value > Me!mfg_ord_due.Value Then
Me!reason_code.TabStop = True
Else
Me!reason_code.TabStop = False
code.Value = ""
End If
End Sub
July 9, 2003 at 8:56 am
Hi alicejwz,
ahem, sorry maybe I am missing something, but what is the error message you get?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 9, 2003 at 11:42 pm
Hi alicejwz,
from what you have sent me off-forum, could it be you have a bit field in one of your tables without a default value? That's working in Access, but not in SQL Server.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 10, 2003 at 3:25 am
quote:
Hi alicejwz,from what you have sent me off-forum, could it be you have a bit field in one of your tables without a default value? That's working in Access, but not in SQL Server.
Cheers,
Frank
It is possible to store NULL in bit field in SQL2000, but Access doesn't like that, and if there is a bit field without default in a database, it is a very likely cause for Write Conflict. Other causes can be more misterius, perhaps linked to missing ServicePack or so.
Hope that helps
PS: Access also doesn't like after-triggers on insert into table.
July 10, 2003 at 6:28 am
I had a similar Access2K/SQL2K problem. I resolved it by adding a column of datatype timestamp to the tables. Even though my app doesn't use it, it stopped the write conflicts.
bb
Edited by - billyburns on 07/10/2003 06:29:30 AM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply