VBA Connection Strings

gazdzid
gazdzid used Ask the Experts™
on
Hello,

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.
Comment
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

Author

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

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

What part don't you understand?

Author

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

Commented:
gazdzid

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

conn.Open

Open in new window

Theo KouwenhovenApplication Consultant

Commented:
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=10.1.4.1;Uid=xxxxx;Pwd=xxxxx;Library=PWRDTA41;QueryTimeout=0¿
ConnectString = “Driver={ISeries Access ODBC Driver};System=10.1.4.1;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
Cells.Select
ActiveSheet.Range(“A7:Z65535¿).ClearContents

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

‘Close Connection and RecordSet
RS.Close
CS.Close

‘Reset cell back to A1
ActiveSheet.Range(“A1¿).Select

End Sub

Open in new window

Author

Commented:
murphey2

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.

Additionally,
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

Author

Commented:
Is this in Access and would it be different in excel
Application Consultant
Commented:
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

Commented:
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.

Author

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