Is there an easy way to make Excel update Access, and vice versa?

[Alen David]

Hey Guys,

Thanks to everyone that gives useful advice on here ...

My issue is that I would like to be able to process an order form, and
finalize it by entering in a customer's billing information and having
it stored into a Access DB. Also if the customer is re-occuring, then I
would like to have it retrieve the customer information(I don't know
how) and populate all the billing/delivery information.

I understand that there are alot better ways of doing this out there,
unfortunately I donnot have the time, nor the budget.

Alen.

P.S.
As always any information is greatly appreciated.

 

[NickHK]

Alen,
My approach would to do the whole thing in Access.
The form wizard would probably generate most of the forms and coding for
you.

NickHK

 

[Chris Marlow]

Hi,

I'd take the opposite approach and say why not do it all in Excel!

Depends on how big your customer base is, but if all you are doing is
storing customer delivery/billing & you are short on budget then why
complicate the issue with Access? A bit of data validation & a few carefully
written vlookups & you would be away.

It depends on how scalable & extensible you need to be. For integrating
Access & Excel you could look at Microsoft Query - although I hate it
personally. The other technologies I've used a lot are DAO/ADO - but there is
a bit of a learning curve on both.

Regards,

Chris.

 

[NickHK]

Chris,
OK, I meant something more along the lines of "use one or the other rather
than both".
As you say, depending on the OP's expectation regarding scalability etc and
expertise, one app should suffice.

NickHK

 

[Bob Phillips]

You could use Access as the data repository and Excel as the client
front-end, using ADO.

http://support.microsoft.com/default.aspx?kbid=295646
How To Transfer Data from ADO Data Source to Excel with ADO

http://support.microsoft.com/default.aspx?kbid=278973
SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel
Workbooks


http://support.microsoft.com/default.aspx?kbid=257819
How To Use ADO with Excel Data from Visual Basic or VBA


Also see Carl Prothman' site
http://www.carlprothman.net/Default.aspx?tabid=81for connection strings.
MySQL even has an OLE DB provider so you can use that rather than ODBC
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMySQL




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Chris Marlow]

Nick,

Sorry, no offense intended, your comment is valid.

Yes one or the other is best ... some people just love Excel though
(accountants for one) - popping ad-hoc functions here there & everywhere ...
but want the advanced query functions of Access (and of course want it
yesterday on a budget of 10p).

In my experience the Form wizard is good if you get your database structure
spot on from the start. But I never really got a handle on using it until I
did my MOS, so I'd got used to always building from scratch.

Cheers,

Chris.

 

[Alen David]

Thanks for the information I'm trying some of the posted solutions.

Thanks to everyone.

Alen.

 

[Alen David]

Thanks Chris! We are initially on a tight timelime and budget.
Eventually we will create our own application to handle all of this...

So essentially an easy route is to use Excel spreadsheets to retrieve,
and update customer/billing information.

The reason we need to be able to retrieve customer/billing information
is so that if someone comes to place an order we can do a search for
their contact information and auto populate the customer information
field. If there is a discrepency with a customer, we want to be able to
pull the billed work.

For what I described above, do you feel that this would still be a
viable solution?

Alen.