VBA Connection Strings

gazdzid used Ask the Experts™

I would like to find the simplest way to bring a data set from AS400 Iseries to Excel.

Please note the perspective must be from excel macro, which means the Excel macro must utilize a connection string that would connect to the iseries then pull the data into excel,

I have searched the web for about 2 hours with very little success.  (I am new to working with VBA connection Strings.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
there is a web site called www.connectionstrings.com that has everything you need
but in order to connect from excel to iseries, you will need some middle ware product such as db2 connect
you can't just access directly


I am so-so at figuring things out, I am familiar with coding strings in Java to connect to Oracle.  However, I am having difficulties with figuring out how to connect excel to AS400.
Theo KouwenhovenApplication Consultant

Driver={iSeries Access ODBC Driver};System=my_system_name;Uid=myUsername;Pwd=myPassword;

What part don't you understand?


Thank you for your quick responce.

 this code appears like I am use to seeing.
I am receiving a compile error (invalid character), which appears in the first Parenthesis "("

My Code is as follows:
Driver={iSeries Access ODBC Driver};System=Sssssss;Uid=MQMQMQ;Pwd=jjjjjj
NorieAnalyst Assistant


Is that all the code you have?

Where are you assigning the connection string to a variable or using it to open a connection?

Off the top of my head the code to open the connection should look something like this.
strConn = "Driver={iSeries Access ODBC Driver};System=Sssssss;Uid=MQMQMQ;Pwd=jjjjjj"

' conn here is a connection object

conn.ConnectionString = strConn


Open in new window

Theo KouwenhovenApplication Consultant

Se here for a clear example VBA Excel

Sub transfer()

Dim varState As String

Set CS = CreateObject(“ADODB.Connection”)
Set RS = CreateObject(“ADODB.Recordset”)

‘Retrieve values from Cells on the sheet to use as selections in the query below
varState = ActiveSheet.Range(“C2¿).Value
varFrom_Date = ActiveSheet.Range(“B3¿).Value
varTo_Date = ActiveSheet.Range(“B4¿).Value

‘ ISeries connection String
‘ConnectString = “Driver={ISeries Access ODBC Driver};System=;Uid=xxxxx;Pwd=xxxxx;Library=PWRDTA41;QueryTimeout=0¿
ConnectString = “Driver={ISeries Access ODBC Driver};System=;Library=PWRDTA41;QueryTimeout=0¿

CS.Open (ConnectString)

SqlString = “SELECT hhicusn , C.FFDCNMB, C.FFDSTEB, hhiclsn, SUM(hhiqysa), SUM(hhiexsn), SUM(hhiexac) ” & _
” FROM pwrdta41.hhiorddp ” & _
” left outer join PWRDTA41.FFDCSTBP c” & _
” ON hhicusn = c.ffdcusn and” & _
” hhidivn = c.ffddivn and” & _
” c.ffdcmpn = hhicmpn and” & _
” c.ffddptn = hhidptn” & _
” WHERE hhidtei between ” & varFrom_Date & ” and ” & varTo_Date & _
” and hhiclsn = ’105' and c.ffdsteb = ‘” & varState & “‘” & _
” GROUP BY hhicusn, c.ffdcnmb, c.ffdsteb, hhiclsn” & _
” ORDER BY hhicusn”

‘Message box can be used for debugging the SQL statement
‘MsgBox (SqlString)

RS.Open SqlString, CS

‘Clear Previous contents of Cells

‘copy the Recordset to excel sheet starting at A7
ActiveSheet.Range(“A7¿).CopyFromRecordset RS

‘Close Connection and RecordSet

‘Reset cell back to A1

End Sub

Open in new window



Thank you for the clearly written code (conn strings are kind of new to me.

My code works fine all the way to line 34 where I recevie an error

Run-time error -214217900 (80040e14)

IBM System i Access ODBC Driver DB2 for i5/os sql0104-token; was not valid.
Valid tokens, From ONTO.

can you explain the following it appears that one string would over write the other in my code I have the second line commented out.

ConnectString = "Driver={iSeries Access ODBC Driver};System=jjjj;Uid=Mjjjj;Pwd=jllll"
'ConnectString = “Driver={ISeries Access ODBC Driver};System=jjjjjj;Library=jjjjjjF;QueryTimeout=0


Is this in Access and would it be different in excel
Application Consultant
the one with the quote in front of it is comment, can be removed,

the error you get "was not valid. Valid tokens, From ONTO"
Looks like an error in your SQL string, remember all quoted fields in AS/400 are single quotes

Please post your SQL statment here, so that We can see the problem
NorieAnalyst Assistant

Probably a stupid question, but have you replaced the values for system, user id (uid) and password (Pwd) with the relevant ones for your setup.

PS The connection and recordset part aren't Excel specific as far as I can see, perhaps murphey2 to can confirm (or not) that.


Thanks Murphy2,

It works!!

I leleft both strings uncommented and It seems as though I had to add the databaseName.tableName as seen below

SqlString = "SELECT * FROM xxxxx.yyyyy"

Be on the look out I may have to post on handling  if there is no data to be pulled.

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