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];
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.
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 ?
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?
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.
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 ?
Tables join using fQSRegno. Scenario is Tbl Individual contains all the members info.Tbl Point contains point given to members who attend courses. Problem is when I created a query...
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
My DB is very slow when it's used by more than 1 person at a time. It's divided into FE/BE. The BE is in a shared server and a copy of the FE is in each person's machine.
I don't know why it takes ages to respond. Any help will be very much appreciated.
I have an Access 2003 db that works extremely well as a stand-alone application but I am keen to “split” it so that many users can operate it together.
Accordingly I did the split and placed the tables on our server, and tried running the front end locally.
Much to my horror, it takes about a minute to open, and many seconds to do anything after a button press etc.
I have been testing it on my wireless home network and the same thing happens, except now I can monitor network activity from the “network working” light.
What I see baffles me and would welcome comments, in particular, what I can do to speed it up by a factor of 100!
1.It takes forms about 50 seconds to open; however, this is due to about 40 seconds of network activity BEFORE the form’s load event fires! 2.In DESIGN mode, any minor change to a control for example, results in about 40 seconds network activity. 3.All queries upon which forms are based take well under a second to run over the network. 4.Making an mde file has no effect.
I thought the idea of splitting was to keep the forms/queries/code etc, local to a machine so I don’t understand what all the network activity is about, particularly in design mode. Also, given the actual queries are so fast, just what is Access doing??
I have a form with a subform (there is no child parent relationship) , when I open the mainform it causes the subform to slow down.
ie If I open the suform by itself - it opens quickly If I then open the main - it runs quickly
If I then open the main again it runs slow or if I open the subform again - it runs slow - it continues to run slow - until I have opened the sunform by itself.
So something in the mainform is causing the subform to slow down.
i'm create a aplication in ms access 2000 developer edition. this app comunicate with sql 2000.
we have problem with slow connection provided by access. in headquater wher people use access app it working quickly. sql 2000 is localted at headquarter too. but if i run app in warehouse it working very slowly.
VPN connection doesn't slow. but access can't use all vpn connection capacity but only 1/5 from this capacity?
how can i do something that acces can use all capacity that i have?
example: link is 512 kbps but access use only 100kbps and work very slowly.
I developed a sales database that contains around twenty forms, (in addition to tables, queries, reports and 2 modules). In these forms, I'm setting recordsets, select statements and filters. We recently hired a person to set up a Virtual Private Network. After spending a couple of thousands on a server and countless hours reprogramming the database to have multiple users access the database out in the field, (via Verizon broadband network card), we were unsuccessful to use the database since it was taking too long to download the data or perform any calculations. The database is split. The users have a front end.
The person setting the VPN has suggested that I get rid of all the queries that I have in the forms (which is impossible), or use Visual Basic for the front end instead of Access with VBA.
When I set up the recordsets I use the following setting: rs.CursorLocation = adUseClient rs.CursorType = adOpenDynamic rs.LockType = adLockOptimistic
I don't know what to do. This program is very complicated and I don't want to have to reprogram the whole thing with Visual Basic, and I don't know how the front end is going to work if I eliminate all the queries, select statements and filtering.
Any ideas, comments or suggestions would be appreciated.
At the moment, I have a very simple Access 2007 database with 3 small tables, and 4 small forms.
In two of the forms' headers, I have a text box with contents '=Nz([FirstName] & " " & [MiddleName] & " " & [LastName],"Untitled")' which I took from Microsoft's Marketing Projects sample database.
When I open the two forms with this text box, the forms displays instantaneously except for this text box -- this text box doesn't show its contents for almost a full second. The part of the form that displays right away includes the fields FirstName, MiddleName, and LastName, so it's not that Access is taking forever to pull the row of data...
The database isn't too large. It's about 800K.
It isn't the computer, it's an Intel Core2 Duo E6600 with 4GB memory.
Any ideas on how to speed this up? Is there a totally different way I should be doing this?
As you can problaby tell, I'm relatively new to Access. I've read a few books, but am at a loss why this function is running so slowly. Before, I've focused on web development however this client requres Access for this project. Any help would be much appreciated!
I have a very simple table with about 20 fields. The thing is it has about 360,000 records in it. It has always been a little slow to open, although using a query works pretty fast. There is just this one table, no forms, no reports, no saved queries. Simple database with one table, but it takes forever to open now, seems to have gotten worse. I have compacted and repaired many times and it does not help. The database is stored on a server.
When I click to open the table up it says RUNNING QUERY at the bottom of the screen and then runs and runs for a LONG time. I cannot rely on queries since we want to keep this real simple for users of this data. Just go in, do a FIND or a FILTER and that is it.
Any idea why this is running so slow, or why it says RUNNING QUERY when I attempt to open it, is that normal?
Hello All, Yesterday only i designed my first form in VB.I designed a tab form as i have to place two kinds for data from the same table separately (for user convience).i was succesfull in that.But the problem is the form is very slow to load........very very slow......
i want to know whether Tab form are slow or i have made any mistake..
I have a project that produces a roll report. To do this, I have two tables:
T1 (roughly 20,000 records) ID (Primary Key), PrevAmt, CurrAmt, Action
T2 (roughly 40,000 records) ID, Amt (linked Excel spreadsheet)
I then run 4 queries in sequence
Q1: Update T1 set PrevAmt=CurrAmt, CurrAmt=0; Q2: INSERT INTO T1 (ID, PrevAmt, CurrAmt) SELECT ID, 0, 0 FROM T2; /* keys that already exists will fail to insert)*/ Q3: Update T1 INNER JOIN T2 ON T1.ID = T2.ID SET T1.CurrAmt=T1.CurrAmt + T2.Amt; Q4: Update T1 SET Action = iif(PrevAmt=0 and CurrAmt<>0,"NEW",iif(PrevAmt<>0 and CurrAmt=0,"DROPPED",iif(PrevAmt<>CurrAmt,"CHANGED","UNCHANGED")));
T1 and the queries is in an Access file on a network drive. T2 is a link to an Excel sheet on a network drive.
When I run the queries from a PC on the local LAN, it takes about 2 minutes to update. I consider that acceptible. But I now have to run it from a PC attached to the network via a VPN over a somewhat slow DSL line. The queries take over 20 minutes to run. I want to cut that time down. But the Access file and the Excel file need to stay put as other users use them for other purposes. (Though they know not to access them till after they see my roll report in their in-box.)
What can I do to decrease the update time on this?
This problem is driving me nuts!! I`ve just spent a fair few months creating a database to be used by our sales team, I`ve about finished it and came to trial it with the sales manager. When he is using the database at the same time as me it becomes slow. The design has a front end with all the queries, forms etc and a back end on a server containing the tables. The database was created using Access 2000 and was designed to replace a similair db created on 97 which has a decent speed on the network. The other strange thing is that one particular macro that I`ve been using for the speed tests runs quite slow (about 3 secs) when 2 pcs have the db open but once the macro has run if it is run again it takes typically less than a second to run. What would cause a db to run a macro at different speeds after they`ve been run once? I`ve tried everything I can find on the net to try and improve the speed to no avail. Help please!!
Hi everybody, I have an access database which is around 40 MB after compacting it and I feel that it is slow especially when I open cross table form. Is there any way to speedup the databse. Note: my databse is on the share drive in my work.
I have a 16Mb DB. The intention is for users to access this over the network without copying onto their local machines.
At present, many of the forms are located on 1 master form and they are all subforms on tabs. There are a lot of calculated control boxes on the forms, and these seem to be very slow to bring back the data over the network whereas on my local machine, the data is displayed in a couple of seconds.
COuld this be sped up somehow? Maybe using queries or other methods?
I have developed a DB for work and it has grown, it is about 60Meg, all of a sudden when I am opening Design View in Reports, or queries it started running really slow.
I always Compact on Close I have Split the DB.
Still no Joy, I have a brand new Dell PC so nothing wrong there.
What is happening? it is a nightmare to get anything done.
I've been searching for an answer for a long time, but i have a database with approx 100 forms, 100 tables (most linked to SQL Server) only a small proportion are front end tables, 50 or so queries, 20 or so modules.
Anyway the problem is, is that the database is slow, to open and to develop in, i have a decent system to develop on with a 3GHz CPU and 1 GHz RAM, but i dont get this problem with any of the other access databases i deal with.
It's kind of a lagging effect, then once it has been written into memory it's quicker... but why would that be different between the systems, as most the systems i deal with are fairly similar in size and in complexity.
For example when loading the logon screen, which is a simple unbound form, it seems to lag, there is 1 combo box which has a front end table as its row source, like 6 records. Then a username and password text boxes which are not bound to anything. Why should this be lagging? After like 2/3 secs its then ok, once ive logged in and then go back to this logon screen its fine, so i think it has something to do with memory.
Anyone else had anything similar happen to them and did they do anything to minimise this lagging effect?
My acccess (.mde) application suddenly slowed down. I have increased the size for some fields in some Tables. Is it could be the reason for this? Is there any way i can get the speed back.
The database response is significantly slow when our application requests data over a network. Are there any settings to adjust network response? The network is connected via a T1 line.
According to information I've found online, our application is the front end to the database and the database sits open on the server.