John, considering the speed issue, I think I’ll just put a little notify up when it is being mass updated and take it down when it is done.
They are happy with the speed, but are confused with the locking. I think I’m going to try this and see if it appeases them.
They are updating as many as a couple hundred items at a time.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, February 05, 2014 9:51 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: update query with inner join
Liz-
I would guess three to four times as slow. As for locking, after so many rows, it just locks the entire table. How many rows are you talking about? 100? 1000?
Yes, you would use a DAO Recordset on the local table and UPDATE commands to the back end. You could also do a Recordset on both tables, do a FindFirst using criteria from each local row, then simply Edit, copy the values, and Update. Either way will lock one row at a time.
Here’s some sample code from Help about SysCmd:
Dim MyDB As DAO.Database, MyTable As DAO.Recordset
Dim Count As Long Dim Progress_Amount As Integer
Set MyDB = CurrentDb()
Set MyTable = MyDB.OpenRecordset("Customers”)
' Move to last record of the table to get the total number of records.
MyTable.MoveLast
Count = MyTable.RecordCount
' Move back to first record.
MyTable.MoveFirst
' Initialize the progress meter.
SysCmd acSysCmdInitMeter, "Reading Data...", Count
' Enumerate through all the records.
For Progress_Amount = 1 To Count
' Update the progress meter.
SysCmd acSysCmdUpdateMeter, Progress_Amount
'Print the contact name and number of orders in the Immediate window.
Debug.Print MyTable![ContactName]; _
DCount("[OrderID]", "Orders", "[CustomerID]='" & MyTable![CustomerID] & "'”)
' Go to the next record.
MyTable.MoveNext Next Progress_Amount
' Remove the progress meter.
SysCmd acSysCmdRemoveMeter
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 5, 2014, at 5:08 PM, Liz Ravenwood <liz_ravenwood@beaerospace.com> wrote:
Okay John, I’ll do it. I guess I’d just use a dao recordset or something?
I’m still mystified by why an inner join would lock an entire table and not just the fields affected by the linked indexes.
How much slower would it be? If it is a lot slower, I’d rather write code that indicates that an ME is running the mass update at the time and they are too wait.
Their biggest priority is the speed thing.
I wouldn’t mind knowing how to do the status bar, though.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, February 05, 2014 8:59 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: update query with inner join
Liz-
Abandon the Update query. Write code to read the local table and then execute individual Update queries against the server table. You can also update the local table as you complete each row. That should lock only one row at a time on the server. It’ll be slower, but the other users won’t get locked out. You could even consider using SysCmd to display a progress bar on the Status bar by first counting the rows in the local table and then using that to drive the bar incremented one row at a time.
Let me know if you need help with the code.
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 5, 2014, at 4:10 PM, Liz Ravenwood <liz_ravenwood@beaerospace.com> wrote:
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.
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 (11) |
Tidak ada komentar:
Posting Komentar