Solved

Make a local ACCESS 2003 db run faster

Posted on 2009-07-08
6
233 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 40

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

825 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