Solved

Make a local ACCESS 2003 db run faster

Posted on 2009-07-08
6
231 Views
Last Modified: 2013-11-25
I have a client that users ACCESS 2003 on local machines.  Problem is that they are processing MILLIONS of records.  They have so many that they have created a series of databases that hold only one table - which can be up to 5 million records.  So, the "processing" database has links to all these "child" databases but queries can take an incredibly long time.  The machine has almost 20 Gig of free space and 2.5 gig of ram.  They DO NOT want to put anything on a SQL server (my FIRST suggestion).  Tables are indexed, but would more ram help?  I am at my wits' end as these people will not change but also insist it should run faster.  Speed is 2992 MHz. XProfessional, SVP 3.  I have contacted their IT department and first thing out of their mouths was put in on a SQL server.  I just want confirmation that there is nothing that can be done to get the application to "run faster".
0
Comment
Question by:ssmith94015
6 Comments
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 24808391
could try compacting the databases.  What about cleaning up old data?  

In general though, for something this big SQL is the way to go.  

Also just to point out this is on a workstation . . . please tell me they're backing it up somehow??
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 24808581
Reviewing and changing those indexes might help, but with this architecture I'm not sure how effective it would be. Cleaning the data might help, but you'd have to remove a LOT of it before you'll see improvements.

If their IT department suggests that the put it on SQL Server, and you suggest they put it on SQL Server, but they don't put it on SQL Server ... sounds like the problem is between the keyboard and the chair :). IOW, send an email stating your case, including the support from the IT dept, and tell them all has been done that can be done. Make sure to send this to someone very high up on the food chain in that company.

0
 
LVL 75
ID: 24809087
'between keyboard and the chair" ... =  vacuum ?

mx
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Expert Comment

by:jmoss111
ID: 24809092
Definitely a short between the seat and the keyboard.
0
 

Author Comment

by:ssmith94015
ID: 24809155
NO THEY ARE NOT, I REPEAT NOT backing up!  I backup everything so they will have a backup when I leave (as well as my own shell of the database).  There is no old data as it is all live and accumulates during the year, at year end, all is cleaned out and they strart over again.  
Yes, it is a problem between the chair and keyboard.  I am dealing with two very arrogant men who think no one could possibly know anything, including neither me nor the IT department.  Of couse, the lady I am working for does not know anything either, but it is not her job to,  and they keep telling her that all I need is a "better" machine.  I just wanted to be sure I was not missing anything in case there may be an option, but it looks like you both only confirmed what I thought.  At least got a copy on the shared directory that is backed up, but can do nothing about their local drives.  They even refuse to listen to suggestions about improving what they have, so I have given up and am doing it "their" way.  I am going to split points as there was really no "answer" but only a request for confirmation of what I thought.  
0
 
LVL 84
ID: 24809204
I'd certainly send that email ... you really should cover your butt and make sure that, when this thing falls over (and it will) you can prove that you made EVERY effort to safeguard the data and were roundly ignored. Could be the difference between who gets the big pink paper ...
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now