connect VBA to mainframe datasets 1

[turil]

Hi,
Thank you in advance for all the information you can give me on this.
I would like to know if there is a way to download datasets from the mainframe to tables in MS access-(by writing some VBA code possibly). I know my PC has ODBC and I'm not sure if I have OLE DB. The files that I want to import are (nonrelational) flat files. The emulator for the mainframe is Attachmate.
Any help with this would be greatly apreciated.
Regards
Teresa

 

[lameid]

It sounds like someone has already exported the data you want to look at as flat [red]text[/red] files.

You can either import text files into Access or link them into Access.

From the file menu select get External Data and select either import or link.

At the bottom select Text from the files of type list (or ODBC if that is how you are going to connect).

Use the wizard to answer the questions... if it is text push the advanced button just so you can see what is there.

I assumed you mean text files when you say files although mainframe people like to call tables files so there is no way for me to be sure. If not then you would need an ODBC driver for the Mainframe in order to perform the above procedure. There are hoops to jump through to set this up and money to spend for the software. I'm no expert on this. I would suggest you find a forum for your mainframe if that is the case.

One thing to note about text files especially from mainframes. Sometimes they just use a Carriage Return or Line Feed at the end of a line instead of both. Access will only import/link if both are at the end of each line. This may be an option when the data is exported. The coding solution is to read and write each line of the file via Access which you can probably find via google as I don't have a copy of that code. The good news is there is a really quick and dirty way to fix this problem. Open the file in Wordpad. Save it. Done.

Docmd.transfertext is the VBA method you would use to import a text file.

I hope your solution is in there somewhere.

 

[turil]

Thank you for your reply Lameid,
I am able to download the datasets from the mainframe in txt or binary format on the server, and I wrote some code in a vba module to read them in MS Access.
I am looking however for a way to directly connect to the files on the mainframe, better yet ftp them in tables in MS Access maybe using ADO, in a vba module, i do not know if that is possible.


Teresa

 

[lameid]

ODBC I think is your best hope. You might check to see if the client software has an API or automation support and if within that it supports the file transfer.

I think the file transfer is simply going to be a manual part of the process but I could be surprised.

 

[turil]

Thank you very much, lameid,
I will try to post this question to a mainframe related forum as well.
Regards
Teresa

 

[lameid]

I think the question on the Mainframe side is how can the data be made available via ODBC or to Windows as a file in realtime... If Windows can get it, Access can get it. It is just a matter of how complex the code is.

 

[GhostWolf]

If you're wanting to access that data via code, this is a good place for ADO - but the part I don't know is the name of the ODBC driver AttachMate provides.

As an example, though, I'm currently using the IBM's Client Access ODBC driver:

Global cn400 as ADODB.Connection
Global cm400 as ADODB.Command
Global rs400 as ADODB.Recordset
Global UserID as String
Global Password as String

Public Sub InitApp()
  Set cn400 = New ADODB.Connection
  cn400.Open "Driver={Client Access ODBC Driver (32-bit)};" + _
             "System=OUR400SRV;" + _
             "UID=" + UserID + ";" + _
             "PWD=" + Password
  Set cm400 = New ADODB.Command
  cm400.ActiveConnection = cn400
  Set rs400 = New ADODB.Recordset
  ' etc, etc
End Sub

Public Sub GetRec(ByVal prmWhich as integer, ByVal prmSQL as string)
  cm400.CommandType = adCmdText
  cm400.CommandText = prmSQL
  Select Case prmWhich
    Case 0
      cm400.Execute
    Case 0
      rs400.Open cm400
  End Select
End Sub

Just replace the "Client Access ODBC..." portion with the AttachMate driver's name, and you should be good to go. (This is the code stub I've set up for my programs that access our '400.)

But lameid had a good suggestion in his first post: use ODBC to link the mainframe's file directly into your Access database. That way you don't have to create an ADODB connection to it, it's already there, (CurrentDB()).

 

[turil]

Hi GhostWolf,

This is just awsome!
I will try to understand and implement the code you gave me. Might be back with some more questions if i can't figure it out on my own.
I really apreciate this, i can't thank you enough!!!

Regards

 

[GhostWolf]

Oops!! Gotta love copy-and-paste, especially when I'm not paying close attention.

The second Case in that snippet should be "Case 1" instead of "Case 0".

Aside from that, you're more than welcome. I just hope it helps.