AS400 download using VBA

[adrianvasile]

I was wondering if anyone has more experience with this. Basically I want to download files from AS400 to Excel or Access using VBA code and not use a DSN file because those excel files will be shared and I do not want to have the DSN installed on each user.
I read about ADO but I wasn't able to get more on it.
Thank you.

 

[BobRodes]

You might start here:

http://msdn.microsoft.com/en-us/library/ms969826.aspx

What you're looking for is an "OLE DB Provider" for AS400. This is Microsoft's; bear in mind that IBM makes one too.

Let me give you a little background that you may find helpful. OLE DB is a group of COM classes that handle data access. If you want to provide data by using OLE DB, you create an OLE DB provider which implements the interfaces defined by OLE DB to do this. If you want to consume data, you also implement interfaces defined by OLE DB for that purpose.

The main OLE DB consumer is ADO, and there are lots of OLE DB providers. The reason you're not going to find much on AS400 by reading about ADO is because they're different layers of the overall process: ADO is a consumer and AS400 is a provider. Now, here are the layers that you're looking at:

You, the user
Excel/Access
ADO
OLE DB
OLE DB provider for AS400
AS400

So, whenever you're looking to get data from somewhere to use with ADO, look for an OLE DB provider for that somewhere.

HTH

Bob

 

[adrianvasile]

Bob thank you for your help. It is indeed complicated. Do you happen to have some samples code? Microsoft's docs is not too helpful.
thank you.

 

[Golom]

There is a PDF at IBM Redbooks on talking to an AS400 and Connection Strings as the ADO connection strings (IBM and Microsoft).

 

[adrianvasile]

I will give it a try; i did not see any concrete examples in that manual - maybe I need to dig deeper.
Thank you Golom.