472,133 Members | 998 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

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

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!

Sep 17 '07 #1
1 2179
3,080 Expert 2GB
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 ?

Oct 5 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by jillandgordon | last post: by
5 posts views Thread by Simon | last post: by
3 posts views Thread by =?Utf-8?B?YzY3NjIyOA==?= | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.