Transfer Data from a Database

I'm trying to transfer data from the AS/400 to a Excel workbook. But I'm still getting a error.

[VBA]
Sub Transfer_File()
Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "Select * from B400.DEVIN100"

Set rst = New ADODB.Recordset

' xxx = your as/400 ip address

rst.Open strSQL, "Provider=IBMDA400;Data Source=204.254.X.XXX;"

Range("A2").CopyFromRecordset rst

End Sub
[/VBA]


I'm getting a error along this line:

[VBA]
rst.Open strSQL, "Provider=IBMDA400;Data Source=204.254.X.XXX;"
[/VBA]


I installed the Visual Basic reference:

Microsoft ActiveX Data Objects 2.7 Library.

Am I missing something?

What error are you getting?

Are you sure the connection string is correct?

Quote:

---

Originally Posted by Norie

What error are you getting?

Are you sure the connection string is correct?

---

It highlights this line:

[VBA]
rst.Open strSQL, "Provider=IBMDA400;Data Source=204.254.X.XXX;"
[/VBA]


Run-Time error '3706'

Provider Cannot be found, It may not be properly install.


What do you think it might be?

I'm no expert with this type of thing but it sounds to me that you may be missing some sort of driver for the database you are trying to connect to.

Can you connect to the database through Data>Get External Data...?

Quote:

---

Originally Posted by Norie

I'm no expert with this type of thing but it sounds to me that you may be missing some sort of driver for the database you are trying to connect to.

Can you connect to the database through Data>Get External Data...?

---

Yes and it works but it takes extremely slow. Doing through a code is much more faster. I do have a ODBC driver set up that is how I was able to do External Data. But do you might have a sample code that I could use that it worked for you?

I'm sorry but I can't give you any sample code.

Like I said I'm no expert at this sort of thing, I prefer to work, if possible, with the data at it's source or another database eg Access.

How did you get the code you have?

Have you tried recording a macro when you go through Data>Get External Data...?

By the way how do you know it'll be faster in code?:)

It is a driver missing. What OS are you using? If 95 0r 98 try referencing ADO 2.5.

Quote:

---

Originally Posted by Norie

I'm sorry but I can't give you any sample code.

Like I said I'm no expert at this sort of thing, I prefer to work, if possible, with the data at it's source or another database eg Access.

How did you get the code you have?

Have you tried recording a macro when you go through Data>Get External Data...?

By the way how do you know it'll be faster in code?:)

---

Yes now I know your no expert but maybe I thought you could've help because you responded to my thread.



Thanks for responding austenr my OS is Microsoft Office 2003 I did trying to use that reference but it did work it might be some security issues with the AS/400 I probably will have to talk to one of the RPG programmers here at work. I'll let you know the outcome.

Thanks!:)

I would suggest you build your connection string through a .udl file, then use that as a datasource in your macro code. In my experience with AS400 - the connection strings normally contained Catalog= or Library= parameters.

If you are unfamilar with UDL's this should help

http://msdn.microsoft.com/library/de...aLinkFiles.asp

.02

Stan