Solved

Append tables in a multi user environment

Posted on 2014-04-15
6
280 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

827 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