Export to Excel, update by other User and Import back to Access

[Pauline]

Dear all,

I have an enormous database (Access 2003) containing sales information, and an Excel tool to enable end users to do planning and forecasting. Untill now I would create several queries, export the data to Excel (Excel 2003) and store the Data in the tool for the users to work with. I always have to export all information for a group of users as I never know on what part of the data they want to work. This makes the Excel files very slow however (to much data, in general over 40.000 lines).
I know there is a possibility to import data in Excel from Access and you can refresh the data in Excel with new information in Access. So possibly I can use this, by having the end users run a query that will only pull the data they need at the time. The only thing is, the changes they make will then also have to be uploaded/ refreshed in Access too. Unfortunately my end users do not have a clue about Access and very limited knowledge about Excel. So all will have to be put in an VBA macro so they can action things with one click of a button.
Any clues from your side will be highly appreciated!

Thanks,
Pauline

[nico5038]

Hi Pauline,

Guess this can't be solved in an easy way when you want a 100% solution.
Your present application solution will have trouble when multiple users update the same information (ID) with different data....
It would be best to move all tooling of Excel into Access and have the users work with an Access frontend.
The other solution should be to split the original data into "unique" sets for Excel per user and thus prevent duplicate updates.

The last solution will be the easiest, but also requires the users to be arranged to handle one of the sections. When multiple users have to manage the same information, they'll have to assign one that's responsible for the final dataentry.

When you can divide the data in this way, then all needed will be to add a field ResponsibleUser to every unique datarecord and split the data into sheets per user.

How does that look like ?

Nic;o)