VBA and ADO

[DeadManWalks]

So I am trying to connect to the 400 using ADO with in excel.

 objConn.ConnectionString = "DRIVER=Client Access ODBC Driver (32-bit); " & _
                           "SYSTEM = 205.205.205.1; USER ID = MYID; PASSWORD = MYPASS" 

objConn.Open

objRs.Open "SELECT ITM05 FROM MYLIB.ITEMSTEP", objConn 

and I am getting the following error that I trapped using objConn.Errors(X).Description

"ExtendedDynamic Support Disabled"

Any thoughts or point me to a simple example of using ADO from Excel

Thanks.

[Red]

Re: VBA and ADO

Well its not really my area but I googled it and came up with a couple possibles
------------------------------------------------------
'Extended Dynamic Support Disabled'

Cause:
The message is generated by the ODBC driver for the AS/400. The message occurs because there is a mismatch between the SQL package object on the AS/400 server and the ODBC.INI settings for the application that uses the package object.

In the ODBC.INI file, the following line was set incorrectly:

Package = QGPL/LABELMA (FBA),2,0,1

Solution:
The last three (3) values on the Package= line tells the ODBC driver about the use of the AS/400 package object when running an application. Please verify that the last three values (usage, pkg full option and pkg not used option) on the Package= line are set to one of the following:

a. Package=QGPL/LABELMA (FBA),0,0,0
or
b. Package=QGPL/LABELMA (FBA),2,1,1

Please refer to your Client Access AS/400 help documentation for more information.
-------------------------------------------------------------------------
Or - from IBM
--------------------------------------------------------------------------------
Extended dynamic disabled error
The Extended dynamic support disabled message is seen when a SQL package is unusable for some reason.

On older servers, this message can be seen when a user connects with a different default library than the user who created the package. To workaround this message you can either:

1) Delete the SQL package on the system so that when you run your application the package will be created with your default package settings

2) Change the SQL default library connection string setting to match the setting that is saved with the SQL package

3) Switch theReturn code for unusable package ODBC DSN setting to Ignore or

Warning. Alternatively, you can get this same behavior by setting the PKG connection string setting.

4) Disable the XDYNAMIC connection string setting

---------------------------------------------------------------------------------------

Hope something there helps. Let us know

[DeadManWalks]

Re: VBA and ADO

Wow. How about if any one has a simple example of pulling data into excel. I have written stuff that does a data transfer behind the scene and then open that file. I would prefer an ADO example.

[littlepd]

Re: VBA and ADO

This code utilizes the OLE DB provider that comes with iSeries Access for Windows. If you don't supply user id and password, you will be prompted for them when the "objConn.Open" statement is executed.

Set objConn = New ADODB.Connection
objConn.Open "Provider=IBMDA400;" & _
             "Data Source=205.205.205.1;"

Set objRS = New ADODB.Recordset
objRS.Open "SELECT ITM05 FROM MYLIB.ITEMSTEP", objConn

lngOffset = 0
If Not objRS.EOF Then
    'add field headers
    For Each objFields In objRS.Fields
        Sheets2.Offset(0, lngOffset).Value = objFields.Name
        lngOffset = lngOffset + 1
    Next objFields
    Sheet2.Resize(1, objRS.Fields.Count).Font.Bold = True

    'dump the recordset to the spreadsheet
    Sheet2.Offset(1, 0).CopyFromRecordset objRS
    objRS.Close
    Sheet2.UsedRange.EntireColumn.AutoFit
Else
    MsgBox "Error: no records returned.", vbCritical   
End If

[DeadManWalks]

Re: VBA and ADO

Thanks for the reply.