Im doing this program for a post office and stuff, their DB is Oracle and they want to front end it in Access coz its free or bundled w/ office.
Im using the almost fossilized Access 2000, the laptop Im working on is a2.6 ghz Centrino w/ 1024 MB DDR RAM abd 250 GB HD.
Now, since that I am sure that the machine's specs has nothing to do with it, I want to ask why are my queries so slow?
I have 15 tables each w/ almot 20K records.
So when I query something like this.
SELECT KYOKUSYOCD AS 局所コード, BUNSITUCD AS 分室コード, REREKINO AS 履歴番号, TOKUISAKICD AS 得意先コード, TRIKAMCD AS 取扱科目コード, '得意先局所対応マスタTTOKUKMD01 の局所コード = ' & KYOKUSYOCD & ' と分室コード = ' & BUNSITUCD & ' のペアが、局所マスタTKYOKUMD01 の局所コードと分室コードとして存在していません。' AS エラー内容 FROM TTOKUKMD01 WHERE KYOKUSYOCD & BUNSITUCD NOT IN (SELECT KYOKUSYOCD & BUNSITUCD FROM TKYOKUMD01)
UNION ALL
SELECT KYOKUSYOCD, BUNSITUCD, REREKINO, TOKUISAKICD, TRIKAMCD,'得意先局所対応マスタTTOKUKMD01 の得意先コード = ' & TOKUISAKICD &' が、得意先マスタTTOKUIMD01 の得意先コードとして存在していません。' AS WHATERROR FROM TTOKUKMD01 WHERE TOKUISAKICD NOT IN (SELECT TOKUISAKICD FROM TTOKUIMD01)
UNION ALL
SELECT KYOKUSYOCD, BUNSITUCD, REREKINO, TOKUISAKICD, TRIKAMCD, '得意先局所対応マスタTTOKUKMD01 の得意先コード = ' & TOKUISAKICD & ' が、得意先マスタTTOKUIMD01 の得意先コードとして存在していません。' AS WHATERROR FROM TTOKUKMD01 WHERE TOKUISAKICD NOT IN (SELECT TOKUISITENCD FROM TTORIKMD01)
UNION ALL SELECT KYOKUSYOCD, BUNSITUCD, REREKINO, TOKUISAKICD, TRIKAMCD, '得意先局所対応マスタTTOKUKMD01 の取扱科目コード= ' & TRIKAMCD & ' が、取扱科目マスタTTORIKMD01 の取扱科目コードとして存在していません。' AS WHATERROR FROM TTOKUKMD01 WHERE TRIKAMCD NOT IN (SELECT TRIKAMCD FROM TTORIKMD01);
Its soooooo slow, as in 6 minutes loading slow, windows xp running on pentium 2 slow, very very... ok you guys get it already.
Please help me coz Im losing my mind already. I did one version in SQL Server and it worked just fine. Sad thing is, they lvoe their VBA apps coz it can be like engraved in Excel or something. Anyway please help
First off, I've gotta apologise for how totally poor I am with Access. I know very little so please retract your laughter if my methods are completely stupid, haha. Basically, I'm using Access to pretty much automate a medium sized businesses accounts with reports, but on top of that, I'm using queries to search for various entries in the database.The DB consists of only one table. I was told specifically to do it this way because the owner of the company is useless with Access and believes it's easier this way, even though, to be honest, it'd be easier with a few relational tables. Anywho, in this one table are details regarding Clientele and their Maintenance Contract information. There is one field entitled 'Name' which is where the customers' name is kept, obviously. I was also told specifically to keep Forenames, middle names and Surnames all in this 'Name' field, which quite obviously leads to a few complications.At the moment I'm using queries to search specific fields. I use a select query and then call on user input to determine the search criteria in a specific field. For example, viewing the select query in design view, there's the field 'Name', then under this field, next to 'Criteria' is "[Input Customer Name]". Upon executing the query, the user then inputs the customer name and results are displayed. Firstly, is this the most sensible and efficient way to search for a customer by their name? Are their easier ways to achieve this?My second question. Say, for example, I have a record with a value of 'D. Smith' under the 'Name' field. Say after a phone call to this person they've told me their name is 'Mr. Smith', then obviously, if I were to search for his information, I'd input 'Smith' as search criteria upon query execution. However, Mr Smith's details will not be called upon, not unless the exact value 'D. Smith' is inputted. Is there anyway to return data if it part-matches a search criteria? It'd streamline the search process, that's for damn sure.Sorry for blabbering on, but I'm not down with the ol' Access lingo so I've had to explain stuff in excess to ensure people actually get what I'm trying to say, haha. Cheers for any help in advance, I'd appreciate it. David.
Hi, i'm currently in 1st year of A-levels and need a bit of help with understanding MS Access.
1. I have an 'email address' column, and i need to make it so that when a user clicks on it, they go to Outlook. I'm using 'mailto:' and a hyperlink at the moment, but it would look complicated for a first time user. Anything i culd do to make it easier to, enter emails, and understand them?
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 ?
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.
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.
created a query (in Access 2010) that joins several linked tables (to an Oracle database). The query runs in about 20 seconds when I filter with a hard coded date (e.g., #12/31/2014#). The Oracle table column Im filtering on is defined as date/time.
When I attempt to change the hard coded value to a soft coded value (e.g., Forms![Form1]![Latest_Extract_Date]), the query runs over 5 minutes. In this case, the form field has the exact same value (12/31/2014).
Ive encountered similar issues using Access 2000, 2003, etc. This is quite frustrating. Does Access interpret #date value# is a special way? Is there a way to trick Access into the thinking a soft coded date is a hard coded date?
I have a db that is having some strange speed issues on some very basic queries. Objects that have a rowsource/recordsource with a SQL statment in particular are really slow to populate. Likewise on deactivate/activate the report query seems to reload. I've put breakpoints in every object event with no stops out of the ordinary, and nothing seems unusual.
While I know MSysObjects is a user read-only table, I came across some records that reference objects that no longer exist in the front-end. Is this table cumulative? Is there any way to purge the deprecated records? I tried to turn on then turn off autocorrect features hoping that would trigger it to repopulate, but no luck.
I'm thinking this may be the source of the issue. Perhaps if it is looking for an object that it can not find (especially the ~sq_ unsaved queries) then it's looking to the wrong place on a lostfocus/activate/deactivate or similar event.
Some history on this file, it has been through many versions of Access starting with 2003, which I think is part of the issue as well. Shy of looping every object and replicating the read-write properties, I'm not sure where else to go with this.
I now understand that when opening and saving crosstab queries Access (2010) runs that query to ascertain the column names. Unless you hard-code them. Running the query takes at least 20 minutes. I have hard-coded where I can, but one report takes arbitrary dates so I can't hard-code them.
I believe that turning off AutoCorrect might make a difference to whether the query runs - but I don't want to turn it off.
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.
