I created an link from Excel to an *.accdb file.  When I manually try to refresh the data, I get a message that the file is opened, which it is, by myself via Access. I don't suppose that there is a way for Excel to access the data even though the *.accdb file is open?  I'm not trying to change the database, just read data from it.           

Hi Polydom,

Could you tell me which kind of link did you create? With your issue, I test in my own environment and other computers. And I get the same error as shown in the following figure.

This error message possible causes:

• You attempted to open a database that is already opened exclusively by the specified user <name>. You cannot open the database until the other user closes it.
• You attempted to open exclusively a database that is already open in shared (non-exclusive) mode by the specified user <name>. You can open the database only in shared mode until the other user closes it.

Based on my tests in different environment, this issue might be caused by design behavior.

If you error message is incorrect, please post the whole message here.

Im glad to help and follow up your reply.

Regards,

George Zhao
TechNet Community Support

That's exactly what I get, and I understand that the Excel sees that the source database is open and refuses to refresh the data.  I'm just wondering if there is a way around that, perhaps via another means of linking?  Currently, in Excel, I go to the Data tab, Get External Data group, and select From Access.  It seems overly restrictive to refuse to refresh the data simply because it is opened.  Refreshing the data should only require read-only access.

Hi Polydom,

With your issue, it seems that Excel and Access lock each other.

I find a similar question about your issue, and the user solve this issue with code. I think if you really need to solve this issue you can try this method.

Please refer to this link and get more information about your issue.

http://www.mrexcel.com/forum/microsoft-access/748735-edit-access-table-while-linked-open-excel-file-possible.html

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

After reading this contents above if you need further assistance about code I suppose you can post your issue to MSDN forum of EXCEL and ask them about the question of code you need.

The link of MSDN forum of EXCEL:

https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

Hope its helpful.

Regards,

George Zhao
TechNet Community Support

Hi, George,

I appreciate that there are programmatic work arounds to the inability to refresh links to a database that is opened in Access, but the complexity is such that it would be simpler to just close Access for the refresh.

I'm surprised that this issue persists after more than a deccade.  Refreshing the link should not require anything more than read-only access.

At any rate, I think I will accept that there is no simple solution for the time being.  I appreciate the research that you have done.

Hello,

Is it not possible to change your architecture? If you put your data in one access, your forms and logic in another one (wich can be used by end-users) and link the data in this one. In this way there is no need to open your access.

Hope it helps,

Wouter

Hi Polydom,You can consider using Power Query to import the data from Access. You can refresh the workbook while the .accdb file is open.

Power Query for Excel is a free add-in that helps you to get data from a variety of data sources and transform the data to meet your business needs with simple user interface. The add-in is now available on all SKUs of Excel 2010 and 2013. You can download it here.

In Excel 2016, Power Query is deeply integrated as an Excel component into the Data ribbon (no longer an add-in).

To try it in Excel 2016 click here.

Thank you,

Gil

• Proposed as answer by Gil RavivMicrosoft employee 18 hours 45 minutes ago
• Unproposed as answer by Polydom 6 hours 19 minutes ago

Wouter, Gil,

I appreciate your responses.  I understand that it is possible to work around the issue, but I was really only looking for a solution rather than a workaround.  I'll keep your methods in mind should I have the opportunity to pursue a workaround in the future.  The Power Query add-on might be difficult to follow-up on because users here have very few permissions.

Gil, I just unproposed your response as an answer because, frankly, there is not solution to the question of making an Excel link to an Access database updatable.  It is a limitation that has persisted for more than a decade.  If there was a way to mark responses as helpful, I think it would be more suitable than to mark it as an answer.  I will look again for such a button.

• Edited by Polydom 6 hours 18 minutes ago