View Full Version : Connect to (Import) external data

2011-05-07, 05:37 PM
:confused2::confused2: HI

I've read the MS help detailed above.....Is there something more I can read to help my understanding?

I would like to learn how to set up data connections to an as400 based (old) erp system..through ODBC I beleive?

:becky:Oh! is this the right place to post this type of question?

Ken Puls
2011-05-09, 05:43 PM
Hi there, and welcome to the forum!

I assume that you're looking to connect to the database using VBA? I have an article on my site to retrieve data from a database that you can find here (http://www.excelguru.ca/node/23). Be aware, however, that the article connects to an Access database.

The methods should be very simliar though, but you would need to update the connection string to something that is compatible with the as400. I found some connection string for the as400 here (http://www.connectionstrings.com/as-400) that may work. (I can't test that part as I dont' have access to an as400 system.)

2011-05-09, 10:20 PM
Thanks Ken...
I will look at this code and copy it.
I will also post I here how I succeed or otherwise.
I was not actually thinkin g of vba...I was hoping that you could just export a data set into a data table ( 2007) using a scheduled task
using the data: conections part of the ribbon (2007).
I'm hoping that I will get help from someone with the as400 side...
Llets wait and see.

Thanks again

Ken Puls
2011-05-10, 04:39 PM
I would think you could... you may have to install an ODBC driver for the as400 (if you haven't done already). At that point, you should be able to build your connection right against the server...

Do you have a driver installed?

2011-05-11, 09:50 PM
Hmm.. where do I go for them/it. I think it is a 64 bit laptop? Thanks for the info

Ken Puls
2011-05-12, 05:35 AM
Great question...

I did a bit of searching and turned up an article that may be helpful: http://www.as400tutorials.com/as400-to-excel/

It makes reference in the article to: "AS/400 and iSeries ODBC driver comes with Client Access and is available as an option when you install Client Access on your PC"

This MAY be what they are referring to: http://www-03.ibm.com/systems/i/software/access/windows/odbc/index.html

I hope it helps,

2011-05-12, 06:07 PM
Wow..Ken thank you very much..I've note tried the links yet but I'm impressed with your dedication to respond so quickly and to keep on doing it...thanks again. sinner01