RE: [PCTECH] automagically import a .CSV into Access db table from CL?

[daparnin@xxxxxxxxxxxxxxxxxx]

If you want security for your ODBC connection you may want to look at a
product called Showcase (http://www.spss.com/showcase/).  It respects
AS/400 security but also has it's own level of security.  I have configured
it here to set up access to files based on job function (accounting,
purchasing, EDI, etc.).  You create queries which can be linked to Excel
spreadsheets.  It still uses the same Client Access AS/400 logon that you
get when you first launch a PC5250 session.  You open a spreadsheet, the
hyperlinks cause the query to launch, you log into the AS/400, and you are
prompted to refresh the data.  There are a number of other built-in
functions.

I am in no way affiliated with SPSS, just a happy consumer.

Dave Parnin
Nishikawa Standard Company
Topeka, IN  46571
daparnin@xxxxxxxxxxxxxxxxxx





                                                                                
                              
                      Dan Bale                                                  
                              
                      <dbale@Duro-last.        To:       PC Technical 
Discussion for iSeries Users            
                      com>                      <pctech@xxxxxxxxxxxx>@SMTP@CTB  
                              
                      Sent by:                 cc:       (bcc: David A 
Parnin/Topeka/NISCO/SPCO)              
                      pctech-bounces@mi        Subject:  RE: [PCTECH] 
automagically import a .CSV into Access 
                      drange.com                db table from CL?               
                              
                                                                                
                              
                                                                                
                              
                      10/20/2004 03:02                                          
                              
                      PM                                                        
                              
                      Please respond to                                         
                              
                      PC Technical                                              
                              
                      Discussion for                                            
                              
                      iSeries Users                                             
                              
                      <pctech@midrange.                                         
                              
                      com>                                                      
                              
                                                                                
                              
                                                                                
                              



Thanks everyone, sorry I haven't responded sooner.  I've been fleshing this
out a bit.

Here's the deal.  It will be a cold day in h*ll before an internet user can
directly touch our data on the iSeries.  So, the Access-->ODBC-->iSeries
file link is out.  It may well be that this can be secured, but we do not
have the expertise in-house to do it and know that we are secure.

However, got to brainstorming, and wondering if I can use ODBC to link to
the .CSV files that get created now.  So I go to the easy-to-find ODBC Data
Source Administrator in the control panel, configure a new data source
using
the "Microsoft Text Driver (*.txt, *.csv)".  Back in Access, I click File,
Get External Data, Link Tables, and select Files of Type "ODBC databases
()".  As soon as I click on that, the Select Data Source dialog box
appears,
so I select the ODBC option I just added and click OK.  I have tried both
the File Data Source and the Machine Data Source, and I get the same error:
"You cannot use ODBC to import from, export to, or link an external
Microsoft Jet or ISAM database table to your database."

Would it be possible to link to an Excel sheet?

Again, the goal is to make this so that I.S. staff intervention is no
longer
required on a daily basis to manually import the .csv data into Access
tables.  (There are two .csv files that populate two tables in the same
Access database (.mdb)

> -----Original Message-----
> From: pctech-bounces@xxxxxxxxxxxx / Vernon Hamberg
> Sent: Tuesday, October 19, 2004 7:53 PM
>
> db
>
> Since security does not matter, just change it so that the table uses a
> link to the actual physical file through ODBC. Then you have
> everything in
> real-time - no need to do anything - the data is live.
>
> HTH
> Vern
>
> At 04:53 PM 10/19/2004, you wrote:
> >VB or any other PC programming idea is out.  KISS is the rule of the
day.
> >
> >Security not a problem, since only three of us in the I.S.
> department have
> >the necessary write authorities on the folder that holds the Access
> >database.
> >
> >db

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