Access Write Conflict

Posted on 2010-11-23
Last Modified: 2012-05-10
Suppose I have 3 tables:


Each table is like this



Main-ID is the main table.  I have relationships built:


 relationship details
I have a form that has the query Select * from each table.

If I update the field on the form for Main-ID.IDTag I get two write conflicts for the other tables IDTag.

How can this be avoided?  What do I need to change?
Question by:VTKegan
  • 7
  • 4
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34197910
1. FWIW, you can leave Cascade update on, but you want to leave Cascade Delete OFF.
Only tun this on immediately before you Delete Parent Records, then turn it off immediately after...

I usually NEVER turn cascade delete on.
This is because I want to be alerted if the Parent has child records.
Remember that once you delete Records in Access, they cannot be recovered.

2. Are you quite sure that the One to One relationship is appropriate here.
A one to one relationship should be rare (for Security purposes, ...etc)

Let's see what some other Experts think.

LVL 10

Author Comment

ID: 34197989
i use a 1-1 relationship to compartmentalize the information.  Essentially there is one record and all of the information could be stored in one table, however the information needs to be compartmentalized for tracking (auditing) purposes, as well as controlling user access to certain information.

I took your advice and removed the Cascade Delete Off. Sounds like a good approach.

LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 34198492
I don't agree with turning off Cascade Deletes.  If you do that, you will end up with orphan records in the related tables.  Also, if Referential Integrity is enabled, which it should be, you will be alerted to 'deleting child records'.

As Jeff note, there is 'rarely' justification for a 1:1 relationship.  They become more and more problematic as you get into the design of forms and reports, because you continually have to bring in all the tables.

"use a 1-1 relationship to compartmentalize the information.'
All you need to do this is a 'category' field in the main table.

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 10

Author Comment

ID: 34198539
What do you mean by 'category' field in the main table?

say my record is this.

IDTag : ID1
Drawing: EP601
System: Mission Critical
Building : Building 1
Floor: 3rd
Room: 318
Voltage: 480V
Current: 225A
kVA: N/A

You can see how I have them compartmentalized.  How would your suggestion apply?
LVL 75
ID: 34198732
Is that 480 VDC? If so, I got shocked once by 480 VDC in a lab.  

So what do you mean by this:

" Essentially there is one record and all of the information could be stored in one table,"

"You can see how I have them compartmentalized."
Not exactly.  Really, I just see a bunch of fields with data ....

You have a common IDTag in each table.  Consequently, the 3 tables are 'really' ... just one big table with the fields split up.

However if this:

"as well as controlling user access to certain information."
 ... means certain users should only see part of the info, then this might be justification for 1:1 ... again as Jeff noted ('security').  However, that's really only going to be useful if ... you implement User Level Security on those tables, and ULS is no longer supported as of A2007 ... in the new format ACCDB.

LVL 10

Author Comment

ID: 34198839
480V AC not DC.


""You can see how I have them compartmentalized."
Not exactly.  Really, I just see a bunch of fields with data ...."
I was referencing my initial post that shows how the tables are created and the fields in those tables.  Then I gave you an example of data that is in those fields to see how I could create a category.
So suppose that information was stored in one table, how would you break it up using category.

I control the information the user has access to through the front end.

With this one-to-one relationship in place as it is, is there anyway to avoid that Write conflict.?

If not, how should I break up this information without using one-to-one so that I can avoid this error.
LVL 75
ID: 34198904
"So suppose that information was stored in one table, how would you break it up using category."
Ok ... then I misunderstood the work 'compartmentalized'
So, there is no need for 'category' in that sense.

I guess I'm not seeing why ... you can't just put all of those fields in one table ?  And there are not that many field involved at that.

LVL 10

Author Comment

ID: 34198983
It has to do with tracking.  I have an Access Front End that is used offline by multiple users for field testing.  I have written a script that will sync the Access (local) front end to a SQLServer.  If I have all of these fields in one table, and User A goes into the field and changes "Building" of ID1 and User B goes into the field and changes "Voltage" of ID1.  I set an update flag SQLUpdate in the Main Table.

User A syncs his database first.  It will see that ID1 has been updated and replace the record currently stored in SQL with the record in access, changing "Building"

User B syncs his database second.  It will see that ID1 has been updated and again replace the record currently stored in SQL with the record in access, changing "Voltage" and overwriting "Building"

By breaking this information up into multiple tables,  compartmentalizing the data.  A user only changes small portions of that record, reducing the risk for overwriting data during the sync script.  It does not eliminate the possibility of overwriting data, but it greatly reduces the risk.

I hope this makes sense and you can see why I am doing what I am doing.
LVL 75
ID: 34199116
ok ... I guess you have a very special case that works for you.  

So, are these Write Conflicts occurring then when 2 or 3 users on separate systems are updating the 3 tables at the same time ? (I know that sounds obvious)

LVL 10

Author Comment

ID: 34199164

The only table where IDTag can be changed is under Main-Master.  But if I have a form open that has more than one of these tables as a record source, and I edit [Main-Master].[IDTag] I get X number of Write conflicts depending on how many tables are used in the recordsource.

Since it is one-to-one and referential integrity is enforced.  Changing [Main-Master].[IDTag] should and does change IDTag in every other table, but I need it to do it without the Write Conflicts and me having to click save record 4 or 5 times.

LVL 10

Accepted Solution

VTKegan earned 0 total points
ID: 34359660
So what I have done is create a new form that is for ID Changing and applied permissions so that only select people can do so.

In that form I only select [IDTag] from Main-Master.  This eliminates the pop-up write conflict error.

I also created a table to Track Changes.  In this table I store the OldID the NewID and TypeOfChange.

Any new records are also added here so I have something like
OldID          NewID          TypeOfChange
IDTag1        IDTag17        Edit
                  IDTag 33       New


Thanks for all of the suggestions and guidance.
LVL 10

Author Closing Comment

ID: 34391517
After discussion I managed to come up with a solution.  My post houses the solution, but DatabaseMX did attempt to answer and I left Cascade Delete On.

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question