Convert Access to Excel (Excel 2003/2007)

Topic

New Reply

I am Secretary of a District Organisation which has about 11 smaller organisations under its control. Each organisation has a membership list. The District is also required to keep a membership list. Some members are members of more than one organisation within the district. Members have a unique Id No, set by a state body. Currently I use an Access Database to record the District Membership. This is done by a Members Table which records all the Members Details; an Organisation Table which keeps all the details of the District Organisations; and a Membership Table which simply cross references Members Id with Organisation Id. Thus member 1234 can be a member of Organisation AB and AC by two records in the Membership Table.

Now for the problem.

The committee has recognised that future Secretaries may not be conversant with MS Access or have MS Access on their computer, and have requested that the database be converted to MS Excel. I recognise that MS Excel does not generally use relational data like Access. While I could have a separate Tab for each organisation, listing all their details, an individual who is a member of more than one organisation would be duplicated on another tab. This would make Membership maintenance messy. If on the other hand I try and set up a tab similar to the Relationship tab, I am unsure of what approach I would need when creating an Organisation/Membership reports.

Can any kind soul suggest how I might use Excel in a relational manner.

Reply | Quote

Replies

Excel is a fine program, but it is *not* a relational database application.

If the Access database has been designed properly, secretaries shouldn’t need to be conversant with Access. You can hide the entire machinery behind a simple, user-friendly interface and still use the power of queries, referential integrity etc. The secretaries would need to have Access installed on their computers, or the runtime version of Access.

It’s possible to mimic a relational database in Excel, but it’s a lot of work, and I suspect that the users would have to understand a lot more of the underlying structure than with Access.

So I strongly recommend staying with Access.

Reply | Quote

Yeah! I know about Access and you know about Access, and then there are those in positions of power…..
I wish I could stay with Access, but I have been instructed to go the Excel Route, but I may have thought of a way by using a VBA spawned form. Maybe if i ….. Hmm Let me ponder this further.

Thanks anyway, will get back to you if I need more advice.

Reply | Quote

Hi Hans,

Forgive my ignorance, but can’t you create an MDE file from an access database and “run” that on a machine without Access? Or is this outdated information I have?

Reply | Quote

To open an .mde file, you still need Access or Access Runtime installed. An MDE database is just an MDB file from wich the VBA source text and the permission to design forms etc. has been removed; it is not a stand-alone application.

Reply | Quote