Use Excel to interface with MS Access query or table

Fritz Paul
Fritz Paul used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Remote Training and Programming
Top Expert 2015
Commented:
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

Author

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 Liason
Most Valuable Expert 2012
Commented:
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

Author

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 Programming
Top Expert 2015

Commented:
you're welcome, Fritz ~ happy to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial