use access linked table query as excel datasource

Posted on 2011-10-31
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?
Question by:avoorheis
    LVL 19

    Accepted Solution

    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.

    Author Comment

    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?
    LVL 19

    Expert Comment

    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
    SELECT E_Name FROM Employees_USA

    Open in new window

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


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

    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.


    Author Comment

    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.
    LVL 44

    Expert Comment

    Show us connection information for your linked tables.
    Post sample workbooks.
    Post your Excel VBA code.

    Author Comment

    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:
    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

    Author Comment

    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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now