Solved

Access Write Conflict

Posted on 2010-11-23
12
291 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 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