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?
LVL 2
Andy CownieImplementation SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andy CownieImplementation SpecialistAuthor 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

Saurabh Singh TeotiaCommented:
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 SpecialistAuthor 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?
Saurabh Singh TeotiaCommented:
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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.