Link to home
Start Free TrialLog in
Avatar of Andy Cownie
Andy CownieFlag for Australia

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?
Avatar of Andy Cownie
Andy Cownie
Flag of Australia image


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

Avatar of Saurabh Singh Teotia

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

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?
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.