Some Formulas Make Access Quite Laggy

[TechnoWay]

Hello, I have Excel spreadsheets linked to my Access and I'm using DCount formulas in a query. When i run the query it takes like 5+min for the query to finish and Access is completely unresponsive during this time. Is this typical for Access? Is there a way to make this faster? Thank you.

 

[Jimmywick]

DCount is a domain aggregate function and can be slow at times. I suggest that you use code (DAO or ADO depending on the version of Access you are using) to see if the particular record the user is entering already exists in the table.

Regards,
Jimmy

 

[Micron]

I would take the DCount out all together and run it. If it still takes a long time, it's not the aggregate function that's the main problem. It could be something else, such as lack of indexing or the query is too complex and should be broken into steps. A DAO recordset might be faster, and you can get the count of records returned to it. I'd remove the function and test first.