Some Formulas Make Access Quite Laggy

TechnoWay

New Member
Joined
Jul 11, 2022
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
DCOUNT functions aren't the most efficient and can sometimes be troublesome.
You may be able to do what you want with an Aggregate Query instead of using DCOUNT.
If you post a small sample of your data, and show us exactly what it is that you are trying to do, we may be able to come up with an alternate solution for you.
 
Upvote 0
DCOUNT functions aren't the most efficient and can sometimes be troublesome.
You may be able to do what you want with an Aggregate Query instead of using DCOUNT.
If you post a small sample of your data, and show us exactly what it is that you are trying to do, we may be able to come up with an alternate solution for you.
Hey there, I'm trying to count the number of times a specific code is repeated in the same column, similar to COUNTIF in Excel. Everytime i run the query and scroll towards the last field, Access becomes unresponsive which makes me think it's the formula that's using so much resource.

Also do you think linking or importing spreadsheets is faster for running queries? I have lots of spreadsheets linked to Access because these spreadsheets will be frequently updated over time, so I went with linking, but I'm thinking it contributes to the lagginess.
 
Upvote 0
Can you post the things I asked for (sample data & formula)?
If this is in a query, you can simply change the Query to SQL View and copy and paste that code here.

I really want to see what your data looks like. From the sounds of it, it sounds like we may be working with a data set that is not Normalized.
Working with data in Access that is not normalized can be VERY cumbersome and problematic.

Also, if the data is in Excel, and you can do a COUNTIF function in Excel, why are you trying to do this in Access instead?

One very important thing to understand is that Excel and Access are NOT interchangeable programs. They are built for very distinctly different purposes.
Access is a relational database program that works best when data is organized in a Normalized manner.
Excel is a spreadsheet program.
While you can design Excel to work like a relational database, it is usually very cumbersome and may be slow/inefficient.
And while you can do some Excel-like calculations in Access, it is really not designed to handle certain Excel functionality easily (like summing across fields).
 
Upvote 0
Can you post the things I asked for (sample data & formula)?
If this is in a query, you can simply change the Query to SQL View and copy and paste that code here.

I really want to see what your data looks like. From the sounds of it, it sounds like we may be working with a data set that is not Normalized.
Working with data in Access that is not normalized can be VERY cumbersome and problematic.

Also, if the data is in Excel, and you can do a COUNTIF function in Excel, why are you trying to do this in Access instead?

One very important thing to understand is that Excel and Access are NOT interchangeable programs. They are built for very distinctly different purposes.
Access is a relational database program that works best when data is organized in a Normalized manner.
Excel is a spreadsheet program.
While you can design Excel to work like a relational database, it is usually very cumbersome and may be slow/inefficient.
And while you can do some Excel-like calculations in Access, it is really not designed to handle certain Excel functionality easily (like summing across fields).
Hey thanks for the response! I'll try to find another way around instead of the formula.

Besides that, could you tell me in general what's the faster option for queries? Linking or importing spreadsheets?

Thanks.
 
Upvote 0
I'll try to find another way around instead of the formula.
We can help you do that if you post what I asked for.
Aggregate (Totals) Queries are a good candidate if your data is normalized.

Besides that, could you tell me in general what's the faster option for queries? Linking or importing spreadsheets?
It probably depends on a lot of things, like how much data we are talking about, your connection, your computer, etc, but in general, I think working from an imported table would be fastest, especially if you have set up indexes and key fields on your table.
 
Upvote 0
I think working from an imported table would be fastest, especially if you have set up indexes and key fields on your table.
One thing i should point out is that these spreadsheets will be updated frequently (only the values, not rows or columns). So I want the data in Access to be updated with the most recent data in the spreadsheets. So how can i import but also keep them automatically updated at the same time? Would that be VBA territory?
 
Upvote 0
One thing i should point out is that these spreadsheets will be updated frequently (only the values, not rows or columns). So I want the data in Access to be updated with the most recent data in the spreadsheets. So how can i import but also keep them automatically updated at the same time? Would that be VBA territory?
If that is the case, then it would make more sense to probably keep them linked instead of doing constant imports.
 
Upvote 0
If that is the case, then it would make more sense to probably keep them linked instead of doing constant imports.
Thank you so much for the responses! Also if you wanted i could send the SQL code in private to see if you have any tips for me, since I'm still new. I couldn't post here since there are some sensitive info. If not, all good.
 
Upvote 0
Thank you so much for the responses! Also if you wanted i could send the SQL code in private to see if you have any tips for me, since I'm still new. I couldn't post here since there are some sensitive info. If not, all good.
The SQL Code of your Query doesn't contain any data, just the names of the fields in the Tables. So usually that does not contain any sensitive data.
If that is indeed true, you should be able to just copy/paste the SQL code here.

The sensitive part usually lies in the data in the table.
Can you mock-up some dummy data? Since the underlying data resides in Excel, I don't imagine it should be to hard to post either some de-sensitized data or dummy data. I only need about 10 rows.

If you are not able to post the SQL Code of the Query for some reason, demonstrate what it is you are trying to do from your dummy data.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top