Update Query Is Running Too Slow

Feb 13, 2008

What would be a better way to do below query --



UPDATE (tblAFFIRMATION_REC_TOOL LEFT JOIN tbl_TZero_Spns ON tblAFFIRMATION_REC_TOOL.CptySPN = tbl_TZero_Spns.SPN) INNER JOIN Entity ON tblAFFIRMATION_REC_TOOL.ReferenceEntity = Entity.ReferenceEntity

SET tblAFFIRMATION_REC_TOOL.[Scope Reason] = IIf(IsNull(tbl_TZero_Spns!SPN) Or (Entity!Test='EM'),tblAFFIRMATION_REC_TOOL![Scope Reason],'TZero Trade')


WHERE ((([tblAFFIRMATION_REC_TOOL]![Scope Reason])="Affirmation Eligible"));




the way it right now, it's running for about half hour..
Could that be IIF statement that slows it down ?

View Replies


ADVERTISEMENT

Very Slow Update Query

Nov 21, 2007

Hi all,

I'm doing the simplest little 'INSERT INTO' type operation here with recordsets in Access 97 (SR2). Inserting into a table whose name is stored in the Const TABLE_CANDIDS.

I have a little piece of code that doesn't move on until the changes are committed to the table (usually this is just a single long integer being added).

The transaction takes up to a second to commit, and sometime doesn't commit at all. I tried putting begintrans and committrans around the loop but it seems to make the problem worse.

Can anyone spot my error?

Thanks,
Rob

' Empty the TABLE_CANDIDS table then start filling it with the current
' list of Candidates
DoCmd.RunSQL "DELETE * FROM " & TABLE_CANDIDS
Set rstCandIDs = CurrentDb.OpenRecordset(TABLE_CANDIDS)

' Find the CandIDs for all selected items (default to select all)
For Each objListItem In objListView.ListItems
If objListItem.Selected Or Not blnSelectionOnly Then
rstCandIDs.AddNew
rstCandIDs!CandIDFiltered = CLng(objListItem.Text)
rstCandIDs.Update
lngCount = lngCount + 1
End If
Next objListItem
rstCandIDs.Close

' Wait for up to 1 second until the new value(s) are committed.
Screen.MousePointer = 11
datStart = Now()
Do While IsNull(DLookup("CandIDFiltered", TABLE_CANDIDS, "CandIDFiltered <> 0"))
If DateDiff("s", datStart, Now()) > 100 Then
Exit Do
End If
DoEvents
Loop
Screen.MousePointer = 0

View 2 Replies View Related

Slow Running

Jun 24, 2005

I am using Access 2003 but it is to slow, what can I do?

View 1 Replies View Related

Where Exists /IN Running Slow

Jan 10, 2008

hello all,
i've written this query and all was working fine, but.... i added the Where IN statement and no it runs really slow. i have tried using the Where exists option but couldn't get it working.

As i understand it this is probably the worst query to run on a jet database
as it contains subqueries, select Disinct and a Where statement.

any /pointers to speed it up?

thanks all


SELECT LNE_ACTUAL_COST_JOIN.Project_ID, LNE_ACTUAL_COST_JOIN.UID, SUM(LNE_ACTUAL_COST_JOIN.[10/08] ) AS ActualCost, PPE.[Plan at Prior Period End]

FROM LNE_ACTUAL_COST_JOIN RIGHT JOIN
(SELECT DISTINCT LNE_P3e_Activities.Project_ID, LNE_P3e_Activities.UID, LNE_P3e_Activities.UID_Desc, sum(Results_Cost_Next_Period_Activities.Planned_Co st) AS [Plan at Prior Period End]
FROM LNE_P3e_Activities INNER JOIN Results_Cost_Next_Period_Activities
ON (LNE_P3e_Activities.Project_ID = Results_Cost_Next_Period_Activities.Project_ID) AND (LNE_P3e_Activities.Activity_ID = Results_Cost_Next_Period_Activities.Activity_ID)

WHERE LNE_P3e_Activities.Project_ID IN
(SELECT project_ID
FROM Project_List_LNE
WHERE LNE_P3e_Activities.Project_ID = Project_List_LNE.Project_ID ) AND

((Results_Cost_Next_Period_Activities.Year)="07/08") AND ((Results_Cost_Next_Period_Activities.Period)=11)

GROUP BY LNE_P3e_Activities.UID, LNE_P3e_Activities.Project_ID, LNE_P3e_Activities.UID_Desc
)
AS PPE ON (LNE_ACTUAL_COST_JOIN.UID = PPE.UID) AND (LNE_ACTUAL_COST_JOIN.Project_ID = PPE.Project_ID)


GROUP BY LNE_ACTUAL_COST_JOIN.Project_ID, LNE_ACTUAL_COST_JOIN.UID, PPE.[Plan at Prior Period End];

View 1 Replies View Related

Access 2003 Running Very Slow

Jan 31, 2006

I have access 2003 installed on two different machines. One machine was built 6 months ago with the best hardware available. The other was an older slow IBM.

For some reason, I am experiencing what appears to be some serious time delay (talking in the range of seconds, sometimes tens of seconds) when building a report that has a subreport in it on the fast machine. Yet when I load the exact same database on the slow machine, the report opens instantly.

Any ideas? I'm lost.

View 3 Replies View Related

Linked Tables Running Slow Over Network

Oct 12, 2006

Hi

I have a couple of acces databases running on a peer to peer network (database A and B, which are housed on PC1). The database B has a link to a Customer table in database A. This was running fine until another user on the peer to peer network wanted to use database B on their pc (PC2). I had to change the the file location of the linked table to show it's location on the network so PC2 could open it. Unfortunatley this greatly slowed the operation of the database B on both machines.
If anyone has made it this far - Is there any way round this?

Regards

David

View 3 Replies View Related

General :: Database Running Slow After Changing To Design View

Dec 14, 2014

I am new to access.

On occasion my access database all of a sudden begins to operate very slowly, I notice the queries take a much longer time to run.

Last week it suddenly began to operate very slowly after idling in excess of 30 mins

Today when I change the screen to design view and then back to form view once again it begins to operate very slowly. (yesterday was working fine on my desktop at home, I have not made any changes to the database with exception to saving and using the file this morning on the shared drive at work) If I shut down and re open it operates well but once again changing to design view causes the same issue. Sometimes compact and repair rectifies sometimes not.

View 1 Replies View Related

Running More Than One Sql Update Query

Aug 26, 2005

I am helping a company reorganize its employee data to reflect recent changes in the company's organization. It involves a lot of data manipulation that, for the most part, can't be done programatically.

I can save a little time using SQL update queries like this:

update personnel set Department = "Support Staff" where Jobtitle="Shipper"
update personnel set Department = "Maintenance" where Jobtitle="Mechanic"
update personnel set Department = "Regional" where Jobtitle="HR Manager"

I have prepared a text file containing 530 such queries that can be pasted into Access. But as much time as that saves me, it is still a day or two of cutting and pasting.

What would be the best way to talk Access into running all the queries, without having to paste in every one individually?

Thanks...
...df

View 2 Replies View Related

Compare Value Before Running Update Query

Aug 31, 2006

Hi all
I want to view vehicles due for servicing in the next week. The queries giving me this information work fine. I have a form that I can select, from a drop down box, either 1,2,3 or 4. A report is then run and emailled to the appropriate people. After testing to see what was the easiest way to remember what the last week was (I tried setting the combo box to remember the value but it did not, unsure why, I also tried recording Mondays date in a field in a table and assigning each Monday a value 1-4 then running a query...long story short, to time consuming) I have used a table with one field to hold the value and after emailling the report, an update query adds one to this field and it is displayed above the combo box in a txt box. I want to reset the value to 0 if the value equals 4 before the update query runs. Is this the best way to do it or can someone suggest another way of doing this.
Thanks in advance
Craig

View 2 Replies View Related

Running A Update Stock Control Query From A Button

May 20, 2005

Hi

I am a relatively new user to access and wondered if you could help me with a stock control problem.

I have created an update query (qryUpdateQuantityinStock) with the below fields:

QuantityinStock
tblProduct
Update: [QuantityinStock]-Forms!frmCustomerOrder![frmProductOrderLine Subform].Form!Quantity

ProductNumber
tblPrdocut
Criteria: [Forms]![frmCustomerOrder]![frmProductOrderLine Subform].[Form]![ProductNumber]

When the user enters the quantity they require into the orders form I would like to deduct it from the quantityinstock field in the products table.

The above query does work if you add the following code to the beforeupdate event of the orders subform:
If Me.NewRecord Then
DoCmd.SetWarnings False
DoCmd.openquery "qryupdateQuantityinStock"
DoCmd. Setwarnings True
End If

Although I wanted the query to run from a button (confirm order) on the order form so that the user decides when to deduct the stock.

I cannot put a button onto the orders subfrom where the query should run from and if I place the code on a button on the main orders form it only deducts one order (one product) and not all products on the order.

Can you help me find a way to run the query from a button?

I have read through the threads and found lots of information on stock control, although have not yet found anything similar to this problem. I understand that this is not the correct way to do stock control, although my database is for academic purposes and only requires a simple stock control system.

Any help would be greatly appreciated (sorry for the long post!). :)

Many thanks

View 1 Replies View Related

Queries :: Pop Up Messages When Running Update Query / Macro

Jan 1, 2015

I have 3 different "buttons" on my form that run individual update queries. I know these update queries are running correctly. Decided to make a macro that will allow me to run all these queries by clicking one macro button.

My question is: Is there some way to stop the pop up msg that advises I am about to run an update query and the next pop up msg that informs me of how many rows I am going to update?

These pop up boxes are starting to drive me crazy when I run the macro.

View 5 Replies View Related

Subform Slow To Update

Jul 27, 2005

I wonder if anyone could offer some advice on a problem with a form & subform?

I have a main form, based on table PublicationOrders:
POrderID (pk)
EventID(fk)

And a subform, EventDisplay, based on table Events:
EventID (pk)
EventName

The main form and subform are linked on the field EventID and my master and child links are correct, as far as I can tell.

The field EventID on the main form is a combobox, bound to field EventID in the table PublicationOrders. It allows the user to select which event an order is being sent to. In the Afterupdate property of this combobox, I have the code: Forms!PublicationOrders!EventDisplay.Requery

I would like the subform to update to show the details of the event that has just been selected from the combobox. However, for some reason, on afterupdate, the subform displays whichever event was selected when the previous record was entered. If I close and reopen the form, it will right itself and show the correct event information that corresponds with the order.

Any ideas why it's doing this? I have searched here for a solution but can't seem to find anything that relates.

View 2 Replies View Related

Update On Form Is Extemely Slow

Sep 30, 2005

I'm wondering if anyone has any idea how I can speed this form up. I have similar forms that don't calculate as much but are much faster when updating the date.

Basically, I have a make table query that pulls all units due to be delivered between a certain time period, that are a specific priority and we input how many days we want to push the delivery date back.

A form opens that shows the Job Code, Run, Door Style, Special Color, Lot Delivery Date, W/O Due Date, W/O Start Date and a Change check box. If the Change check box is selected the W/O Due Date moves back however many days entered. Based on the new W/O Due Date and whether or not a Raised Panel door or Special Color was selected the WOSD resets as well.

Here is the code I have used. It takes a little over a minute to update 1 line and we may need to move several lines. After all the units needed to move are selected, an update query is run updating the information in the actual tables used in the make table query.

Private Sub Change_Exit(Cancel As Integer)
If Me.Change = True Then
Call UpdateWODDChange
Call UpdateWOSDChange
End If
End Sub

Private Sub Form_Close()
DoCmd.OpenQuery "qryUpdateWODDPriorityChange"
DoCmd.OpenForm "frmMenu"

End Sub

Private Sub UpdateWODDChange()

Dim intNumDays As Integer

intNumDays = Forms!frmUpdateWODDbyPriority.Form.PushForwardDays

Me.WODD = AddWorkdays(Me.LotDelDate, intNumDays)

End Sub


Private Sub UpdateWOSDChange()

Dim AddColor As Boolean
Dim intNumDays As Integer

If Not (Forms!frmUpdateWODDbyPriority.Form.SpecialColor = 0) Then
AddColor = True
Else
AddColor = False
End If

Select Case Me.DoorStyle

Case "Eagle", "H/E", "F/E", "RP-9", "RP-22", "RP-23"
If AddColor Then
intNumDays = 7
Else
intNumDays = 6
End If

Case Else
If AddColor Then
intNumDays = 7
Else
intNumDays = 5
End If
End Select

Me.WOSD = MinusWorkdays(Me.WODD, intNumDays)


End Sub

View 2 Replies View Related

General :: Slow Searches After Update To 2010 Data?

Apr 16, 2013

I have a big application, which we updated to Access 2010 a month ago. It ran fine. Then we also just updated the data from 2003 to 2010. Now we have very slow searches of big tables/queries, and the tech says that 'cpu usage on the server has gone to 100%'.

What can be happening? Nothing else has been changed except that the data has been updated to 2010 accdb.

View 3 Replies View Related

Modules & VBA :: Running Update Query On Record And Have Form Show Updated Record

Jan 26, 2015

i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?

View 4 Replies View Related

Access Slow To Load [really Really Slow!]

Dec 14, 2007

Hi All,

I'm having trouble with access. Basically it has been very slow to load
(over 30 seconds when not opening a database) and when opening a database it just crashes.

Has anyone any idea what could cause this as it worked fine before ?

also would a reinstall help ??

-Elfman

View 3 Replies View Related

Update Qry (running Sum)

May 22, 2006

I need to update a ytd-budget field using the curmo-budget field. The table contains one record for each month. The ytd-budget for Jan would be the same as curmo-bud for Jan. Feb's ytd would be the sum of Jan + Feb...etc. Is there a way to update the ytd fields with a 'running sum' feature as found in the report options?
(... unfamiliar with VB). Thanks

View 11 Replies View Related

Union Query Very Slow

Sep 27, 2005

Hi,
In my database im using several union queries as they turn out to be very useful.
However, whenever I have a calculation that involves one of the union queries, things happen at a very slow pace. Is there a way to speed it up a bit?
Thank you
Stacey

View 6 Replies View Related

Slow Query In Access 2007

Jan 19, 2008

Any ideas why a particular query would run very slow in access 2007 when it runs fine on 2003. This is running on a terminal services environment and the database connects to a ms sql 2000 server. I will try it outside terminal services when I can but I can't understand the problem. Im not talking about slightly slower its something like a factor of 100 or 200 slower (I had to limit the dataset just to get a return within a day).

The database was an access 2000 mdb for compatibility across computers but I have tried converting it with out any luck.

View 5 Replies View Related

Slow Query Using A Global Variable

Aug 9, 2005

Hello,

I have the following query that I set up as a test, and it runs fine:

SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID = PROBLEM_DE.PROBLEMNR
WHERE (((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))='K29') AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY STATUSHISTORIE.PROBLEM_ID,
STATUSHISTORIE.STATUSDATUM;

I then set up two global variables ( a String and a Date) and respective functions to return them – ReturnE( ) and ReturnKW( ). Now my query looks like this, but takes ages to run:

SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON [STATUSHISTORIE].[PROBLEM_ID]=[ PROBLEM_DE].[PROBLEMNR]
WHERE (((STATUSHISTORIE.STATUSDATUM)<ReturnKW( ) ) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE( ) ) AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY [STATUSHISTORIE].[PROBLEM_ID], [STATUSHISTORIE].[STATUSDATUM];

My two public functions that return the global variables look like this:

Public gstrE As String 'global variable: contains E used for query
Public gdatKW As Date

Public Function ReturnE ()
ReturnE = gstrE
End Function

Public Function ReturnKW ()
ReturnKW = gdatKW
End Function


The tables are actually Views set up from an ODBC Data source. Can anyone please tell me why these global variables are causing the traffic jam? :)

Thanks in advance
J

View 6 Replies View Related

Speed Up A VERY Slow Append Query

Jul 27, 2006

All,

I'm not sure how well I've managed to search on this as I'm not too sure where to start!

I have an append query as follows:

