Microsoft KB Archive/295169

= ACC2002: Write Conflicts Are Not Reported in Text or Image Data Type Fields in a Microsoft Access Project =

Article ID: 295169

Article Last Modified on 9/26/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q295169



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
When two users change the same record at the same time in a Microsoft Access project or in a Microsoft Access database connected to SQL Server, a write conflict message is not received if the data type of the field is Text or Image.



RESOLUTION
Create custom code that uses form events to check for write conflicts and to post a warning. To do so, follow these steps.  Start Microsoft Access. On the Help menu, point to Sample Databases, and then click Northwind Sample Access Project. Minimize this instance of Access. Open a second instance of Access. Create a new project named Nwind2.adp connected to NorthwindCS.adp. Return to NorthwindCS.adp.</li> Open the Categories form in Design view.</li> On the View menu, click Code.</li>  Type or paste the following code: Dim strTemp As String

Private Sub Form_BeforeUpdate(Cancel As Integer) On Error GoTo MyForm_Err Dim cnn As ADODB.Connection Dim MyRS As New ADODB.Recordset Dim strOld As String Dim iAns As Integer

Set cnn = CurrentProject.Connection MyRS.Open &quot;select Categories.Description from Categories where CategoryID =&quot; & Me.CategoryID, cnn, adOpenStatic

strOld = MyRS!Description

If Me.Description <> strTemp Then

iAns = MsgBox(&quot;The record was changed by another user. Current record is: &quot; & vbCr & vbCr & strOld _    & vbCr & vbNewLine & &quot;Your changes to record are: &quot; & vbCr & vbCr & Me.Description & vbCrLf _     & vbNewLine & &quot;Do you wish to overwrite the current Record with the changes you made?&quot; _     & vbCr & &quot;OK to keep your changes or Cancel to keep current record.&quot;, _      vbOKCancel, &quot;Write Conflict&quot;) If iAns = 2 Then Me.Description = strOld End If  End If

MyRS.Close Set MyRS = Nothing Set cnn = Nothing MyForm_Exit: Exit Sub

MyForm_Err: Resume MyForm_Exit End Sub

Private Sub Form_Current If IsNull(Me.Description) Then

Else strTemp = Me.Description End If End Sub </li> Save the form, and then view the form in Form view.</li> Return to Nwind2.adp.</li> Open the Categories table and edit the Description field in the first record. Change the record to &quot;Soft drinks, coffees, teas, beers, and ales old.&quot;</li> Move to the second record.</li> Return to NorthwindCS.adp</li> Edit the Description field in the first record. Change the record to &quot;Soft drinks, coffees, teas, beers, and ales new.&quot;</li> Move to the second record.</li> Note that you receive the following message:

The record was changed by another user. Current record is:

Soft drinks, coffees, teas, beers, and ales old

Your changes to the record are:

Soft drinks, coffees, teas, beers, and ales new

Do you wish to overwrite the current Record with the changes you made?

OK to keep your changes or Cancel to keep current record.

</li> Click OK to keep your changes, or click Cancel to keep the current record.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

Steps to Reproduce the Behavior

 * 1) Start Microsoft Access.
 * 2) On the Help menu, point to Sample Databases, and then click Northwind Sample Access Project.
 * 3) Minimize this instance of Access.
 * 4) Open a second instance of Access.
 * 5) Create a new project named Nwind2.adp connected to NorthwindCS.adp.
 * 6) Open the Categories table.
 * 7) Return to NorthwindCS.adp, open the Categories table, and edit the Description field in the first record. Change the record to &quot;Soft drinks, coffees, teas, beers, and ales old.&quot;
 * 8) Move to the second record.
 * 9) Return to Nwind2.adp.
 * 10) Edit the Description field in the first record. Change the record to &quot;Soft drinks, coffees, teas, beers, and ales new.&quot;
 * 11) Move to the second record.

Note that you do not receive a write conflict message.
 * 1) Return to NorthwindCS.adp
 * 2) On the Records menu, click Refresh.

Note that the record reads &quot;Soft drinks, coffees, teas, beers, and ales new.&quot;

Additional query words: pra

Keywords: kbbug KB295169

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.