Make a local ACCESS 2003 db run faster

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".
Sandra SmithRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
'between keyboard and the chair" ... =  vacuum ?

mx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jmoss111Commented:
Definitely a short between the seat and the keyboard.
0
Sandra SmithRetiredAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.