Excel Access Imports

[Tom J]

Anyone,
While in Excell I am attempting to "Get External Data" from an Access
database. When I follow through and select the table I wish from that data
base everything imports fine. All records and fields are imported. However,
when I try to import a query only the field headings come across with no
recors data. I have checked design view and the records are not listed. What
am I missing here?
Thanks
Tom J

 

[FSt1]

hi
not sure. i'm assuming at this point that your are trying to import a query
from the same database. what database? Access?
If you are gettng headers, you should be connected. Are there parameters in
the query that might be restricting the data? if so, you need to remove them
and place any parameters(criteria) on the excel side. can you get data from
the query when you run the query in the database?

post back with more infor about the query?
Regards
FSt1

 

[Tom J]

Yes the query is from the same data base (Access).
I get data on the Access side just fine using the query function.I could
just use the
whole table in Excel and use filters but that would defeat the purpose of
using
the data base to be able to manipulate the data.
In the Access table each record is a submittal with data types including
auto number,
date/ time, text, number and yes/ no.
The specification section numbers (text) are of the syntax i.e. 03-1245,
04-0270, 05-6482
representing Concrete, Masonry and Metals respectively On the query
designed, all fields are checked "to show" and the
Specification Section column has a criteria phrase: Like"03-*". This gives
me a query
showing all the info for just the 03 Concrete items. which I want to import
onto an
Excel sheet. As I said It works when I just bring the whole table across
onto an Excel
sheet.

 

[FSt1]

hi
your problem lies with your select. i am not sure what at this time. i have
been doing test in a database i have using the like operator and i cannot
make it not return data. I thought it might have something to do with the
wild card operator(*). MSQ uses the percent sign as the wildcard operation
but i can get data from acess into xl with the like operator and the *
wildcard in access so now i'm stumped.
if your are getting a blank query with no error messages then you are
connected. it's just that for some reason, the Like "03-*" is not being
reconized by MSQ. Acces will pull data so MSQ should be just pulling the
query results into xl.
did you put any filter criteria in the second dialog of the MSQ wizard?

Regards
FSt1

 

[Tom J]

Thanks, partial success!
I got the idea to look at wildcard operators in Access. In "Access Options",
"Object Designers" there is a checkbox for ANSI 92 syntax. Apparently mine is
defaulted to ANSI 89. I tried checking and unchecking 92 to get my criteria
to recognize % as wildcard and somewhere along the line when I unchecked 92
it substituted the phrase ALike "30%" for Like "30%" which gave the same
results as Like "30*" and subsequently the query data was accepted in Excel.
Now I am trying to conquer the next issue. I had the Excel sheet formatted
in a certain way but when I import the data it changes some formatting
especially row widths and column heights. Refresh makes it worse.