Access Write Conflict

Suppose I have 3 tables:

Main-ID
Main-Location
Main-Parameters

Each table is like this
Main-ID
IDTag
DrawingNumber
System

Main-Location
IDTag
Building
Floor
Room

Main-Parameters
IDTag
Voltage
Current
kVA


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

[Main-ID].[IDTag]->[Main-Location].[IDTag]
[Main-ID].[IDTag]->[Main-Parameters].[IDTag]

 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?
LVL 10
VTKeganAsked:
Who is Participating?
 
VTKeganConnect With a Mentor Author Commented:
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

etc...

Thanks for all of the suggestions and guidance.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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.

JeffCoachman
0
 
VTKeganAuthor Commented:
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.

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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.

mx
0
 
VTKeganAuthor Commented:
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?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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.



mx
0
 
VTKeganAuthor Commented:
480V AC not DC.

Ouch!

""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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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.

0
 
VTKeganAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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)

mx
0
 
VTKeganAuthor Commented:
No,

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.

0
 
VTKeganAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.