Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1285
  • Last Modified:

Live Link Between Excel and Access

Experts.

I have an existing access database where the user would like a couple fields to populate from data in an excel document. Then when the spreadsheet is updated Access would in turn have the same live update.

I've never done this before and am looking around to see if I can find someone who has done something similar. I'm not having any luck.

Any suggestions?
0
acramer_dominium
Asked:
acramer_dominium
  • 5
  • 4
1 Solution
 
Gustav BrockCIOCommented:
Attach/link the Excel worksheet or range as a table and read data.
Delete the linked file or close the Access application to allow the Excel file to be updated.
Update the Excel file.
Reattach the Excel worksheet or range or reopen the Access application. Read the updated data.

/gustav
0
 
mbizupCommented:
You can't have "live updates"/ links between Access and Excel, with instantaneous updates like you are describing - comparable to linked access tables.

The reason for that was a lawsuit years ago:
http://www.theregister.co.uk/2006/02/07/microsoft_office_access_infringement/
0
 
Gustav BrockCIOCommented:
That was the other way around - update Excel from Access.

Here Excel is updated - but that cannot happen if the Excel "table" was live in Access because that locks the Excel file.

The only option is to open the file in Excel, then open the linked table in Access.
Now you can update and save the Excel worksheet - and refresh (if still open) or reopen the linked table in Access.
If you close the worksheet, you have to first to close the table in Access, open the worksheet, then reopen the table in Access.

/gustav
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
acramer_dominiumAuthor Commented:
Unfortunate for us, but good to know you can't live link to an Excel file in Access. We will try to find another alternative.

Thank you for all the feedback.
0
 
Gustav BrockCIOCommented:
> you can't live link to an Excel file in Access

That's not what I wrote. Using the described sequence, you can.

/gustav
0
 
acramer_dominiumAuthor Commented:
You stated there would be locked table issues and if I have anywhere from 1 user to 50 users with the table open that could create major issues.
0
 
Gustav BrockCIOCommented:
You didn't try, I guess.
So:

Open the file in Excel, then open the linked table in Access.
Now you can update the Excel worksheet (a save is not needed) - and refresh (if still open) or reopen the linked table in Access.
The refresh in Access may even not be needed.

Let one more user open the linked table in Access.
You can still update the Excel worksheet - and refresh (if still open) or reopen the linked table in Access.

If you close the worksheet, you have to first to close the table in Access (all users), open the worksheet, then reopen the table in Access.

That said, if at all possible, I would find another route.

/gustav
0
 
acramer_dominiumAuthor Commented:
Parts of it worked. I was able to open the spreadsheet and the linked table. When I made a change to the spreadsheet it automatically updated the linked table. When I closed the spreadsheet errors started popping up in the linked table in access. We also have a .mdb and the spreadsheets are .xlsx which won't work together.

With the number of users and the complexity of this, I think I will tell them we need to go another direction.

Thank you for your help!
0
 
Gustav BrockCIOCommented:
The solution is to not close the worksheet. That's all that is needed.

/gustav
0
 
acramer_dominiumAuthor Commented:
No solution was found.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now