How do I import mainframe reports into an Access database?

[Joey]

I'm using reports generated from mainframe accounting systems but am using an
Access DB to generate reports. Currently the process of generating these
reports by other means has been labor intensive. I'm hoping that Access will
allow me a greater ease of report production with tremendous time savings.
My manual and online Access help has returned no clear explanation.

Current process:

(1) Log into mainframe
(2) Collect data for report
(3) Save report query on the mainframe, C: drive, papercopy
(4) Manipulate data to compile report (*time consuming part)

What I would like to do is either import or link (if possible) these
mainframe reports into Access to more readily manipulate the data into
readible reports, quickly.

 

[G. Vaught]

If the data coming out of the mainframe is ASCII text and delimited with
comma's and quotes, then you can import the data to Access. If the format
out of the mainframe is different, check to see if it can be exported to
Excel first. Excel imports to Access quite easily if the data is defined
correctly. By this I mean, a text field is all text and is treated as text;
a numeric field is all numbers and no text is in the column.

 

[Joe Fallon]

It is possible to directly link Access to the mainframe and manipulate the
data.
There are *many* issues with this - but it does work.
About a decade ago I used Access to gather up the data in a few mainframe
tables and imported it to Access where the accountants could then run 10-20
reports against it for thier monthly closing.
Each report used to take hours on the mainframe and once the data was in
Access (20 minutes) then each report could be run in seconds.

A couple of tips:
1. You need to use the ODBC driver that talks to the mainframe as the way to
link Acces to it.
So you sghould have a regular program on your PC that already uses ODBC to
talk to your mainframe.
When you link Access use that one.

2. Once you get a some linked tables: do NOT open them up in Access - ever.
It pulls a lot of data over the wire!
Always use queries that return 1-5 rows when figuring out what is in a
table.
They are quite fast.

3. Write queries to extract tghe mainframe data into a local Access table
and report off of that.

 

[Joey]

Thank you. Definitely worth a try on Monday.

It is possible to directly link Access to the mainframe and manipulate the
data.
There are *many* issues with this - but it does work.
About a decade ago I used Access to gather up the data in a few mainframe
tables and imported it to Access where the accountants could then run 10-20
reports against it for thier monthly closing.
Each report used to take hours on the mainframe and once the data was in
Access (20 minutes) then each report could be run in seconds.

A couple of tips:
1. You need to use the ODBC driver that talks to the mainframe as the way to
link Acces to it.
So you sghould have a regular program on your PC that already uses ODBC to
talk to your mainframe.
When you link Access use that one.

2. Once you get a some linked tables: do NOT open them up in Access - ever.
It pulls a lot of data over the wire!
Always use queries that return 1-5 rows when figuring out what is in a
table.
They are quite fast.

3. Write queries to extract tghe mainframe data into a local Access table
and report off of that.

 

[Joey]

Thank you.

If the data coming out of the mainframe is ASCII text and delimited with
comma's and quotes, then you can import the data to Access. If the format
out of the mainframe is different, check to see if it can be exported to
Excel first. Excel imports to Access quite easily if the data is defined
correctly. By this I mean, a text field is all text and is treated as text;
a numeric field is all numbers and no text is in the column.