Solved

Access Write Conflict

Posted on 2010-11-23
12
286 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:VTKegan
  • 7
  • 4
12 Comments
 
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.

JeffCoachman
0
 
LVL 10

Author Comment

by:VTKegan
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.

0
 
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.

mx
0
 
LVL 10

Author Comment

by:VTKegan
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?
0
 
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.



mx
0
 
LVL 10

Author Comment

by:VTKegan
ID: 34198839
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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.

0
 
LVL 10

Author Comment

by:VTKegan
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.
0
 
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)

mx
0
 
LVL 10

Author Comment

by:VTKegan
ID: 34199164
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
 
LVL 10

Accepted Solution

by:
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

etc...

Thanks for all of the suggestions and guidance.
0
 
LVL 10

Author Closing Comment

by:VTKegan
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now