Solved

Append tables in a multi user environment

Posted on 2014-04-15
6
279 Views
Last Modified: 2014-04-16
Main table with 7 linked tables.

I want to copy  the main record wth all related data from the 7 sub tables to the same tables. (There will be a new main record ID and corresponding new id for the linking tables) I can get the procedure to work, my question is how will it respond in a multi user environment. If someone else is editing a record that is being copied what will happen? or does access just copy what is there regardless and append all info to all tables.

It is unlikely that someone will be editing that data but it is possible.
0
Comment
Question by:DatabaseDek
  • 2
  • 2
  • 2
6 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40001434
<< If someone else is editing a record that is being copied what will happen? >>

 It will just copy the records and create new ones.

Jim.
0
 

Author Closing Comment

by:DatabaseDek
ID: 40003554
That's put my mind at rest

Thanks Jim
0
 
LVL 9
ID: 40004031
Jim's right, but I'll add a bit of clarification.  The copy routine will copy the data that has been committed into the tables (the parent and 7 children).  It's possible that the user doing the editing has updated *some* of the records in the "family" but not others.  For example, they might edit the parent table, then move the cursor to a child table.  The moment focus moves to the child table, the parent edit is committed.  They might start editing a child value also, but if your copy routine runs at that moment, it will pick up the updated values in the parent (since it was committed) but not the child (since it is still being edited).  This may not matter to you, but I thought I'd mention it.

Cheers,
Armen Stein
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 57
ID: 40004058
Armen,

 Thanks for kicking that in!  It's an excellent point and one that I should have addressed.

 Many don't realize that when moving to a sub form off a main form, the parent record is committed automatically (which is actually one of my big gripes with Access), so it would be easy to have a data inconsistency if someone happened to be in the middle of working with it.

Jim.
0
 
LVL 9
ID: 40004075
Right, Jim.  If it's important to have a consistent family, you need to load the whole family into a set of work tables, edit them, then add/update/delete everything back in the main tables inside one transaction.  Rarely necessary, but certainly doable.
0
 

Author Comment

by:DatabaseDek
ID: 40004289
I think I'll risk it, but thank you for the clarification. It's useful to know.


Derek
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

775 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