Is it possible to use MS Excel as front and back-end for MS Access?

Question

in a few words, I would like to use MS Excel together with MS Access, such that Excel is the front and backend, but Access provides the capability for relations between data and sql queries. Is that possible?

In a few words more, I would like to use MS Excel together with MS Access such that :

• the 'raw data' is visible and accessible from Excel ((write, modify, delete data entries!)
• I can use SQL queries to query my data via MS Access (and hence dynamically create different views on my data as excel sheets)
• I can implement relations between the data via MS Access (for consistency checks, etc)

My own research got me here:

• I know how to set up access to use an excel table as its data backend (both, linked to or copied from excel)
• I know how to send SQL queries from Excel to an access database and display the results in excel sheets

However, here is what's missing:

• in Access if I use tables that are linked to Excel, I cannot create relations between tables (access says it's not possible)
• in Access if I use tables that are imported (copied) from Excel, I can create relations between tables, but after creating relations I cannot update my import anymore (access says it's not possible, I'd have to remove the relations before)
• In any case I have to trigger updates manually ...

Background:

Excel is currently used for project management. The management process often involves 'exploring the data', e.g. assigning people different projects, adding people, adding projects, ... and then seeing how that affects other things. (Hence) it is required that Excel is kept as the platform. But it would be nice to have database feature like linked tables and queries, as data is getting too complex to manage with excel only tools. Also, this is not a large project, but I'm aiming for an easy and not complex solution.

Basically I want to keep excel and everything, I'd simply like some new features (e.g. relating tables, sql-like queries).

So, does anybody have an idea how to go about this?

Thanks a lot!

Answer 1

I have been using a lot Excel as Front-end and Access as Back-end.

Typically, my solutions involve UserForms or ActiveX Objects in Excel Sheets that manipulate and show data that is stored in an Access database. The Excel file and Access file are completely independent, which allows you to create and alter relationships in Access without restrictions. Just make sure you update your queries in Excel. There are even some cases that I use more than one Workbook to access the same Access database.

I make Excel communicate with Access via Excel VBA, using the ADODB. A quick Google search got me to this tutorial.

Cheers, Tiago