Use Excel to interface with MS Access query or table

I want to apply Excel as interface for an MS Access database. The user will only have to enter and updata one field in a query (or a table), but needs to see the other fields also. How can I accomplish this?
The reason to do this is that the only database interaction for this user is only this one field in one query. The user is used to simple Excel use and we do not want to expose him to a "new" computer application.
Fritz PaulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
On a new worksheet (my recommendation), you can link to that table in Access from Excel.  The data comes into your workbook just like it was really there.  You can use it in calculations, refresh it to see the latest values, add to it (make sure connection is read-write) and do what you would with any data that was actually typed into Excel.

to link to the Access data from Excel,
1. Data ribbon
2. From Access icon in the Get External Data group
3. follow the wizard steps to identify the database and table/query you want to link to

now you have the data from Access in an Excel worksheet.  You can add data to it programmatically and manually

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fritz PaulAuthor Commented:
I love your reply. I can see that this solution should be exactly what I require. However it seems a bit more complicated than meets the eye. I linked to two databases (.mdb (2003) and .accdb (2010) and the third .mdb (2003) could not link, because it has permissions. All three occasions I was offered different wizards. The one with the permissions, was the only one that offered read/write option, but could not find the workgroup information file.
I think a VBA linking process will work best. Do you perhaps have linking VBA code?
Jeffrey CoachmanMIS LiasonCommented:
Last time I tried, ...Linked Access tables still do not support being updated from Excel...

Besides, ...even if it could, ..it would allow the user to change *anything* they wanted to,
(...unless you custom configured Excel' s Protection features...)
Hence, this would destroy your:
"only have to enter and update one field"
...requirement.

You could create a simple Datasheet (View) form in Access.
This will look like the Excel "grid" interface.
With a form, ...you could only allow edits in a certain field.

simple sample is attached.
The "form" ("OnlyEditAmounts')
...will open automatically ...and the the user will only be able to edit the amounts.

JeffCoachman
Database2.accdb
Fritz PaulAuthor Commented:
Thanks to you both. Crystal opened my eyes to the possibility to link to Access from Excel side. I always exported from Access to Excel.
Thanks for Jeffrey as you confirmed that what I want to do is not possible.
I do still believe that there must be a VBA way. For instance use the linking from Excel and then have an update button to go and save entered data into the access table. I will ask a question.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Fritz ~ happy to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.