Excel data connection to Access- doesn't work on some PCs

Andy Cownie
Andy Cownie used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Andy CownieImplementation Specialist

Author

Commented:
Here's the connection String in Excel if that helps:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\auintranet\dfs\2K\SydHO_FundsMgmt\InvServ\Retention & Support\Workflow Tool\Reports\CS Workflow Tool V1-1_Reports.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

Open in new window

Top Expert 2015

Commented:
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...

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;

Open in new window

Andy CownieImplementation Specialist

Author

Commented:
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?
Top Expert 2015
Commented:
Yes you need to change this in the vba code which you are using to fetch this data from the access file which you posted..

Saurabh...
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial