MS Access : Drop down List is too slow

Question

I created a drop down list in MS Access (Form) based on a Query (multiple tables), the problem is it takes about a minute to open the drop down.
If I open the Query it opens instantly.

I found out what is making the Drop down to respond so slowly. It's a criteria that I have in the Query:

In (SELECT [ObjectID] FROM [ObjectDetail] As Tmp GROUP BY [ObjectID] HAVING Count(*)>1)

If I remove this Criteria from the Query, the drop down works perfectly and instantly.

So my question is this: Is there any way to make the drop down respond quicker, while still maintaining the criteria?
This is how my drop down Query looks like :

SELECT Version_Change.ObjectID, Version_Change.Key FROM Version_Change 
UNION 
SELECT "(All)" as ObjectID,"" as Key FROM Version Change;

there are several (+10k) Record in the table that I'm querying , but the final result of the query only shows 10 records. — adp, Jan 18 '16 at 15:38
is there a network involved, or just a stand alone desktop database? — Albert D. Kallal, Jan 19 '16 at 00:24
No.The tables that i query , are local tables so on the desktop.The query runs just fine..and quick..the problem is at the dropdown. — adp, Jan 20 '16 at 15:43

score 0 · Answer 1 · answered Jan 18 '16 at 15:07

By basing the second UNION part on the complex query, and then filtering out duplicates with UNION instead of UNION ALL, you add unnecessary complexity.

Try this:

Create a dummy table DummyOneRecord with one field (doesn't matter what) and add one record.

Then use (note the UNION ALL)

SELECT Version_Change.ObjectID, Version_Change.Key FROM Version_Change 
UNION ALL
SELECT "(All)" as ObjectID, "" as Key FROM DummyOneRecord;

MS Access : Drop down List is too slow

1 Answers1