Hi John, The remote table is in Access, and now is a bit smaller since I archived out some data. All join fields are indexed.
Locking the local table isn’t an issue, but it is an issue that the remote table is locked. The users requested “mass update” ability because of the huge quantities of data they have to manage through engineering changes.
Here again, is where corporate tells me this shouldn’t be an enterprise solution, but they won’t give me anything else nor train me nor allow me any sandbox testing nor…..
I need a new job. I actually have asked to be relocated here doing other functions. I don’t want these particular databases any more. Siemans PLM hired someone to subsume them and that fell apart so we wasted a huge amount of money on that. Meanwhile, I’m the little gal with her pinky stuck in the hole in the dyke.
NOT happy about it. My blood pressure went so high I ended up in the hospital for a few days last week. Need to find other solutions here. I’m constantly having to take these databases down for compact and repair. Corporate won’t help me. I have several hundred users on these databases that are critical to tracking engineering changes and I see data loss regularly. I report it up, but … nothing.
I tell them the metrics are not to be trusted – nothing…
Nothing nothing nothing.
I wouldn’t own stock in this place because of the way they ignore some of the critical infrastructure and expect good hard working people to help the crippled sucking machine along.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, February 05, 2014 1:55 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: update query with inner join
Liz-
I would guess that it locks both tables while the query is running. Is the “remote” table in Access, or is it SQL Server? If the latter, then it could take a long time if the Drawing table is large. I don’t think Access can take advantage of any indexes on the server to make this run faster. If both are in Access, it’s imperative that all the join fields are indexed.
Why is locking the “local” table an issue? Isn’t that a table local only to the user who is running the query?
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 11:20 PM, Liz Ravenwood <liz_ravenwood@beaerospace.com> wrote:
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.
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 (7) |
Tidak ada komentar:
Posting Komentar