Using Excel to Query AS/400
Chris Hagwood
I apologize if this is on dejanews, but I couldn't find it if it is:
I am trying to use the "get external data" option in EXCEL. I've been
able to query the AS/400 from ACCESS without too much trouble (well, it
won't let me do any complicated queries, but it's working OK). Now, I'd
like to make a query in MSQuery that runs w/in EXCEL so that my data is
directly pulled and placed into my spreadsheet. When I try to make the
query, it always writes the select statement like this:
SELECT table.column1, table.column2 from *n.library.table table
^^^
My problem seems to be around the *n in front of the library. I've tried:
systemname.library.table
systemname/library.table
systemname_library.table
systemname\library.table and even
library.table
None of them work. Where am I going wrong?
Thanks
Bill Guenthner
1. Create a DB in Access.
2. Link to or import the AS/400 files into that database.
3. Save the Access database and (optionally) close Access.
3. From Excel query the ACCESS database.
also, How complex is complex? Sometimes you can do things in reports that
you can't do in queries. I can't seem to sort anything (AS/400 data) in a
query but I can build a report on the query results and sort the report. You
can also do a MAKE TABLE query and then do a second query against that table
to do the "complex" stuff.
Chris Hagwood wrote in message <6hj2bc$fdu$1...@pinta.pagesz.net>...
Chris Hagwood
Bill sez:
: 1. Create a DB in Access.
: 2. Link to or import the AS/400 files into that database.
: 3. Save the Access database and (optionally) close Access.
: 3. From Excel query the ACCESS database.
Well, the intended user of this spreadsheet doesn't have access installed,
so I'm trying to avoid access altogether.
I don't mind using the "analyze with excel" option in access, but there
are others who don't have access, or aren't knowledgable enough to do
that each time. I'm hoping to save a query that they can run from excel
w/out any additional steps.
Thanks for the suggestion.
Charles M. Wilt
Chris,
I can't help you, but I wanted to let you know that I have the same problem
you do. I seem to have know problems using access to access the AS/400
data. But I get an error when I try to use Excel's "Get external Data"
function.
--
Charles Wilt
Miami Luken, Inc.
Springboro, OH. 45066
e-mail: charle...@worldnet.no.spam.att.net
--remove the .no.spam
Dave Shaw
Chris Hagwood wrote in message <6hj2bc$fdu$1...@pinta.pagesz.net>...
>I apologize if this is on dejanews, but I couldn't find it if it is:
>
>I am trying to use the "get external data" option in EXCEL. I've been
>able to query the AS/400 from ACCESS without too much trouble (well, it
>won't let me do any complicated queries, but it's working OK). Now, I'd
>like to make a query in MSQuery that runs w/in EXCEL so that my data is
>directly pulled and placed into my spreadsheet. When I try to make the
>query, it always writes the select statement like this:
>
>SELECT table.column1, table.column2 from *n.library.table table
> ^^^
>My problem seems to be around the *n in front of the library. I've tried:
>
>systemname.library.table
>systemname/library.table
>systemname_library.table
>systemname\library.table and even
>library.table
>
>None of them work. Where am I going wrong?
The correct syntax is systemname.library.table, as shown in the following
that I copied from a working query that I have:
SELECT PSTRUC.PINBR, PSTRUC.USRS1, PSTRUC.CINBR, PSTRUC.USRS2, PSTRUC.MDATE,
PSTRUC.QTYPR
FROM GNPBAT.AMFLIBB.PSTRUC PSTRUC
ORDER BY PSTRUC.PINBR, PSTRUC.USRS1, PSTRUC.CINBR, PSTRUC.USRS2
If this syntax doesn't work, you need to check your ODBC driver setup - you
need to make sure that the system name is explicitly specified in it. I
think the *N could be coming from it being blank, which could be the case if
you're only connecting to one AS/400 (I have two, so I can't leave it
blank).
Once you get past this hurdle, I think you'll find that MSQuery into Excel
works very well - I have much better luck with this than with Access. The
one drawback is that since it always redisplays the retrieved data to you as
you make changes to the query, it can be sluggish setting up the definition
if there's a lot of data involved. It can also make your /400 response drag
if you don't have it tuned to isolate this stuff. Good luck!
--
Dave Shaw, General Nutrition, Greenville, SC (just down the road from BMW -
Bubba Makes Wheels :)
The opinions expressed may not be my employer's unless I'm sufficiently
persuasive...
Allan S
Hi Dave,
You can get around the re-retrieve of data when you make changes by turning
off the automatic query option from either the toolbar or under the records
option. It always does a full retrieve the first time you go into MSQuery
with a query though, which is a real pain.
HTH
Allan
Manuel Amaro
Hi Dave,
Sorry to pop here but, I am using the Excel to import data too and it
works well in the majority of files but I'm getting some problems in
files over more or less 800 lines. It starts inserting blank lines and
ASCII code after that number of lines.
I have CA400 V3R1M2 SP SF46059.
Thank's for any help.
--
Manuel Amaro
CIN, S.A. / I.T.Department
Portugal
Mailto:am...@cin.mai.mailpac.pt
Dave Shaw
Allan S wrote in message <6hmjuh$4h...@iccu9.ipswich.gil.com.au>...
Well, stupid me, there it is! Thanks, Allan, that's a GREAT tip!
Anyone know a secret handshake to kill that full retrieve on entry into
MSQuery? That would be quite valuable.
Steve Moland
>
> Anyone know a secret handshake to kill that full retrieve on entry into
> MSQuery? That would be quite valuable.
>
procedures. I would try to code a procedure to set the undesired property
to OFF. When you get that working, rename the procedure to Auto_Open and
it will be executed automatically each time as the file is loaded. FYI, a
procedure named Auto_Close runs automatically as a file is being closed.
To skip the execution of Auto_Open, hold down the shift key as you open the
file.
Steve.
Dave Shaw
-----Original Message-----
From: Manuel Amaro <c...@cin.pt>
Newsgroups: comp.sys.ibm.as400.misc
To: Dave Shaw <dsh...@InfoAve.Net>
Date: Thursday, April 23, 1998 3:49 AM
Subject: Re: Using Excel to Query AS/400
>Hi Dave,
>
>Sorry to pop here but, I am using the Excel to import data too and it
>works well in the majority of files but I'm getting some problems in
>files over more or less 800 lines. It starts inserting blank lines and
>ASCII code after that number of lines.
>
>I have CA400 V3R1M2 SP SF46059.
Strange. The SQL that I posted downloads 29,757 records without any
problems. I'm also on CA400 V3R1M2 SP SF46059. What version of Excel are
you running? I'm using Excel 97 on Windows 95B, running on a Pentium MMX
233 with 32 MB of memory. My AS/400 is running V3R7, with the C7343370 cume
installed. I've also installed all the relevant PTF's mentioned in the
CA/400 informational APAR, II10303, as of about a month ago. There were
some database PTF's in there, perhaps one of them has some bearing. II10303
lists the correct PTF's for each OS/400 release - if you haven't reviewed it
against the PTF's installed on your /400, you should. It's supposed to be
out on the Web somewhere, but I normally just use SNDPTFORD to download it
to the /400 when I I want to.
Oh, it probably doesn't matter, but I'm connecting over Ethernet through a
NetWare for SAA gateway. Normally that tends to make problems worse, rather
than to hide them, so I wouldn't think that would be a factor.
Mr Lou
Yes. Get Excel 97. Look at the worksheet's query collection.
You can hardcode the connection to be DSN-less, adjust the
requery flag & have multiple queries fill different parts of the
sheet. Who needs MSQuery?