Getting External Data From Access

deanfran

Board Regular
Joined
Sep 9, 2011
Messages
52
I created a split DB that is used throughout our small office. A couple of my coworkers need to move some of data to Excel from the tables in the back end that is housed on a shared volume inside of our LAN. First I setup the connections Using the basic "From Access" wizard, but that tool appears to need exclusive access and prevents anyone else from opening the database so long as the connected Excel file is open. Next, I used the "From Other Sources" wizards, and set up a custom query, that doesn't prevent other users from using the database, but it only allows the computer that the connection was set up on to refresh the Excel table, because the connection wizard insists on creating a drive letter, that is unique to that computer. Looking for any help setting up these connections that A. Allow other users to use the database while the Excel file is open, and B. allow multiple Excel users to refresh data using the same connection setup. I have some, but limited VBA knowledge.
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

DSCfromCFA

Board Regular
Joined
Feb 27, 2018
Messages
147
Office Version
  1. 365
Platform
  1. Windows
Re: A Question Regarding Getting External Data From Access

G'day deanfran,

Most of what you said was outside of my expertise, however I am aware of a Microsoft Add-in specifically for importing and manipulating data into Excel.

For Excel 2010 and 2013 if is called Power Query
For Excel 2016 and onwards it is called Get and Transform

It may be that using this Add-in will assist you with your particular issue.

Cheers

shane
 

deanfran

Board Regular
Joined
Sep 9, 2011
Messages
52
Re: A Question Regarding Getting External Data From Access

Yes, I've used PQ as well. It has the same issues AFAIK.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,620
Messages
5,625,902
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top