Excel front-end and Access back-end?

[Guest]

Using Office 2003 and Windows XP;

In MS-Access one can split the database into a front-end and a back-end
wherein the front-end consists of the GUI and programs; the back-end only
contains tables.

Thinking radically now, would it be possible to use MS-Excel containing a
spreadsheet based form as the front-end distributed to users and have an
MS-Access back-end that contains the programming (that validates the form and
controls form submission and printing, etc)? Has anyone ever tried this
approach?

The front-end would only contain code that calls appropriate programs stored
in modules in the password protected back-end. The idea here is to distribute
a very light-weight front-end and protect the actual code a little better.

Users prefer Excel and are diverse, the processors of user info are
centralized. The front-end would be distributed via script that copy it onto
the local user's desktop. Form submission would push the data into the DB
where the processors could get at it easily...

Before spending more time and effort thinking it through, is this
hair-brained?
Thanks for your input and/or comments...

 

[RB Smissaert]

I am sure that is possible and don't think there is much radical about it.
Not sure though if the protection of Access is any better than that of
Excel.

RBS

 

[Bob Phillips]

No it is not hare-brained.

You can make the split between the DB and the front-end at whatever point
you wish, more logic at the back, or more at the front.

That is a very common technique (although personally I put very little logic
in an Access DB myself).

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I hope it is not hair brained. I have done it. On however can argue that I am
a little hair brained.

I have made Excel the front end using Access, Essbase and a combination of
the two. The simplest form of what you are talking about is a pivot table
that is linked to an Access database. All of the data comes from access and
goes directly into the pivot cache. I have done some reporting that queries
Access dependant on the user. The toughtest part is when you want to write
back to Access but suing ADO Recordsets that is very managable. It keeps the
front end very light weight and allows the user to access the data via a read
only spreadsheet that can be stored centrally and modified or updated easily.

To Bob's point however I only use Access as the data layer. The business
logic and user interface are done in XL. All of the queries are in XL also.
That being said I have created multiple front ends for a single Access back
end and even done some reporting and querieng directly within Access (using a
Access Frond end Database)...

 

[Bob Phillips]

To Bob's point however I only use Access as the data layer. The business
logic and user interface are done in XL. All of the queries are in XL
also.
That being said I have created multiple front ends for a single Access
back
end and even done some reporting and querieng directly within Access
(using a
Access Frond end Database)...

Same here, but with a real database (cue Access aficionados jumoping on me)
such as SQL Server, Sybase, etc. I have coded triggers and constraints that
do some of the validation work.

 

[Guest]

I've been linking to DB2 also but I didn't want to appear too snobby ;-)

 

[RB Smissaert]

Been linking to Interbase, MS SQL Server, SQLite, Access, Excel and text
files all in the same .xla.
So, no need to worry about being snobby with mentioning linking to DB2 ...

RBS

 

[Guest]

Yes but you and Bob are both from the UK. Descended from aristocracy and all.
I'm from Canada where people start thinking you're getting a bit too high and
mighty when you get a new pair of snow boots...

 

[RB Smissaert]

Ah, yes, in that case it would be risky to mention you linked to 3 different
backends!
I am actually Dutch.

RBS

 

[Guest]

What I was thinking might be novel turns out to be a wheel re-invention.

Looks like I have some work to do.

Thanks to all who replied!