Write Conflict

[MDGarcia]

Good day everyone.

I have a System in MSAccces that is giving the message: This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

The objects are in Access but it links via ODBC to Oracle. This app had been used for a loooong time without major problems and suddenly my clients are getting this message sporadically. One of the users has Office 2013 (Win 7), the others 2010 (Win NT). I understand that all of them had this message appeared at some point. I use Office 2013 and haven't been able to replicate the problem because I never get the message.

Searching in forums I have tried these resolutions without success:
1. If Me.Dirty Then
Me.Dirty = False
End If
[run your SQL update here]

2. Click Refresh All in the Records group on the Home tab.

3. Set the RecordLocks property of the form to Edited Record.

Any suggestions?

Thanks.

 

[Andrzejek]

Do you have any record locking in your logic?
Can 2 or more users access the same record in Oracle's table for update?

Have fun.

---- Andy

 

[MDGarcia]

Thanks for your response Andy.

No record locking at the logic or DB level, so it is possible that 2 people access the same record. Even if 2 people can access the same record, only one user was working with the record at the time of the error and other users couldn't been on other records. Like I said before, they're been using this for quite some time and never this error occurred.

 

[dhookom]

You might get this type of message with MS SQL Server if there is a yes/no/bit field in the linked table that doesn't have a default value.

Duane
Hook'D on Access
MS Access MVP

 

[MDGarcia]

Thanks dhookom.

The app connects to an Oracle DB via ODBC. I checked all the fields that uses a check mark to display the value. These fields are defined as NUMBER and they assume a 0 value by default, -1 when checked.

 

[dhookom]

Do you have any code you can share? Are you opening any bound forms while other forms are open?

Duane
Hook'D on Access
MS Access MVP

 

[MDGarcia]

I'm pretty sure the form I'm opening is unbound(Access default). The only form open in the background is the Menu form which is unbound too.
--On form open
Private Sub Form_Open(Cancel As Integer)
Me.PerKey.Locked = True ====>this is the record key field
End Sub

--On form close
Private Sub Form_Close()
Me.PerKey.Locked = False
End Sub

--When searching for a record
Private Sub cmdFindPer_Click()
On Error GoTo Err_cmdFindPer_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_cmdFindPer_Click:
Exit Sub
Err_cmdFindPer_Click:
MsgBox Err.Description
Resume Exit_cmdFindPer_Click
End Sub

--When saving changes (and where error message occurs)
Private Sub cmdSavRec_Click()
On Error GoTo Err_cmdSavRec_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_cmdSavRec_Click:
Exit Sub
Err_cmdSavRec_Click:
MsgBox Err.Description
Resume Exit_cmdSavRec_Click
End Sub

 

[MDGarcia]

Hi.
I compact/repair the mdb and re-link all the tables to the Oracle DB. I asked my client to test it out and inform me if the issue is fixed.

 

[MDGarcia]

Hi.

Haven't been able to resolve the issue. Like I said above the user who entered the data uses MS Access 2013 on a Win 7 PC. All other users has MS Access 2010 running on NT, one in Win 7.

I have tried everything I found on different forums without success. Changing equipment to one with Win 7 apparently isn't an alternative as this moment.

Thanks.

 

[missinglinq]

Your app is split, with an Oracle db as the Back End, but does each user have a copy of the Front End on their PC, or are they sharing a single copy of the Front End that resides on a shared drive?

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[dhookom]

If "the form I'm opening is unbound" it would suggest the Record Source property of the form is blank. Also the controls would not have anything in their Control Source property.

Duane
Hook'D on Access
MS Access MVP

 

[MDGarcia]

Thanks for your responses.

missinglinq: the users have a copy on each PC connecting via ODBC to the Oracle DB. As I remember, it is done this way for better performance. Server can be slow @ times.

dhookom: I'll be checking the properties.

Regards.

 

[addy]

I'm not sure if this works for Oracle but I had this issue in the past with linking via ODBC to SQL tables and if they contained a bit field (if I remember correctly) then one solution was to add a timestamp data field to the relevant SQL table.

This always worked for my issues - not sure if Oracle uses timestamp fields or not (I have virtually no experience of Oracle as you can probably tell!)

 

[MDGarcia]

Thanks addy.

None of my fields are bit type. I have about what you're writing about.

Regards.

 

[dhookom]

I still think your form is bound since you have code to save the record. Records in bound forms are saved automatically so you rarely need code to save the records.

Duane
Hook'D on Access
MS Access MVP

 

[MDGarcia]

Thanks dhookom.

Good point. Gave me an idea.

I don't remember exactly why the save button is there but It was to resolve another issue.

Thanks.

 

[dhookom]

MDGarcia,
You still haven't told us if your form has a record source and your controls have control source values.

Duane
Hook'D on Access
MS Access MVP

 

[MDGarcia]

dhookom:

It’s a bound form. The record source is the Oracle table that is accessed via ODBC. The control source are the fields of the table. This situation was never reported until some of the clients started using the mdb in an Access 2013 environment while others still use 2010.

Regards.

 

[dhookom]

I would make sure there is no code that is editing the records and use Me.Dirty = False to save the record. You can also rely on Access to save the record which is the default behavior in most of my bound forms.

Duane
Hook'D on Access
MS Access MVP

 

[MDGarcia]

Hi dhookom.

Already done both. Since this doesn't happen with every record, just a group that was entered by an Access 2013 user, I suspect something happened while creating the records.