Solved

Make a local ACCESS 2003 db run faster

Posted on 2009-07-08
6
232 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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

22 Experts available now in Live!

Get 1:1 Help Now