Using Access as Backend and Excel as Frontend

[dpsciarrino]

I am making an inventory system for a consultant who buys products and sells them at vendor shows. I have a simple table in Access that holds the quantity of each item the consultant has. I would like to be able to use Excel as a front-end interface for manipulating this data (through userforms). How might I be able to accomplish this? Keep in mind I'm fairly new to Access.

 

[kevin.philips]

I am making an inventory system for a consultant who buys products and sells them at vendor shows. I have a simple table in Access that holds the quantity of each item the consultant has. I would like to be able to use Excel as a front-end interface for manipulating this data (through userforms). How might I be able to accomplish this? Keep in mind I'm fairly new to Access.

Being new to Access, doing as you describe might not be the best route for resolving the problem. If you have more experience in Excel and VBA forms then I'd probably suggest holding the inventory data table in Excel too. That way you'll be working in a single environment and the write backs will be far easier to perform.

You can easily hide the data environment from the consultants view.

 

[SydneyGeek]

It can be done, and has the advantage that you can set up a multi-user system as well.
Take a look at this tutorial: Excel, Access and ADO -- DataWright Information Services

Denis

 

[dpsciarrino]

Thanks for the reply Kevin. As convenient as that sounds I don't want to have to deal with hidden spreadsheet databases, I'd much rather harness the strengths of both applications.

And Denis, this is exactly what I was looking for! I will look into the tutorial. Thank you.

 

[enijhuis]

Have you considered linking to Excel (ranges) from within Access?

 

[Kyle123]

If you want to do this, I'd seriously consider SQL Server, the express version is free and Stored Procedures mean that you'll end up writing less sql in Excel than you would with Excel

 

[xenou]

Along with Denis' excellent tutorial I have also gleaned many helpful hints about using Excel and Access with ADO from this site:
ADO | Erlandsen Data Consulting

 

[Joe4]

Note that this was a thread that was supposedly resolved back in November 2013.
Not sure why it was resurrected, there is no new question...

 

[Kyle123]

Ah yes, hadn't spotted that, ta muchly!

 

[enijhuis]

Mea culpa.
Was googling for information on using Excel as a frontend for Access databases and ran into this thread.
Since I found a solution in the meantime I figured I'd post it although it's an older thread.