?
Solved

use access linked table query as excel datasource

Posted on 2011-10-31
7
Medium Priority
?
394 Views
Last Modified: 2012-06-27
I'm trying to set up and access 2007 query as a data source for an excel 2007 table. The query in access is based upon a few linked tables, which link to oracle and sql server. The excel connection only returns the query headers and no data, but, running the query through access returns several records (query takes about 15 sec to execute).
There is no password required for the access database, but, there is for the linked tables, which are stored in access.
Any ideas on what's preventing the table data showing up?
thanks
alan
0
Comment
Question by:avoorheis
  • 4
  • 2
7 Comments
 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 total points
ID: 37062609
you could store the working query in access and use this query as the datasource in excel.

As you indicate the passwords for the linked tables are stored in access but apperantly not in excel.
0
 

Author Comment

by:avoorheis
ID: 37062724
Yes, the query is in access and linking to that query just produces the headers.

I'm wondering if it's possible to actualy create a connection in excel that combines tables in separate databases, like sql server and oracle, in one excel query?

If not, shouldn't the query from access work?
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 37063762
To my knowledge (although i cannot test it because i currently do not have access installed), this should certainly be possible with something like

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

Open in new window

for tables having identical fields & information, as found on w3schools.

or

SELECT a.Name, a.Surname, b.Address, b.City FROM Persons AS b, Locations as b WHERE a.id = b.id

Open in new window

for tables that are contain information that is mutually connected by the contents of the 'ID' field.

I would have expected that whenever a query presents you results within access, the same results should be available in excel as well.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:avoorheis
ID: 37088402
I have a query in access that works. The problem is when I try to make a data connection to that query from excel, it does not return any records. I can do that with other queries, that do not used linked tables, but this particular query is using 2 tables that are links to seperate databases.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 37088476
Show us connection information for your linked tables.
Post sample workbooks.
Post your Excel VBA code.
0
 

Author Comment

by:avoorheis
ID: 37088768
no vba code, connection uses command type sql and the sql code is just a simple select from table. BTW, I found if the where clause uses field = 'name', it works, but, if the where clause uses field like "name*" it does not work.
connection string:
Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;Data Source=R:\path\Automate.accdb;
Mode=Read|Share Deny Read|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=True
0
 

Author Comment

by:avoorheis
ID: 37088794
oh, maybe i figured it out
the tables in Access are actually linked tables, oracle. So, while the sql, if i'm in access would be like:
where field like "name*"
but, seems like the query in excel requires sql to be compatible with oracle, so, needs to look like
where field like 'name%'
pretty tricky
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question