• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

use access linked table query as excel datasource

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
avoorheis
Asked:
avoorheis
  • 4
  • 2
1 Solution
 
Arno KosterCommented:
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
 
avoorheisAuthor Commented:
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
 
Arno KosterCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
avoorheisAuthor Commented:
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
 
aikimarkCommented:
Show us connection information for your linked tables.
Post sample workbooks.
Post your Excel VBA code.
0
 
avoorheisAuthor Commented:
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
 
avoorheisAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now