asked on Excel data connection to Access- doesn't work on some PCs
We have a split MS Access database, where the backend resides on a shared network drive, which is updated by ~40 users daily. This works well, however to extract data and report on it, we are trying to import this data into Excel as Access' reporting can't deliver what we need.
I have successfully set up a data connection in Excel, to link to the backend Access database, lookup a query and import this data into excel. The query is a simple lookup on a few tables, no grouping, no parameters, and works on my pc. However when I send this spreadsheet to anyone else they can't refresh it, and receive an error saying the database can't be found. If i try to setup a connection on their pc, the database file can be linked to, but most queries don't appear in the list to link to, including the query I need, but I can link to a table.
Being on a corporate network, all of our PCs are basically the same, with the same access. I'm not sure if there's a setting to change or another better way of doing this?
I have successfully set up a data connection in Excel, to link to the backend Access database, lookup a query and import this data into excel. The query is a simple lookup on a few tables, no grouping, no parameters, and works on my pc. However when I send this spreadsheet to anyone else they can't refresh it, and receive an error saying the database can't be found. If i try to setup a connection on their pc, the database file can be linked to, but most queries don't appear in the list to link to, including the query I need, but I can link to a table.
Being on a corporate network, all of our PCs are basically the same, with the same access. I'm not sure if there's a setting to change or another better way of doing this?
Microsoft ExcelMicrosoft AccessVBA
Last Comment
Martin Liss
Andrew,
Quick question.. I'm assuming the pc's or for users for which doesn't work has access to this folder where this database is kept?
Also just try using this connecting string only...
Quick question.. I'm assuming the pc's or for users for which doesn't work has access to this folder where this database is kept?
Also just try using this connecting string only...
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\auintranet\dfs\2K\SydHO_FundsMgmt\InvServ\Retention & Support\Workflow Tool\Reports\CS Workflow Tool V1-1_Reports.accdb;
Persist Security Info=False;
ASKER
Thanks for replying Saurabh.
Yes as stated all users have full access to this network folder, and have no issues using this database to make changes (in Access.)
When I open the connection properties and change the connection string, Excel changes it back automatically. Am I doing something wrong here? Do I have to do this in VBA?
Yes as stated all users have full access to this network folder, and have no issues using this database to make changes (in Access.)
When I open the connection properties and change the connection string, Excel changes it back automatically. Am I doing something wrong here? Do I have to do this in VBA?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I've requested that this question be closed as follows:
Accepted answer: 500 points for saurabh726's comment #a40951468
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 500 points for saurabh726's comment #a40951468
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Open in new window