Forum Discussion

Manoj8690's avatar
Manoj8690
New Contributor
5 years ago

db2/as400 load data in excel sheet

Hi Folks, Can anyone help me to set connection with DB2/AS400 data base and load data in excel sheet. Thanks, Manoj  
  • Manoj8690's avatar
    Manoj8690
    5 years ago

    Thanks Everyone for your suggestion!!

    Now I am able to fetch details from Database with below code.

    But, could not able to store in excel... Can anyone help me for copy/save data from recordset to excel.

     

    Sub TestADO
    Dim Conn, Rs , strSql
    Const adOpenStatic = 0
    Const adLockOptimistic = -1
    Const adCmdText = "&H0001"

    Set Conn = CreateObject("ADODB.Connection")
    strSql = "SELECT * from TableName LIMIT 20"

    Conn.ConnectionString = "Provider=MSDASQL.1;" & "Persist Security Info=False;" & "User ID=NA;" & "Extended Properties= " & "DSN=NA;SYSTEM=NA;UID=NA;DBQ=NA;DFTPKGLIB=NA;LANGUAGEID=ENU;PKG=NA/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;"

    Conn.Open
    ' Create a recordset
    Set Rs = CreateObject("ADODB.Recordset")
    ' Open the recordset and get the Orders table from the OrdersDB database

    'Rs.Open strSql, Conn, 3, 1, 2 ' adOpenStatic, adLockReadOnly, adCmdTable

    Rs.Open strSql, Conn, adOpenStatic, adLockReadOnly, adCmdText

    Log.AppendFolder "Customer names"
    Rs.MoveFirst
    While Not Rs.EOF
    Log.Message Rs.Fields.Item("NA").Value
    Rs.MoveNext
    WEnd
    Rs.Close
    Conn.Close
    End Sub

BenoitB's avatar
BenoitB
Community Hero

On DB2/AS400 it's not standard connection.

Some prerequesites are mandatory on the AS400 depending the kind of connection.

 

And you need iseries odbc access driver  (need to be registered to IBM)

https://www.ibm.com/support/pages/odbc-driver-ibm-i-access-client-solutions

 

Some other infos here:

https://www.nicolas-housset.fr/installer-le-pilote-odbc-pour-db2/

 

You can also use a third party tool allowing you to make software (and not driver) gateway.

https://stackoverflow.com/questions/20634843/configure-squirrel-sql-client-to-work-for-db2-as400

 

 

 

 

 

sonya_m's avatar
sonya_m
SmartBear Alumni (Retired)
5 years ago

Thanks for help Benoit, Wamboo!

 

Manoj8690 please let us know how you solved this and mark the most helpful reply as a solution!

  • Manoj8690's avatar
    Manoj8690
    New Contributor
    5 years ago

    Thanks Everyone for your suggestion!!

    Now I am able to fetch details from Database with below code.

    But, could not able to store in excel... Can anyone help me for copy/save data from recordset to excel.

     

    Sub TestADO
    Dim Conn, Rs , strSql
    Const adOpenStatic = 0
    Const adLockOptimistic = -1
    Const adCmdText = "&H0001"

    Set Conn = CreateObject("ADODB.Connection")
    strSql = "SELECT * from TableName LIMIT 20"

    Conn.ConnectionString = "Provider=MSDASQL.1;" & "Persist Security Info=False;" & "User ID=NA;" & "Extended Properties= " & "DSN=NA;SYSTEM=NA;UID=NA;DBQ=NA;DFTPKGLIB=NA;LANGUAGEID=ENU;PKG=NA/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;"

    Conn.Open
    ' Create a recordset
    Set Rs = CreateObject("ADODB.Recordset")
    ' Open the recordset and get the Orders table from the OrdersDB database

    'Rs.Open strSql, Conn, 3, 1, 2 ' adOpenStatic, adLockReadOnly, adCmdTable

    Rs.Open strSql, Conn, adOpenStatic, adLockReadOnly, adCmdText

    Log.AppendFolder "Customer names"
    Rs.MoveFirst
    While Not Rs.EOF
    Log.Message Rs.Fields.Item("NA").Value
    Rs.MoveNext
    WEnd
    Rs.Close
    Conn.Close
    End Sub

    • BenoitB's avatar
      BenoitB
      Community Hero
      5 years ago

      Great !

      I suggest you to create a new post about this subject on Excel .. but if you search a little into the forum i think solution is already written 😉