At 130k Records Am I Asking Too Much of Access - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

At 130k Records Am I Asking Too Much of Access

Sandi Cushion

14 months ago

I am currently using Access to monitor staff work – we have about 1800 staff carrying out case working activities. However, I am now looking to incorporate the case work but there are usually 130k of workable cases at any one time. The case lifetime can be short or long, and it will progress through about 5 stages from start to finish. I want to monitor the stages and analyse the data to capture trends. I am using basic tables and relationships and have recently started creating queries for my reports to update, append and log changes etc, but the queries are taking a humongous and laborious amount of time. Is this because I am asking too much of Access? Do I need to upgrade to SQL Server or similar?

	Joe Holland @Reply 14 months ago
I have hundreds of thousands of records and Access is fast. Can you share screenshots of your queries so we can help answer your question?

	Alex Hedley @Reply 14 months ago
I depends, if you have the correct indexes setup and you are archiving old data then there's no reason why it can't handle it. Yes SQL Server will handle it better but then you'll have latency considerations, although depends if your current setup is on a server and split.

Matt Hall @Reply

14 months ago

What Joe and Alex said.

Be aware that as you increase record count, any inefficiencies will be magnified by the volume of records to evaluate. A couple of weeks ago, I had me.refresh accidently sitting inside a while loop that ran 1.9 million iterations. That took about 14 hours (overnight) to run. It took about 10 seconds to run once I corrected what I had done.

I have also struggled with queries where I failed to reduce the record count before evaluation.

Richard Rost @Reply

14 months ago

It's not about the number of records; it's more about the structure of your database (as the other guys have mentioned) and the size of those records. You could have 1,000 records that are really large with lots of data and perform slower than 100,000 records with only a few fields in them. As long as everything is indexed properly, you should be okay. I've got a database that I use that has well over a 300,000 records in it and it performs just fine even over a network.

The beautiful thing about Access is that if you do notice it's starting to get slow and you've optimized it as much as you can, you can always upgrade the back-end to SQL Server, and I've got lots of videos to help you with that process.

	John Davy @Reply 14 months ago
Hi Sandhi, Have you gone to design mode, database tools and run the Analyze utility? Check your indexes as well. What Rick said is so true. One has to examine the structure to optimize performance. John

Sandi Cushion OP @Reply

14 months ago

Hi everybody, thank you all so much for your replies. I just had to rule out that possibility. I have been using Access for many years albeit on small databases and I've been harping on at my boss to let me show them what Access can do and how wonderful it is. I just didn't want to have egg on my face! So now I am going back to the drawing board and check everything and get this thing up and running the way I know Access can.

	Richard Rost @Reply 14 months ago
Awesome. We're here if you need help.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View