Beginners Question: How to access mainframe files

[Greg Truby]

I feel like a fool asking, but here goes...

The files on our mainframe are "VSAM" (I think). I've figured out how to use EZTrieve to create data sets on our mainframe and then FTP them over to my PC where I can play with them in Excel to my heart's content. From some posts I've read, it appears that there may be a way to pull data directly into Access from the mainframe. So my question is - what does this entail?

Do I need some type of "middleware" that would have to be purchased from some vendor? Or can I use some type of "ODBC" connection that is already ships with Access 2002? Anyone got a good reference on the subject.

Any insights/knowledge/advice are plenty welcome, 'cause (in case it ain't already quite obvious) the depth of my ignorance on the subject is deeper'n a well.

Thanks for yer time.

 

[SydneyGeek]

Hi Greg,

Yes you could do it with ODBC but I usually do this:
1. Save data from the mainframe as a .csv or .xls file
2. Use code in another workbook to massage that file -- set headings, create a range name for import. The range name stops Access spitting the dummy over additional [blank] fields that you never wanted to import anyway.
3. Import this data into Access using the TransferSpreadsheet method.

The above is manual (at least Step 1 is -- the other two can be automated from Access) and works best when you only want to update periodically (daily, weekly, monthly...). For a live update, ODBC or similar is what you need.

Denis

 

[Greg Truby]

Denis,

Thank you for responding. Do you know if some ODBC connection (or other tool) would allow me to query mainframe files directly (the original VSAM files, no some extract I've pulled)? I'm wondering how this is possible because with EZTrieve I have to set up "file" definitions that tell where every field is - the fields aren't "named" as there are differing record types all in the same files. So I don't see how you could do a SQL query without "named fields". (But I'm not SQL master, either).

Again, thanks to Denis or anyone else kind enough to share any knowledge.

 

[NateO]

Greg, do you have any experience with OLE DB/ADO?

http://msdn.microsoft.com/library/en-us/ado270/htm/dasdkadooverview.asp

Here's your connection:

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForAS400AndVSAM

 

[Greg Truby]

Nate,

Thanks for stopping by.

I had already stumbled across the 2nd link you cite. There the connection string given for "OLE DB provider for AS/400 and VSAM" has an argument "Data source=myAS400". However I don't even know what I would put here.

But isn't my problem even more fundamental? At first I thought that since I can FTP files over, perhaps I could connect directly into the VSAM files. But everything I'm pulling up in places like here and here seems to indicate that I need something to act as some type of a "bridge" between my desktop PC & Access and the mainframe?

Also, surfing off your first link, I came to this, but the title section on this indicates that it is a part of MS Host Integration Server 2004. Again the implication to me is that I can't simply hit the mainframe without something to act as an interpreter that will define the structures of the VSAM files so that Access or the "provider" can understand.

As I said in my first post, this is a lot of terra incognita for me. I'm beginning to suspect I can't get there from here with the tools I have in my toolbox. But I would be thrilled to be wrong! Thanks again,