RE: Link AS400 database wirh Access Database -- MIDRANGE-L

Watch out - if your PF has packed fields, or zoned fields with negative values, FTP will do no good. Also if it has binary data, although this is relatively rare in user files on the iSeries up to this time.

You could use CPYTOIMPF to do the conversion to things a PC can understand.

You should be able to do all this with ODBC and have no problem. Be sure to check the naming convention, *SYS or *SQL, in the ODBC settings. BTW, SQL knows nothing about multiple members. You need to create an SQL ALIAS on the iSeries, which will be handled like a table.

For more information on all this, go to www.iseries.ibm.com/access and take the Library link on the left, then look for Programming, then Database or some such. I put this info in a thread this week about VB and ODBC. Look esp. at the old manual on 95/NT ODBC User's Guide - still superb information.

HTH
Vern

At 07:03 PM 1/20/2005, you wrote:

Ok ,then I guess, FTP would be the best option, as Paul also mentioned I may end up killing the system performance all the more. For big files the "Link Table" option in Access is not the right way ... why do we have this option at all??

Thanks everybody for the help.
rgds,
Sachin

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Gary Monnier
Sent: Thursday, January 20, 2005 6:37 PM
To: Midrange Systems Technical Discussion
Subject: RE: Link AS400 database wirh Access Database


My understanding is the data conversion happens within the ODBC
connection.  It is also possible your iSeries file may be too big for
Access to handle.

Yes, you can always try FTPing the iSeries file to your PC and importing
it into Access.  This would put the data conversion into the FTP.  No
need for Access to worry about library or member if you've FTP'd the
file to your PC.  You will be looking at importing a flat file.

-----Original Message-----
From: midrange-l-bounces+gary.monnier=powertech.com@xxxxxxxxxxxx
[mailto:midrange-l-bounces+gary.monnier=powertech.com@xxxxxxxxxxxx] On
Behalf Of Chaudhary, Sachin (GE Equipment Services,Consultant)
Sent: Thursday, January 20, 2005 4:28 PM
To: Midrange Systems Technical Discussion
Subject: RE: Link AS400 database wirh Access Database
Importance: High


1. Can't we choose to do this conversion on MS Access? Something like we
FTP the data to PC and then choose to open the file in Access?
2. Even if we can do this I wonder how can we select a library and a
file member??

Rgds,
Sachin

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Gary Monnier
Sent: Thursday, January 20, 2005 6:15 PM
To: Midrange Systems Technical Discussion
Subject: RE: Link AS400 database wirh Access Database


Access will have to perform a data conversion before your new query can
run.  You may use more CPU converting EBCDIC to ASCII that your original
query used.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Chaudhary, Sachin
(GE Equipment Services,Consultant)
Sent: Thursday, January 20, 2005 4:09 PM
To: Midrange Systems Technical Discussion
Subject: Link AS400 database wirh Access Database

Hi

I want to run query on one of the largest files on AS400. Since it eats
up a lot of CPU , I thought of Linking the file to Access database using
ODBC connection.
I will run the query in Access, and leave it overnight so that AS400 CPU
usage is not affected at all.
However when I chose link table in MS Access , and try to select ODBC
Databases, it offers selection of files from only 2 libraries PSCOMMON
and QGPL!. There is no browse button so that I can select a particular
production library, neither can I select any particular member of the
file?
Isn't this weird??
Is there any place where I can get some info about AS400-MS Access??

Rgds,
Sachin

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.