Access to Excel is getting slower (1 Viewer)

EmileEA

Registered User.
Local time
Today, 23:27
Joined
Apr 7, 2014
Messages
17
Hi,

I'm currently working at a company where they make use of MS Access for their database. I work with an access database which my predecessor made. The database, files all sales data of the company. I file the sell-in and sell-out data on a weekly basis. This database goes back to 2009. It's a large database (390MB) and its getting bigger every week.

My problem:
After I have added the weekly sell-out data in MS Access. I go to an excel sheet and press refresh, by doing so, the last added week of sales is added in the excel sheet. Within the excel sheet it makes calculations of the top 20 sols items this week, the market share per week, the sold items of the total market accrued as weekly etc. In short I could say, it makes a lot of calculations. The generated tables and graphs that excel automatically makes after the refresh are send out to my global partners. Everything works perfectly except the fact that it takes 50-60 minutes to do this refresh.

When I started working here a year ago this refresh in excel was done within 5 minutes. But every week it took a minute longer and longer. My PC is so busy calculating that it occupies the whole PC.

Since my predecessor left me a bit to soon, I did most learning of Access by trial and error.

Personally, I'm thinking of separating the sell-in and sell-out data in two different MS Access files since they have no relation within Acccess. When divided they will both be around 200 MB. But will this speed it up? Or is this just a temporary way of speeding things up. Or is this not the problem at all?

I'm out of solutions on this. And I hope that some of you wizards can help me with his/her magic.

For the record I'm using office 2013 and have no experience with VBA

Many thanks!:)

kind regards,

Emile
 

spikepl

Eledittingent Beliped
Local time
Today, 23:27
Joined
Nov 3, 2010
Messages
6,144
You need to have indexes on all fields used in sorting, grouping and WHERE criteria. You likely do not have them , considering your symptoms.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:27
Joined
Jan 20, 2009
Messages
12,849
In addition to Spike's sound advice you might also have problems with inefficient queries.
 

EmileEA

Registered User.
Local time
Today, 23:27
Joined
Apr 7, 2014
Messages
17
You need to have indexes on all fields used in sorting, grouping and WHERE criteria. You likely do not have them , considering your symptoms.

Thanks for the quick reply Spike!

The indexes option does not show in my query. I'm a complete noob at this, are indexes the only an dmost efficient way?
 

namliam

The Mailman - AWF VIP
Local time
Today, 23:27
Joined
Aug 11, 2003
Messages
11,696
Indexes are defined on the tables that source the queries
 

EmileEA

Registered User.
Local time
Today, 23:27
Joined
Apr 7, 2014
Messages
17
Indexes are defined on the tables that source the queries

I found it and indeed, I'm not using any indexes except for the primary key. Will it make the query faster by switching indexed to yes (with duplicates) and in ascending order on the columns which haven't been indexed?
 

namliam

The Mailman - AWF VIP
Local time
Today, 23:27
Joined
Aug 11, 2003
Messages
11,696
Depends on where the slowdown is actually occuring, to me it sounds like it is in excel... which would mean "no"

But spike and galaxiom seem to think it is in access, if it is, that would mean "Yes"
 

EmileEA

Registered User.
Local time
Today, 23:27
Joined
Apr 7, 2014
Messages
17
Depends on where the slowdown is actually occuring, to me it sounds like it is in excel... which would mean "no"

But spike and galaxiom seem to think it is in access, if it is, that would mean "Yes"

Thanks for your quick reply

My knowledge of excel is better than access, do you have any suggestions of how to prevent the slow down in excel?
 

namliam

The Mailman - AWF VIP
Local time
Today, 23:27
Joined
Aug 11, 2003
Messages
11,696
Two major reasons of slow down in excel are
1) Screenupdating
Add
Application.screenupdating = false
at the start of your process and set it back to True at the end....

2) Complex formulas
You solve this by either fixing the formula's to be faster (or get the proper data in a better way from access)
Alternatively you can turn of auto-calculation while you are importing data from access and turn calculations back on when all data in place. This will prevent the same formula being updated time and again.

I think that is
To switch to Manual (i.e. no update till I tell you too): Application.Calculation = xlCalculationManual
To switch to Automatic (default): Application.Calculation = xlCalculationAutomatic
 

spikepl

Eledittingent Beliped
Local time
Today, 23:27
Joined
Nov 3, 2010
Messages
6,144
assuming the calculations concern some given repetitve time period, then a huge rise in processing time cannot be due to calculations in Excel, since the total count of calculations is likely roughly the same from one week (month) to the next.

Collecting similar amount of data from Access table week by week (month) but without indexes set, does require more and more time, the more records are present.
 

Users who are viewing this thread

Top Bottom