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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Some Formulas Make Access Quite Laggy
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
Regards,
Jimmy
Last edited by a moderator:
Upvote
0
Excel Facts
Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
Upvote
0
Similar threads
- Replies
- 4
- Views
- 400
- Replies
- 3
- Views
- 684
- Question
- Replies
- 5
- Views
- 3K
