[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Append tables in a multi user environment

Posted on 2014-04-15
6
Medium Priority
?
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 58
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

649 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