Liz-
Thanks all,
John, the localtable is LocalTBClosedPRocess and is a datasheet view of a request from the engineers to be able to "mass close" their action items to the main table Drawing. The table drawing is the record source for the main form.
UPDATE LocalTBClosedProcess INNER JOIN Drawing ON (LocalTBClosedProcess.SolutionRev = Drawing.SolutionRev) AND (LocalTBClosedProcess.SolutionItem = Drawing.SolutionItem) AND (LocalTBClosedProcess.ECO = Drawing.ECO) AND (LocalTBClosedProcess.Revision = Drawing.Revision) AND (LocalTBClosedProcess.Drawing = Drawing.Drawing) SET Drawing.Methods = [LocalTBClosedProcess]![Methods], Drawing.[Methods Closed] = Date(), Drawing.HeldForSSI = [LocalTBClosedProcess]![HeldForSSI], Drawing.AffectsInventory = [LocalTBClosedProcess]![AffectsInventory], Drawing.MEInstInventory = [LocalTBClosedProcess]![MEInstInventory], Drawing.MECommentInventory = [LocalTBClosedProcess]![MECommentInventory], Drawing.AffectsProcurement = [LocalTBClosedProcess]![AffectsProcurement], Drawing.MEInstProcurement = [LocalTBClosedProcess]![MEInstProcurement], Drawing.MECommentProcurement = [LocalTBClosedProcess]![MECommentProcurement], Drawing.AffectsPC = [LocalTBClosedProcess]![AffectsPC], Drawing.MEInstPC = [LocalTBClosedProcess]![MEInstPC], Drawing.MECommentsPC = [LocalTBClosedProcess]![MECommentsPC], Drawing.ReworkTravNum = [LocalTBClosedProcess]![ReworkTravNum], Drawing.ModNoticeNum = [LocalTBClosedProcess]![ModNoticeNum];
This second one is a subtable to add what they did.
UPDATE LocalTBClosedProcess INNER JOIN Actions ON (LocalTBClosedProcess.ECO = Actions.ECO) AND (LocalTBClosedProcess.Revision = Actions.revision) AND (LocalTBClosedProcess.Drawing = Actions.Drawing) SET Actions.Comments = [LocalTBClosedProcess]![MEActions];
A grossity is that the business changes and processes pushed me in to having a composite pk of 5 fields.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Tuesday, February 04, 2014 3:01 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: update query with inner join
Since about A 97, Access has used row-level locking, but it will escalate to the entire table if enough rows are impacted. Locking happens as the query starts to run because it has to be sure it can perform the update if you click "OK" on any warning message. Queries run from code should lock only as long as it takes the query to run because there is no confirmation in the UI. Does that help?What's the SQL of your query, Liz, and which is the "local" table?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Feb 4, 2014, at 10:34 PM, Liz Ravenwood <liz_ravenwood@beaerospace.com> wrote:
Even if it is only a selection of the table because of the inner join to a local table?
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of wrmosca@comcast.net
Sent: Tuesday, February 04, 2014 2:30 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] RE: update query with inner join
Liz
An UPDATE query is a transaction. All the changes are loaded into a buffer when you run the query. The records are not changed in the table until you click the OK on the warning message box which commits the entire transaction. The buffer is used in case you change your mind about the update. In that case the table is left alone or in SQL-speak, the transaction is rolled back.
As far as I know, the entire table being updated is locked right before the transaction is committed because it's not a row-by-row update. But John V. might prove me wrong.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
---In MS_Access_Professionals@yahoogroups.com, <liz_ravenwood@beaerospace.com> wrote:Pros, can an updated query with an inner join lock the entire table?
Respectfully,
Liz Ravenwood
Programmer / Analyst
B/E Aerospace | Super First Class Environments
1851 S Pantano Road | Tucson, Arizona 85710
Office +1.520.239.4808 |
Passion to Innovate. Power to Deliver
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar