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.

 

[Joe4]

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.

 

[TechnoWay]

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.

 

[Joe4]

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).

 

[TechnoWay]

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.

 

[Joe4]

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.

 

[TechnoWay]

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?

 

[Joe4]

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.

 

[TechnoWay]

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.

 

[Joe4]

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.