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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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;
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?
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...
Saurabh...
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial