0

I would like to have fields in my Excel sheets be bound (in both directions) to a data source, in this case an Access DB.

For example, I would like to have an Excel sheet 'select' a particular record, say a customer, and then load information on that customer into the worksheet. Then, any changes made to that worksheet would be pushed back to the data store, making Excel a nice front end to the data.

Can this be done? From what I can tell "Get External Data" options in Excel are one way routes. My development background is heavy in ASP.NET C# and SQL.

Improve this question
3
  • From the reading/searching I have done it does not seem possible (hoping asking here proves otherwise) Commented Dec 8, 2013 at 21:43
  • 1
    You can use VBA recordsets to do the CRUD operations on the Access database based on values in cells on an Excel worksheet. You will have to lock down so many cells and be very careful to manage row/column insertions and deletions, I don't see the Excel advantage. Commented Dec 9, 2013 at 4:58
  • Yea, and that's exactly the 'rigidity' I'm trying to avoid bringing them. Commented Dec 9, 2013 at 15:25

1 Answer 1

Reset to default
1

Excel is designed to deal with datasets and not so much single records. For what you are trying to do with a single record, you would be far better off building a form in access, but as I don't know your environment/organisations limitations I'll make a suggestion.

Since you've obviously got a bit of SQL and coding skill check out this post for an option that would work for you - Updating Access Database from Excel Worksheet Data

You can get or put as much data as you want and can join tables too. It's a good basic get and then push set up.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

My hope is to keep as much control in accountant's hands within Excel (and not limit them by having to require me for changes).
Maybe go with the access front end and split it into two halves. One front end as an accde for the accountant and a back end up on a shared location on the network. If you go the excel route they aren't going to be able to change the structure (add fields) without you so it wont be any worse with an access form. Plus you can make the access form have some data validation.
Thanks @goneos, I'm going tno look a bit further into this route.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.