INSERT INTO tbl_Employee ( Company_No )
SELECT tbl_Co_Data.Company_No
FROM tbl_Co_Data
WHERE (((tbl_Co_Data.Company_No) Not In (select Company_No from tbl_Employee)))
ORDER BY tbl_Co_Data.Company_No;

Basically this query is run a number of times a day and appends new company numbers in to a table - 'tbl_Employee'. It's badly named - it's not got much to do with employees. Any way it takes a good 3 minutes to run with about 20k records in tbl_Co_Data and probably 18k records in tbl_Employee.

It looks to me like it's looping through each record in one table for each record in the other - which is plain daft.

I'm currently experimenting with a DTS package that puts tbl_Co_Data in to SQL server first before the query would run (tbl_Employee is already there) with a view to running a SP and ditching the query.

Does anybody have any other ideas as I'm having problems with the DTS in that it appears to be pretty slow in itself!

Many thanks in advance for any response.

View 3 Replies View Related

Modules & VBA :: Way Of Looping Through Continuous Form Recordset And Running Update On Each Line?

Jun 11, 2014

i have set up an update query. is there a way of looping through a continuous form record set and running the update on each line?

View 2 Replies View Related

Slow Query On Table With Many Memo Records

Feb 7, 2006

Ok my database tracks escalations through my team, I have a main table that stores the unique ID's from the other tables I use in my Combo boxes, this part works really well, no problems.

My issue is with a tableI have claled "TBL_EscJournal", this table has the following fields:

JournalID (Unique Ref, generated by autonumber)
EscID (the escalation Id that this journal is relevant to)
Journalcreator (captures name of person who as entered the journal)
JournalNotes (memo field where you enter your update)
JournalDate (Date/Time the journal was entered)

So typically when viewing the main detailed form for a particular escalation I have a subform that shows all the journal entries relevant ot that escalation.

This table is huge, about 70% the total size of my database, partly because of the number of journal entries and partly because it is a memo field and a lot of data is required sometimes.

Up until now th edatabase has been located on a local server and has been fine for local users (2-3 of us) however there is a requirement for another office to use this database.

I am now experiencing massive performance issues, whereby the data is tkaig a long time to refresh on the other sites.

I have migrated the DB over to a SQL back end but still finding performance issues, which further testing has shown that the TBL_EscJournal is the cause.

So a coupel of things really, is there another way I can layout this table to improve performance or should I be uerying the data from this tabel in another way, would it be better for me to split TBL_EscJournal in two, the first part keeping the date/time and person who entered and the second part keeping the notes. Possibly increasing the query speed by carrying out the query on the first part of the table and not on the notes (memo) part ?

I hope this makes sense, if not feel free to PM me,

Appreciate any help or assitance you could offer

MattP

View 4 Replies View Related

Forms :: Create A Message Box To Flash On Screen To Tell User That Update Code Is Running

Mar 6, 2014

I need to create a message box or a form or something to flash on the screen to tell the user that a piece of "Update" code is running. the update code will be updated reports from marketing returns, but the 3 branches who use the information are separate so I am creating an update form to download and update the table.

The code for the update is already working, but can take a while, so I thought a message or splash screen would be useful as the update runs on start up.

It would have another use, I have a report which is made mainly of calculated fields on an onPrint event and also takes a while to work it out, so a similar screen would be more useful than my current spinning circle and blank screen.

View 1 Replies View Related

Queries :: Slow Query Filtering On Concatenated Variable

Jul 9, 2015

I am trying to use a criteria to filter out nulls in concatenated variable, which slows the query considerably.The part that makes it slow looks like this -

WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...

If I work them individually, it's not a problem, the query is fast, but I have to combine them first in order to produce the correct output.

View 13 Replies View Related

Queries :: Pass Through Query Slow Until Change Search Criteria

May 21, 2014

I have an access 2007 database connect to sql server 2008.I am running a pass though query to search between two dates (this query has been fine for years)

If I now run any search using parameters from 26th March 2014 to date - the query takes 10+ minutes to run.If I then change the date to 25th March 2014 to date - it runs in a nano second.I have not changed the back tables and I have not changed the format the data is saved in.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved