Excel Help for Using Excel To Connect To As400 Database To Retrieve Data

Excel Help for Using Excel To Connect To As400 Database To Retrieve Data in Automating Data Analysis Tasks

Using Excel To Connect To As400 Database To Retrieve Data

Rate this:

(3/5 from 1 vote)

Hi All,

I am a new VBA programmer. I hope someone can help me. I am trying to use Excel to connect to a AS400 and run a query with parameter to download some data. I copy some codes from the Internet but when I run the codes, it gave me the following error message

"Compile error. User defined type not found"

It highlight the codes "Dim cnnConnect As ODBC.Connection"

Any response is very much appreciated.

My codes as follows:

VBA Code:

Sub AS400Connect()
Dim cnnConnect As ODBC.Connection
Dim rstRecordset As ODBC.Recordset
Set cnnConnect = New ADODB.Connection
cnnConnect.Open "Provider=IBMDA400;" & _
    "Data Source=EDCSYSB;" & _
    "User ID=daong;Password=qaz123wsx;"
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
    Source:="Select OKISBN, OKBQTY From OETHDRP where OLOPRT = 'SQ' ", _
    ActiveConnection:=cnnConnect, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockReadOnly, _
    Options:=adCmdText
With ActiveSheet.QueryTables.Add( _
        Connection:=rstRecordset, _
        Destination:=Range("A1"))
    .Name = "Contact List"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With

End Sub

Daivd Ong

Posted by davidong328 on 26 Dec 2010

Replies - Displaying 1 to 1 of 1

Order Replies By: Most recent | Chronological | Highest Rated

Mixing ODBC with ADODB

Rate this:

(3/5 from 1 vote)

It seems that you need to replace:

VBA Code:

Dim cnnConnect As ODBC.Connection

With:

VBA Code:

Dim cnnConnect As ADOBC.Connection

You will also need to make this change with the declaration of the Recordset object.

Excel Business Forums Administrator

Posted by Excel Helper on 26 Dec 2010

Displaying page 1 of 1

Using Excel to connect to AS400 database to retrieve data

Excel Help for Using Excel To Connect To As400 Database To Retrieve Data in Automating Data Analysis Tasks

Using Excel To Connect To As400 Database To Retrieve Data

Excel templates and solutions matched for Using Excel to connect to AS400 database to retrieve data: