Access query does not work in Excel

A

Ake

In Access I generated the following query:
SELECT dbo_tblProj.strProj, dbo_tblWood.strSample,
dbo_tblWoodCell.dtsFinish, "C" AS Scnr
FROM (dbo_tblProj INNER JOIN dbo_tblWood ON dbo_tblProj.lngIdProj =
dbo_tblWood.lngIdProj)
INNER JOIN dbo_tblWoodCell ON dbo_tblWood.lngIdWood =
dbo_tblWoodCell.lngIdWood
WHERE (dbo_tblWoodCell.dtsFinish Is Not Null);

Which renders a list of the following type:
strProj strSample dtsFinish Scnr
ProjA Sample1 2004-06-18 C ... Etc

Pasting the same Query into the Excel Query Wizard and executing it, first
gives a message about not being able to display the query graphically, and
then the message "Could not add the table 'dbo_tblProj'."
I tried to explicity name the DB by "...FROM (Database.dbo_tblProj..." but
that gave the same result.
(The query: SELECT tblProj.strProj FROM SS3Data.dbo.tblProj tblProj works
just as expected when entered into Excel).

Q1: Shouldn't any query generated in Access also work from Excel?
Q2: How can I make this specific query work in Excel (it is eventually
targeted for a Pivot graph, including new Excel formulas in new columns)

Best regards /Ake
 
D

DM Unseen

Ake

I do not have anything to test this myself but here are some tips:

not all Access queries work, queries using (access) VBA in their
definition wont work in Excel.
Look at http://homepages.paradise.net.nz/~robree/excel/queryeditor.html
for a better editor
Start with a simple query in the visual grid, and after that start
changing it, msquery is very picky on syntax and format.
For complex queries create views/queries or stored procedures on the
database for added power and ease. Excel can sometimes reject complex
but valid queries.
Excel ODBC uses double quotes as identiefier delimiter, but can also
use the datasource native identifier delimiter (usually square
brackets)


DM Unseen
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top