Is there a way to create a script or something to 'import' multiplefiles from AS/400?

  • Thread starter Thread starter Gilbert Noetzel
  • Start date Start date
G

Gilbert Noetzel

I would like to find a way to create a script file when opening the MS
Access. That I don't have to 'manually' select each individual AS/400
database. There are over 25 database that I need to import and this is
going to be on a daily basis.

Can anyone help me here?

Thanks

gil
 
Yes, it is not that difficult.
You can use a macro or a form.

If you have a macro named Autoexec, it will execute every time you open the
mdb (unless you are using the Bypass Shift Key).
You can create a macro that does each import and give it the Autoexe name,
and it will execute and import your AS400 data each time you open the mdb.
 
Correct me if I am wrong assuming what you are saying is understood
properly:

I can create a single macro when opening the MDB file that will
automatically import from the AS/400 25+ database files?

I am going to attempt to do that today. If I get lost, do you have a
link that you can point me to so that I can achieve my goal.

Thanks for your help Klatuu

Gil
 
No Link to offer, but if you can do one, you can do 25. Since I don't use
Macros, I don't know if there is a limit to the number of commands allowed in
a macro, but if it is fewer than 25, you can run a macro from a macro, so you
can chain them together.

If you have any problems, post back with details and we can work it out.
 
Klatuu -

I am getting close but I am getting errors of all sorts. The reason why
I am getting errors is that I do not have a point of reference to know
what to type in the "Action Arguments" for the Macro.

Here is what I have done so far:

ACTION: TransferDatabase

ACTION ARGUMENTS:

Transfer Type: Import
Database Type: ODBC Database
Database Name: DRIVER={iSeries Access ODBC
Driver};PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=b2hdsdata;SYSTEM=S105F7MM
Object Type: Table
Source: FILEDSN=C:\Program Files\Common Files\ODBC\Data
Sources\B2HDSDATA.dsn
Destination: B2HDSDATA.APPAID
Structure Only: No

I believe that my mistakes lies in the Database Name & Source of the
Action Argument. I have scour over the Google, Microsoft & IBM website
for sames or direction, but none have anything or I am searching under
wrong 'search' words.

The common error it says "could not find installable ISAM" I wish there
was a wizard to do this, but I do not see any.

Thank you for your help.

Gil
 
Klatuu - and others...

I apprecaite any or all of your help.

I was able to get this far on creating a single macro to import 1
database file but it ends with the following error:

Syntax error in query. Incomplete query clause.

I click on okay and it showed the 'action failed' and I see the
arguement box and found that it truncated the end of the arguments as
follows:

Import, ODBC, ODBC;DRIVER={iSeries Access ODBC
Driver};PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=b2hdsdata;SYSTEM=S105F7MM;FILEDSN=C:\Program
Files\Common Files\ODBC\Data
Sources\B2HDSDATA.dsn;;TABLE=B2HDSDATA.APPAID, Table, APPA

Where the end of the argument should be APPAID, but it seems it ran out
of character length to 255. Is there a way to increase this or shorten
the argument?

Below is the Macro of the above sample:

MACRO: APPAID
Action: TransferDatabase
Action Arguments:
Transfer Type: Import
Database Type: OBDC Database
Database Name: ODBC;DRIVER={iSeries Access ODBC
Driver};PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=b2hdsdata;SYSTEM=S105F7MM;FILEDSN=C:\Program
Files\Common Files\ODBC\Data Sources\B2HDSDATA.dsn;;TABLE=B2HDSDATA.APPAID
Object Type: Table
Source: APPAID
Desitnation: (left blank)
Structure Only: No


Again..thanks in advance for your help

Gil
 
This does not appear to have anything to do with the macro. It appears there
is an error in the Query. Try running the query without using the Macro to
see what errors you get.
 
Hi Gilbert,

You may be on track. The limit for an action argument in a macro is 255 (per
Access Help). Increasing this limit is probably not an option. So that
leaves you with:

- find a way to shorten the connect string (I dont know much about this, but
maybe some of the AS/400 people can help you?)
- Use VBA


HTH,
Immanuel Sibero



Gilbert Noetzel said:
Klatuu - and others...

I apprecaite any or all of your help.

I was able to get this far on creating a single macro to import 1
database file but it ends with the following error:

Syntax error in query. Incomplete query clause.

I click on okay and it showed the 'action failed' and I see the
arguement box and found that it truncated the end of the arguments as
follows:

Import, ODBC, ODBC;DRIVER={iSeries Access ODBC
Driver};PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=
b2hdsdata;SYSTEM=S105F7MM;FILEDSN=C:\Program
Files\Common Files\ODBC\Data
Sources\B2HDSDATA.dsn;;TABLE=B2HDSDATA.APPAID, Table, APPA

Where the end of the argument should be APPAID, but it seems it ran out
of character length to 255. Is there a way to increase this or shorten
the argument?

Below is the Macro of the above sample:

MACRO: APPAID
Action: TransferDatabase
Action Arguments:
Transfer Type: Import
Database Type: OBDC Database
Database Name: ODBC;DRIVER={iSeries Access ODBC
Driver};PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=
b2hdsdata;SYSTEM=S105F7MM;FILEDSN=C:\Program
Files\Common Files\ODBC\Data Sources\B2HDSDATA.dsn;;TABLE=B2HDSDATA.APPAID
Object Type: Table
Source: APPAID
Desitnation: (left blank)
Structure Only: No


Again..thanks in advance for your help

Gil


Driver};PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=
b2hdsdata;SYSTEM=S105F7MM
Click to expand...
out.
 
I got it Klatuu - It appears that I was putting the 'link' in the wrong
spot. Now it is working great and Access is fantastic! until now...

Like I said I have over 25 database files to import. Apparently there
is 1 file that has a problem. Originally, I converted one of the fields
to numeric and when done, it removed any text data in the fields..which
is fine.

During the Import using the Macros, the field remains texted and I have
to manually change it to numeric. Is there an automated way during the
import process that the data field can be forced to be numeric instead
of text?

Thanks

Gil
 
Back
Top