Web Development

Ask something
Question Locked

MS Access Query Execute Time

0 helpful
5 replies

by pd_2004

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello,

I have a very simple Access database with one large table (1.6 Million records x 12 fields). I am trying to run a simple summary query to summarize six fields of the data by grouping the first five and summing the sixth. It is a n append query. The problem is that it runs forever, and doesn't execute. I have checked, and there are no errors in the query itself.

My question: in the task manager ASACCESS.EXE is using between 01 and 05 percent of the CPU. Is this a RAM shortage issue, or is there a way to allocate more resources to Access. I currently have 1.96 GB of ram on a 2GH laptop PC. Any help is sincerely appreciated.

Thank you,

Pat

5 replies

by tony hopkinson

Can't see why append would matter, but change it to normal one and see if that makes a difference.

If I was taking a wild guess I'd suggest there were no indexes on the table. That much data they will take a while to build (do one for each of the fields you are grouping by) , but it will be faster.

That much data in access is pushing it to it's limits in my opinion. Take regular back ups it's got a real bad habit of corrupting the MDB file.

by pd_2004

OK Tony. Thank you for all of your help.

Pat

by pjroutledge

There are probably a range of possible issues, but here are some things you might try:

1 Ensure that the source and destination tables are local, ie not on a network drive.

2 Break the query down into steps, eg from what you've described you could make step 1 a SELECT query (including the GROUP BY clause) and then if step 1 works, try a new query to APPEND the results to the destination table. You might even consider creating an interim table for the results of step 1.

3 Check the size of the Access .mdb file(s) you're using. Remember that Access can only handle files of limited sizes, the size depending on the version. Eg Access 2003 can only handle 2Gb files. If your source table and destination table are in one file you might be hitting the limit, so move one of the tables to another file.

Cheers,

Peter

by pd_2004

Thank you, Peter. I have been working in just the direction you suggested and I am having some success.

This discussion is closed to new replies.