Append tables in a multi user environment

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.
DatabaseDekAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
<< 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
 
DatabaseDekAuthor Commented:
That's put my mind at rest

Thanks Jim
0
 
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
 
DatabaseDekAuthor Commented:
I think I'll risk it, but thank you for the clarification. It's useful to know.


Derek
0
All Courses

From novice to tech pro — start learning today.