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