?
Solved

Make a local ACCESS 2003 db run faster

Posted on 2009-07-08
6
Medium Priority
?
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 1000 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 85
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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

762 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