Solved

Append tables in a multi user environment

Posted on 2014-04-15
6
278 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now