Write to Access Via Excel

[PJFry]

Is it possible to use Excel as a frontend to Access? I developed a database
for one of the groups at my company, but the request was made to interface it
to Excel, if possible.

If it can be done, what are some of the pros and cons of this approach?

Thanks!
PJ

 

[George Hepworth]

I suppose you could do that, but why would anyone want to cripple their
users that way?

The real question, though, is what does Excel offer in terms of an
interface, that would be superior to the interface in your Access database?

George

 

[PJFry]

In this case, the request was made to be able to data entry in Excel to a
database. I agree that it is a horrible idea, but I need to research it
nonetheless. I have already developed the database and the interfaces, so it
won't take much to convince the users to go with the Access interface.

What I am looking for is a simple explaination of the process. If I develop
a one-off solution and match it against Access I can make my point in a much
clearer manner.

 

[George Hepworth]

My question remains the same. Why do they want to use Excel? If they can
articulate to you a good reason for that, then you have a starting point for
the comparision. I can only imagine that they will say "everyone already
knows how to use Excel".

The whole point, as far as I'm concerned, is that Access allows you to build
an INTERFACE, through which you, the developer, control access to the data.
It is the interface that turns a set of flat tables into an application.

If "they" want to remove the interface and let your users put data anywhere
in the tables, with no validation or control of any kind, then Excel might
be one way to go with that. They could do the same thing in Access, too.

If the response is, "Well, can't you build an interface in Excel?", the
answer to that is basically, why not use the native interface tools in
Access to do a better, cleaner job of it?

I've been trying to come up with an analogy and the best I can do at the
moment is this.

If you wanted to build a house and had the choice between using a
contractor's tool box filled with various types of saws (power and manual),
hammers, screwdrivers and wrenches (power and manual), levels, trowels, etc.
or a home hobbyists tool kit with three screwdrivers, one hammer and a pair
of pliers, which one would YOU choose? More to the point, if you hired a
contractor to remodel your kitchen, which one would you expect him to bring
to the job?

It's not that Excel is a bad tool, it's just not the right tool for the job.

George

 

[(PeteCresswell)]

Per PJFry:

but the request was made to interface it
to Excel, if possible.

If it can be done, what are some of the pros and cons of this approach?

Programming-wise, most things are possible.

But I'd multiply my estimates by a factor of five and figure on
not being able to do a number of things that are quite simple
with an Access UI.

I don't base this on any experience with getting stuff from Excel
to Access - but I do have quite a bit of experience going the
other direction.

Seems to me like either way, the user is going to click on a
desktop icon to begin the data entry process.

Theoretically, if the screen was done carefully enough most
people wouldn't even be able to tell the diff by looking at
it/using it.

Based on that assumption, I'd ask the users why they should care
whether that icon brings up a screen that's based on an MS Access
..MDB or an Excel .XLS.


I'd expect the answer to be something to the effect that they are
familiar/comfortable with adding rows tabbing across cells in
Excel.


But if it comes down to a screen with that much freedom, they've
opened up a whole can of worms around data validation, which
columns are which, whether the user has moved columns, whether
the user has inserted columns, whether the user has deleted
columns, which row is the first data row, which row is the last
data row, empty rows, cells that are supposed to contain dates -
but which the user has typed some kind of label into..... and
so-on and so-forth.

Come to think of it, I'd probably increase my estimates by a
factor of at least 12 instead of five.

 

[AnandaSim]

Some of us don't have a choice - if the client wants Excel to be the
front end (could be they or their IT Dept does not want to deploy or
support Access on their SOE), then they have the upper hand.

Certainly you can use Excel to enter data / update, do anything with
Access.

As certain is that you will be working with unbound Excel VBA Forms
and unbound Excel cells. You can program with VBA + DAO / ADO, Excel
has a native method Range.CopyFromRecordSet but there will be a suite
of methods that you will have to invent to cater for validation,
record and cell trapping etc... Excel has events on worksheets that
will trigger your code.

 

[Bob Quintal]

Some of us don't have a choice - if the client wants Excel to be
the front end (could be they or their IT Dept does not want to
deploy or support Access on their SOE), then they have the upper
hand.

Wrong! Wrong! WRONG!!!!!

You have the upper hand: Just say "find yourself some incompetent
who will agree to this", pick up your coat and find another client.

If you are competent, you've got a waiting list of clients already.

Q

Certainly you can use Excel to enter data / update, do anything
with Access.

As certain is that you will be working with unbound Excel VBA
Forms and unbound Excel cells. You can program with VBA + DAO /
ADO, Excel has a native method Range.CopyFromRecordSet but there
will be a suite of methods that you will have to invent to cater
for validation, record and cell trapping etc... Excel has events
on worksheets that will trigger your code.

 

[AnandaSim]

Wrong! Wrong! WRONG!!!!!

You have the upper hand: Just say "find yourself some incompetent
who will agree to this", pick up your coat and find another client.

If you are competent, you've got a waiting list of clients already.

Wonderful words Bob. I'm sure you're one of those tolerant, good-to-
work-with people.

In a few sentences, you've been kind to the client, to me, and to my
competence.

Lovely.

 

[Bob Quintal]

Wonderful words Bob. I'm sure you're one of those tolerant,
good-to- work-with people.

In a few sentences, you've been kind to the client, to me, and to
my competence.

Lovely.

It's a dog eat dog world out there man. I have built my reputation
by not tolerating nonsense.

