Home > Forum Home > Automating Data Analysis Tasks > Using Excel to connect to AS400 database to retrieve data Share

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


Forum TopicPost Reply Login

Using Excel To Connect To As400 Database To Retrieve Data

Rate this:
(3/5 from 1 vote)
ConfusedHi 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 on
 
Replies - Displaying 1 to 1 of 1Order Replies By: Most recent | Chronological | Highest Rated
Surprised
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 on
 Displaying page 1 of 1 

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

Solutions: Pricing and Breakeven Analysis Database Report Builder Stock Trading System Spread Trading System Batch Replace Tools Project Planner Statistics Training Useful Add-ins OPC Data Connection Dependency Auditing OCX Office Viewer Component Spreadsheet Search Functions and Formulas SQL Statements Cube Functions