asked on

ADO connection to AS/400 using Excel VBA

I'd like to write a VB script in excel to establish a connection to AS400.
After I run below scrap to open the connection in myCon.Open,
popup windows arise for login and pwd.

1) Can I place the login & pwd into the myCon.Open option?
(I checked IBM redbooks that it seems cannot)

2) I am fail to sendkeys to the popup to send login/pwd, don't know why.

3) How I can know I have successfully connect to the data source? Can I open a pComm session after the connect?

Thanks in advance!

Sub Connect()
Dim myCon As ADODB.Connection
Dim myRS As ADODB.Recordset
 
Set myCon = New ADODB.Connection
'400 CONNECTION
myCon.Open "Provider=IBMDA400;Data Source=SYSTEMNAME;", "", ""
 
...
...
(then login windows pop up as attached)
 
End Sub

Open in new window

sample1.JPG

Gary Patterson, CISSP

forget about sendkeys to the password box. Just specify the correct parameters in your connection string.

I'm not sure what documentation you are looking at, but the Help files that come with the Client Access Programmer's toolkit are the "definitive" documentation for the OLE DB provider, and you will find these parameters documented there. Also documented in the examples in the old OLE DB Redbook.

Just add the "User ID='myuserid';Password='mypassword';" parameters to your connection string (for the IBMDA400 provider):

http://www.connectionstrings.com/as-400

If your connection attempt fails, you will receive an error, unless you are ignoring errors with an On Error Resume Next. In that case, you will need to check the Err object:

http://msdn.microsoft.com/en-us/library/ms677489(VS.85).aspx

Post back if you need anything elst/

- Gary Patterson

hikingexport

ASKER

Thanks.
But what will I expect to see after I success the "connection"?
can i have some way to test, say, send some testing things or gettig some dummy info?

ASKER CERTIFIED SOLUTION

Gary Patterson, CISSP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hikingexport

ASKER

Thanks Gary.
I have just tested with mycon.State and it returns 1
i.e. success to connect.
I am not going to write the query part yet so can't chk with sth that needs response with AS400.

Gary Patterson, CISSP

Great - glad it worked for you.

- Gary Patterson