Sometimes telling a customer that his request is not practical and
will end up costing a lot more that he can afford is good business.

 

[Jamie Collins]

Access allows you to build
an INTERFACE, through which you, the developer, control access to the data.
It is the interface that turns a set of flat tables into an application.

If "they" want to remove the interface and let your users put data anywhere
in the tables, with no validation or control of any kind, then Excel might
be one way to go with that.

If you implement data validation at the engine level -- and I suggest
you should -- then there would be fewer issues...

If you wanted to build a house and had the choice between using a
contractor's tool box filled with various types of saws (power and manual),
hammers, screwdrivers and wrenches (power and manual), levels, trowels, etc.
or a home hobbyists tool kit with three screwdrivers, one hammer and a pair
of pliers, which one would YOU choose?

If you wanted to prevent the warranty on your new house being made
invalid by unauthorised works, would you seek an injuction on all
house maintenance contractors in the area, perhaps confiscate their
saws, hammers, screwdrivers and wrenches (power and manual), levels,
trowels, etc? Or would you protect the house?

Jamie.

--

 

[AnandaSim]

It's a dog eat dog world out there man. I have built my reputation
by not tolerating nonsense.

Sometimes telling a customer that his request is not practical and
will end up costing a lot more that he can afford is good business.

Fine, dude. What you stand for and what you want to stand for, that's
your perogative and your right. Nobody's gonna take that away from
you.

But this is a public forum, some of us work for other people, some of
us program for our ourselves, some of us run our own business etc....

Some of us are Excel programmers, some of us are Access programmers,
some of us are .NET programmer, some of us are VBA programmers (any
app). We drop by to exchange tips and approaches.

What determines what is not practical? Cost only? Manpower? Timelines?
Skill and Knowledge? Authorisation to change the SOE? Size and
complexity of the job?

If someone says they can only offer you Excel programming and you are
competent in Excel programming and they're willing to pay the cost /
time and complexity is under control, what's not practical?

 

[Bob Quintal]

But this is a public forum, some of us work for other people, some
of us program for our ourselves, some of us run our own business
etc....

Yes it's a public forum. An I will publicly state that if a client
asked me to deliberately break a law so that he could cheat on his
taxes, I'd tell him off. If a client wants me to do something that
will ultimately cost him money, I will tell him that his
requirements document is flawed, giving reasons and examples,
propose and price an alternative, and state clearly "No Bid" on his
original requirements.

9 times out of 10 they will amend the requirements and the tenth,
that's where I make real money, fixing what somebody inexperienced
did trying to follow the original requirements.

Some of us are Excel programmers, some of us are Access
programmers, some of us are .NET programmer, some of us are VBA
programmers (any app). We drop by to exchange tips and approaches.

Absolutely. And if you are an Excel programmer, and asked to write
an MP3 player in Excel,how would you respond?

What determines what is not practical? Cost only? Manpower?
Timelines? Skill and Knowledge? Authorisation to change the SOE?
Size and complexity of the job?

All of the above, and more.

If someone says they can only offer you Excel programming and you
are competent in Excel programming and they're willing to pay the
cost / time and complexity is under control, what's not practical?

If it's something you know can be done faster/better/cheaper in
Access, and will cost you time servicing/maintaining this
application, as opposed to developing other appplications for better
paying customers, it's definitely not practical.

You also have the issue of reputation to consider. If the
application could be better done with another tool, you can be sure
that the client will badmouth any programmer/consultant that didn't
object to the faulty requirements. Conversely, by letting the client
know that there is a better alternative, he'll go on to praise the
guy who saved him money.

 

[AnandaSim]

Yes it's a public forum. An I will publicly state that if a client
asked me to deliberately break a law so that he could cheat on his
taxes, I'd tell him off. If a client wants me to do something that
will ultimately cost him money, I will tell him that his
requirements document is flawed, giving reasons and examples,
propose and price an alternative, and state clearly "No Bid" on his
original requirements.

Yes, as I said before you are welcome to say what YOU WOULD DO.

However, it is incorrect to tell anyone:

WRONG, WRONG, WRONG

Because they are NOT YOU.

The original poster said:

Is it possible to use Excel as a frontend to Access? I developed a >database for one of the groups at my company, but the request was >made to interface it to Excel, if possible.

He is NOT YOU. He doesn't run a development business, he is an in
house user or programmer.

Similarly I AM NOT YOU.

Absolutely. And if you are an Excel programmer, and asked to write
an MP3 player in Excel,how would you respond?

Yes. Although this is a Access topics newsgroup, that does not mean
that Access is our ONLY skill or interest.

Both Excel and Access can play media files with the help of some API
calls. They may even be seriously used to store filenames and paths
and interface to media playing.

You also have the issue of reputation to consider. If the
application could be better done with another tool, you can be sure
that the client will badmouth any programmer/consultant that didn't
object to the faulty requirements. Conversely, by letting the client
know that there is a better alternative, he'll go on to praise the
guy who saved him money.

We're only here to do what we can. We're not THE MIGHTY ATLAS, out to
hold up the world, destroy people who will mock us, protect our
reputation against the foul mouthed.

To put it another way, we're driven either by income or fame or both
in mixed portions.

Access is a tool, a damnned good one. So is Excel. That's about it.

 

[Bob Quintal]

Yes, as I said before you are welcome to say what YOU WOULD DO.

However, it is incorrect to tell anyone:

I respecfully disagree.