ADO connection to AS/400 using Excel VBA

hikingexport used Ask the Experts™
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
myCon.Open "Provider=IBMDA400;Data Source=SYSTEMNAME;", "", ""
(then login windows pop up as attached)
End Sub

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gary PattersonVP Technology / Senior Consultant

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):

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:

Post back if you need anything elst/

- Gary Patterson


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?
VP Technology / Senior Consultant
If the mycon.Open line does not return an error, then you are successfully connected.  You won't "see" anything.  You can always query your connection object's "State" property:

You get an error if the connection fails.  If it succeeds, then your program just keeps running.

If you just want to prove to yourself that you are connected, then execute a command or run a query or perform some other action over the connection that requires a response from the AS/400.

- Gary Patterson


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 PattersonVP Technology / Senior Consultant

Great - glad it worked for you.

- Gary Patterson

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial