Access 2010 Not Responding Message Running Queries Thru Forms

[JMarcus]

I have just one table with more than 300 records with queries set up. The queries are run from form date category and button with drop down to select category. Unfortunately, I keep getting the not responding message and hanging when running it although it completes it. Any suggestions or a way to replace the not responding message with something else through VBA. Please let me know.

 

[Minty]

I think you need to add some indexes to speed your queries up. Can you show the SQL of the query.
They should not be slow with only 300 records. What type of fields and data are you querying?

 

[JMarcus]

Typo 300,000 records. It is a linked table so I don't have control of the table.

 

[JMarcus]

I don't have the problem with it when running only the query. It is when I run the query through forms

 

[JMarcus]

Here is the SQL

SELECT All_Cars.[Sub #], All_Cars.[Name], All_Cars.City, All_Cars.State, All_Cars.Zip, All_Cars.[Term Date], All_Cars.Plan, All_Cars.[Plan Name], All_Cars.[ Eff], All_Cars.MFORM, All_Cars.LMAT
FROM All_Cars
WHERE (((All_Cars.[T Date]) Is Null) AND ((All_Cars.[Category Name])=[Forms]![frmPlanNames&EffDate]![Combo51]) AND ((All_Cars.[True Eff])=[Forms]![frmCategoryNames&EffDate]![EffDate]));

 

[gemma-the-husky]

I think Minty has it.

If Access can't find a "quick" index to use, it will need to scan every record.

 

[JMarcus]

How should I handle it and I index it? I dont have control of table as it is linked. It is one table with about 20 fields. Queries are run from the one table. I only have oroblem when running queried through forms

 

[JHB]

Have you tried to replace the references to the form objects/controls with hard coded values?

 

[Minty]

What happens if you import the table to a local table and add indexes, make that table the record source for the form. Do things improve?

 

[JMarcus]

Ive done everything I could to fix this issue. Is there any way to hide the Not Responding message?

 

[Minty]

Did importing the table and adding indexes make no difference ? I find that hard to believe ?

 

[JMarcus]

So your saying instead of linking to the table directly, import the table daily into the database and create indexes on the table daily?

 

[JMarcus]

You were very right. I ran a test imported the data and indexed one of the signifigant fields and ran the same queries, forms, and vba and it ran so much faster. Now I just need to find out how to auto import the table daily in the morning without having outlook. Thanks so much!

 

[Minty]

If you can - append the new data to your existing import table, if that simply isn't possible then create the table add your indexes, then each day delete the records and re-import by appending, this way the indexes stay in place and up to date.

It may be worth compacting the database between deleting and appending to remove any database "bloat" that might be caused as you have a lot of records.

 

[gemma-the-husky]

is the linked data in a system your company doesn't control?

is there no way the host providers could add the index to the data table?

 

[gemma-the-husky]

maybe you could split your query into 2

eg

SELECT All_Cars.[Sub #], All_Cars.[Name], All_Cars.City, All_Cars.State, All_Cars.Zip, All_Cars.[Term Date], All_Cars.Plan, All_Cars.[Plan Name], All_Cars.[ Eff], All_Cars.MFORM, All_Cars.LMAT
FROM All_Cars
WHERE (((All_Cars.[T Date]) Is Null)

do this first. This will probably produce a data set much smaller than 300000 items.
If necessary, store this data in a temporary table.

then use the resulting query/table as a base for your next query.

Access maybe able to do it a lot quicker this way. Sometimes the way it tries to build an entire query is not the most efficient, and you can help it along, by redesigning the query.

You could research "query plans"

 

[JMarcus]

I need it automated so will be hands off it once Im done. Im going to try and have the original table indexed. Seems like the easiest and best approach. Otherwise I would have to write vba to auto import the table and index it daily. They do not have outlook for tasks.