Hi
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.
Please advise.
H
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.
I have a front end that is connected to three back end files. The front end is on my local computer while the back end files are on a network drive.
There are a lot of calculations that go into the queries and intermediate queries. For a report, I have based it on a UNION query.
But when trying to design the report it takes about 45 seconds just to do any one thing, e.g.;
- Add Groupings
- Add Grouping Headers//Footer, sorting option
- Add bound textbox
:eek:
Needless to say this is very annoying.
:mad:
The union query itself runs fine (takes about 15 seconds to run) and returns about 12,000 Rows. The union query looks like this (I changed the field names to make it read easier, hopefully);
SELECT a1, a2, a3, a4, a5
FROM qry_A;
UNION SELECT ALL a1, b2 AS a2, b3 AS a3, a4, a5
FROM qry_B;
UNION SELECT ALL a1, c2 AS a2, c3 AS a3, a4, a5
FROM qry_C;
UNION SELECT ALL a1, d2 AS a2, a3, a4, a5
FROM qry_D;
UNION SELECT ALL a1, e2 AS a2, a3, a4, a5
FROM qry_E;
UNION SELECT ALL a1, f2AS a2, tblG.f3 AS a3, tblG.f4 AS a4, a5
FROM qry_F;
One solution I came across when searching the forums was to use an Append Query to append the query results to a table and base my report on that. This does indeed fix the problem.
But what I was wondering if it was is my query design that is causing it to be slow or is it just the fact that I am returning 12,000 rows?
:confused:
In case it matters, I wanted to mention that I can’t use the report wizard to create the report. When I select the union query, the fields will be showed for awhile then they just disappear. That in and of itself doesn’t cause any trouble since I am creating the report using the design view and not the wizard.
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
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.
Hi Guys,
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?
Any ideas or comments are welcome,
Thanks.
Hello,
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.
Many thanks in advance..
Nitesha
I have one PC running win98,acc97 and Access is CRAWLING. I'm seeing this on ONLY this one PC, and in multiple databases.
Any ideas? Possible places to look to fix this? I've tried reinstalling Office, I'd rather not have to rebuild this pc.
Thanks,
-Mike
I not a big time DB person so please be gentle. I am a computer technician at a school district and need some help. We use a program called Aeries made by Eagle Software for out attendance and grading. It uses an interface that runs through Microsoft Access. There are two version of this software. One is the SQL version and the other just uses an Access DB on a server. We have the Access DB version running Access 2003. It was explained and accepted in the beginning that we didn’t really need the SQL version because of our district size which is some where around 1500 students. Our district consists of 6 school sites that are all connected with a fiber optic backbone running at 200mb with a minimum of 100mb to each desktop. We have a DB for each site running on 1 very beefy server in a central location. I’ve run my problem by the software maker and don’t feel like I’m getting much help. The problem I am having is slowness during login. When the first user of the day attempts to login the program is fast, about 10 seconds from “ENTER” to the main menu. As soon as that 1st user clicks one of the menu buttons anyone else who attempts to login to that database after that experiences at least 30 seconds from “ENTER” to the main menu. I’ve watched what files are accessed and when. I see that when the 1st user logs in, the systemxp.mdw file is accessed and a systemxp.ldb file is created. Then when the user clicks a button on the main menu, the sch05xxx.mdb is accessed and a sch05xxx.ldb file is created. The sch05xxx.mdb file is the main database file for any particular site. The “xxx” would be the site number. As soon as that file is accessed or that lock file is created the user will experience the long login times and sometimes as long as 4 minutes. I don’t believe this is a hardware problem. During the same time that users are slow I have made a copy of one of the databases onto the same HDD on the same server and as long as I’m the 1st person to open it, it will fly. The 2nd person is slow just like the rest. I also don’t think it’s a permission issue, at least not in the domain. I’ve run all these test logged in as myself and have Domain Admin rights to everything. I believe this is a “split database” with the tables on the server and the forms, and queries on the users computer. I read an article here, http://www.microsoft.com/office/previous/xp/columns/column05.asp called “Why is Access 2000 slow for more than one user?” Has anyone else had this problem or have a solution? If I’ve missed something and you need more info please let me know.Thank you for your time and I appreciate any responsesJohn
View 2 Replies View RelatedI 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.
Hi,
I have an access database that runs fine on our current systems (mix of office 2002, 2003 and windows xp/2002.
However we have one new machine here running windows vista and office 2007 and the database runs super super slow. just about every operation takes 10 times plus to run!
I have looked at the sticky subject at the top of this forum and it's not the problem linked to here http://support.microsoft.com/kb/935366/ as this uis the only vista machine we have accessing the database. the same problem also occurs when running locally on my home machine (again vista and office 2007). From memory if is fine when running it through office 2003 on vista.
Anyone else have similar problems and find a solution?
Regards
John
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.
I am using MS Access 2000 in a Windows Terminal Server 2003 Environment and the performance is incredibly slower than running the same job on a Stand Alone CPU with Access 2003.
Any idea? Access 2000 is running slower on a TServer?
May the Access version difference explain the difference?
Any idea would be appreciated.
Thanks!
Is Access slower at summing null records than SQL server?
I have a query which takes less than 1second in SQL server but takes about 5-10 in Access but can't think why there is such a lag in processing speeds.
I have completed a database for a company with 60 000 clients and over 100 000 job records.
The database works at very resonable speeds on the Server computer, or the computer on which I installed the back end of the database.
Each of the other 3 computers on the wireless network, have a local copy of the Front End on their machine, and reference the Back End (BE) on the server computer.
Each of the Client computers have varing speeds when accessing the BE, some as slow as 10 minutes for a simple search, filter or just loading a form.
What can I do to improve performance across the network?
Do I need to install additional components on the other computers to improve the db performance?
Your help will be much appreciated.
Joseph.
I am using John Big Bootys Dynamic Search Form (built in Access 2003) as it totally met my search requirements, however when typing anything into the search box it is realy slow, taking several seconds for each letter to show in the box.
This has always been a problem now and again, however some of us have upgraded to Office 2010, (although still using the 2003 built Db as we won't move to a 2010 version Db untill March next year) and now the problem is even worse that ever. Everything else seems to work really well except for this search function, however it is the search function that is used more that anything else.
I recall reading somewhere (fairly recently) about a parameter or property in Access which is "On" by default and allows a form/query to identify when backend table field names or query arguments are changed and automatically looks for and "fixes" the renamed link. By leaving this switched ON, the database is slower than it could be. The db speed performance can be can be improved significantly by switching this 'feature' off. (i.e. Name of the property)
View 3 Replies View RelatedI have a database which tracks onsite and offsite files. I need to create a pop up message box using the toggle button function, with a "yes" or "no" response, so when a file is onsite and you click the offsite box, a pop up message box will display the following message, "Are you sure you want this file offsite?" and if yes is clicked, it will switch to the offsite field. If no is clicked, it stay as is, (onsite would be the default). The same thing applies for when a file is offsite and you want to return it onsite, when you click in the onsite box, the pop up message box would display "Are you sure you want this file onsite" if yes is clicked, it will switch to yes, and if no is clicked, it stay as is. Can this be done?
Thanks!
In my database I have many names in alphabetical order. What I want to do is create something where, for example, if I type a 'B' in a text-like box the list of names from the database automatically scrolls/moves to the beginning of the Bs either in table view or preferrably in another text-like box that mirrors the table contents. Then if I enter an 'E' after the 'B' the cursor moves to the beginning of the names with 'BE' in them, and so forth for each additional letter. Each move would happen immediately after each key is pressed, without using the <enter> key, probably like a 'keypress' event.
I would appreciate some direction or if someone knows where there might be some existing code or examples.
Thanks ahead of time for your effort!!!
Looking for a way to supress this message box...
The text you entered isn't an item in the list.
Select an item from the list or enter text that matches one of the listed items.
... when the NotInList event fires.
Trying to just open an unbound modal form, pass a couple values to it, and set focus on a particular combobox for additional information to be added to my lookup table:
Private Sub cboPostalCode_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ErrorHandler
Const Message1 = "The postal code you have entered is not currently on record."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown Postal Code"
Const NL = vbCrLf & vbCrLf
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
DoCmd.OpenForm "frmPostalCode"
Forms!frmPostalCode!cboCountryID = Me.cboCountryID
Forms!frmPostalCode!txtPostalCode = NewData
Forms!frmPostalCode.SetFocus
Forms!frmPostalCode!txtCity.SetFocus
Else
Me.cboPostalCode.Undo
End If
Exit_ErrorHandler:
Exit Sub
Err_ErrorHandler:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Sub
Any thoughts?
Regards,
~Chad
I have an 18.8MB Access application consisting of the following:
140 tables
410 Queries
67 Forms
5 Macros
26 Modules
It's not a lot of data storage, but is very computationally and mathematically intensive. As we continue to develop and expand the application, I noticed last week that we suddenly experienced a massive falloff in performance. Access is taking an incredibly long amount of time to traverse the querydefs collection, find objects, etc. It seems that the object collections themselves are not indexed, meaning that it takes a much longer amount of time to run a compiled and saved query than it does to simply build the SQL string and execute from within code. I continue to hunt for coding bottlenecks and any other efficiency problems I can find. Has anyone else experienced this? Is there a "critical mass" for the object containers?
Here's my crosstab report
Answer Name1 Name2 Name3
Yes 90 95 85
No 10 10 35
Total 100 105 120
I need to devide the number of Yes responses for Name1 by the total number of responses. I tried doing it like this =(([Name1]=Yes)/sum([Name1])), but that isn't working.
Thanks in advance,
Dave
I have a table of issues (tblIssues). Each issue has legislation that it must be dealt under. Some issues relate to more than one piece of legislation. Some legislation deals with more than issue. Ideally, therefore, I'd like to have a separate table of legislation (tblLegislation).
Where any issue refers to two (or three) pieces of legislation, can I store that in a single field? Or do I simply need to create a record in tblLegislation for the combined legislation?
Hi, All--
I am designing a database to capture the data of returned surveys. I want to design the database to facilitate data analysis through crosstabs or other aggregation queries.
If I design a table where each record is the complete survey responses to all survey items in a returned survey, this is not friendly for such query analysis. (In this, each field would be a survey item). Call this the horizontal method.
The other way would be to have a reference table containing the survey items , and have responses entered in a seperate table linked by item id and response id (from a third table containing a record for each submitted survey). Call this the vertical method. This would take more time to set up but would probably be easier to query.
The item response table would become quite long contaiging every item response for every survey turned although each record is short.
Does anyone have any opinion on this, or perhaps a completely different approach that I haven't thought of that would be easy to set up but also easy to query?
Thanks.
Paul
Hey. Basically I have several tables. One table (vistiors) has a combo box in one field called Hostee. Each hostee is listed in another table with all their information. When a hostee is selected in the hostee field of the visitors table I simply need it to count how many people including the currently selected person are being hosted by the person and check if it is greater or less than the amount of people set in the host table which has a field called Limit that has a number which is the maximum amount of people one person is willing to host.
If its greater than the amount in the host table a warning message should come up informing the person (but still allow the assignment to occur.)
As a note I have the combo box set to show the first name of the individual but select both the first name and ID
the code is below for the row source:
SELECT [Hosts].[First Name], [Hosts].[id] FROM Hosts;
If someone could tell me how I could get it to show multiple things within the combo box that'd be great.
IE Right now it shows just a first name liek Alex, Rachel, Bill. I'd like it to show the first name as well as the last name and a third additional field. IE Alex Lastname T1 or Rachel Lastname F4 etc.
Can anyone show me the general code to do this? I do know some VB but I'm used to using SQL with PHP primarily. I am new to access and only know it vaguely. Thanks in advance!
I have a form that records followup activities for protocols. The form allows the user to enter up to six f/u actions from a dropdown menu in six separate fields (called fu1_action, fu2_action, etc).
I am trying to write a query that retrieves only protocols that only have one f/u action selected as "telephone correspondence" and am having trouble. It seems I could write a function in VBA that would somehow loop through the 6 f/u action fields and give a value of 1 to those that have 'telephone correspondence' as a response, and then add them up to give a total of calls for that specific protocol, and then in a separate query only retrieve those that have a total number of calls = 1, but how to get started in writing this.