Data extract from access query

[Guest]

Hi

I am using MS Query to extract data from an Access Database which runs
queries from ODBC linked data tables.

In the Database the queries run perfectly and I have no problem, but if I
try extract data from these queries, the query name is visible in the MS
query wizard window but it will not allow me to move on through the wizard.

I would appreciate your feedback

 

[Conrad Carlberg]

Can you provide some more information as to what's happening, or not
happening, and what your setup is? Are you attempting to put the data into
an external data range, or directly into a pivot table? Are all the tables
in the same Access database? What's happening, or not happening, when the
wizard "will not allow me to move on"? An error message? A disabled OK
button? Something else?

Meantime, you might try running MS Query without using the Query Wizard
(clear the "Use the Query Wizard to create/edit queries" checkbox first).
This tends to give you more control over what's going on.

 

[Guest]

I am trying to put the data into an external range as I need to manipulate
the data with excel formulae.

All the tables are contained in one database.

At the query wizard stage the Next option is disabled and when I try to
expand the query to select data it the expand/tidy indicator changes from +
to - but I cannot view any records.

I had come a cross something similar previously where I couldn't extract
data from a query based on ODBC tables but I ran a make table query and it
worked - however this doesn't work in this case.

Thanks

TrevorM

 

[Conrad Carlberg]

Hi Trevor,

I'm sorry, I use MS Query a _lot_ but I don't recall ever having encountered
what you're describing -- apart from some silly error I've made such as
setting a criterion so that no records are returned, but you're not doing
that if the query works in Access.

My best suggestion, which is probably sub-optimal, is to use DAO (optimized
for JET) or ADO (a more robust and easier method) to create a QueryDef in
VBA and try returning the data that way. The VB Editor will give you some
opportunities, particularly in the Immediate Window as you step through the
code, to check on what's going on with your query.

I assume you're using a Select query, and I'm sure you know that it should
return the same records and fields as a similarly structured Make Table
query.

 

[Guest]

Thanks Conrad

Hi Trevor,

I'm sorry, I use MS Query a _lot_ but I don't recall ever having encountered
what you're describing -- apart from some silly error I've made such as
setting a criterion so that no records are returned, but you're not doing
that if the query works in Access.

My best suggestion, which is probably sub-optimal, is to use DAO (optimized
for JET) or ADO (a more robust and easier method) to create a QueryDef in
VBA and try returning the data that way. The VB Editor will give you some
opportunities, particularly in the Immediate Window as you step through the
code, to check on what's going on with your query.

I assume you're using a Select query, and I'm sure you know that it should
return the same records and fields as a similarly structured Make Table
query.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005