Improve company productivity with a Business Account.Sign Up

x
?
Solved

Live Link Between Excel and Access

Posted on 2013-10-25
10
Medium Priority
?
1,463 Views
Last Modified: 2013-11-06
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
Comment
Question by:acramer_dominium
  • 5
  • 4
10 Comments
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 39601691
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39601881
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
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 39602494
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 

Author Comment

by:acramer_dominium
ID: 39605956
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
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 39606005
> 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
 

Author Comment

by:acramer_dominium
ID: 39609023
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
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 39609109
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
 

Accepted Solution

by:
acramer_dominium earned 0 total points
ID: 39617595
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
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 39617969
The solution is to not close the worksheet. That's all that is needed.

/gustav
0
 

Author Closing Comment

by:acramer_dominium
ID: 39626850
No solution was found.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question