Linking excel avoiding redundancy

[GenericHbomb]

My users are having a little bit of trouble getting used to access and as a result have continued relying on excel. What they wanted me to do is make it so they can use the access database i created but still enter the data in excel.
The only thing I can think of would be creating a link between the two but there would be the problem of redundancy.

Each company has submits a plan to us. If there plan doesn't meet standards they fail and have to submit a second or even a third plan. So each company has the potential to submit multiple plans.

Here is the basics of how it looks in access
TBL: Company
Primary key: CompanyID
Carrier name, company number, address, city, state, zip

TBL: Plan
Primary key: PlanID
Secondary key: CompanyID
Received date, date sent, status, submission number

In excel it is all in one sheet so whenever a company submits more than one plan there is redundancy.
Is there anyway to make this same kind of set up using a link from excel?
THANKS!

[Rod]

By 'redundancy' I assume you mean that the data is not normalised in Excel.

There is also another type of 'redundancy.' Your data appears both on an Excel worksheet (which by default is a data store) and in Access tables. No way round that I'm afraid unless you programmatically prevent your users from saving the workbook or delete it if they do. If you don't, then sooner or later a user will (re)open a workbook in Excel and make changes. Now you have not just duplication but different versions! All a bit messy in my opinion.

What you want to do is feasible but I think you are in for some tedious work making Excel simulate an Access form. You have to think of a way to allow your users to update company data and make sure that the updates take effect on every row of the worksheet. You have to prevent your users from manipulating the worksheet too much: if they insert/delete rows/columns then, for example, cell D5 no longer contains what you placed in it. If a user defines a formula do you want to capture the fact and reproduce it next time the worksheet is generated? Etc.

The flexibility to manipulate worksheets is what users love about Excel. Take that away from them and I think you will suffer more than a few complaints. The usual scenario for an Access-Excel link is where Access pushes data into Excel and then 'forgets' it; users are free to do whatever they like with the resulting workbook.

OK there are two basic concepts that you can use: Access pushes and pulls the data or Excel pushes and pulls the data. From your write-up I believe you are considering Access to be the 'manager.'

I don't know your level of expertise but I would respectfully suggest the following as minimum requirements before you embark on this.



Rudimentary knowledge of:

• Objects (particularly properties, methods and classes/instances) and Object Oriented Programming (OOP);
• Component Object Models (COM - Automation) in general and the Excel COM in particular;
• VBA coding and navigating the VBA coding window;
• Use of the File Scripting Object found in Microsoft Scripting Runtime (for basic file and directory management).

You may at some point get into the realm of making Windows API calls.


I'm not trying to put you off. OLE or Automation or COM or whatever Microsoft chooses to call it these days is a powerful tool but I want you to realise what you are undertaking, not so much because it's difficult but because you need to protect the integrity of your data.

Let us know and we are here to help.

[GenericHbomb]

Based on what you said and what I have read I would rather my users move completely to access. I am sure the initial move will be tough but if I remove their reliance on excel it should work out.

[Rod]

For the situation as you describe it, Access forms are the way to go. Practise your sales skills and convince your users.

[GenericHbomb]

It is done. We are moving over to Access within a week. Assuming I can figure out all the little bugs and quirks that keep coming along. Thanks!