General ODBC error AS400

[stuart62]

Hi,

I am retrieving data from AS/400 database to a worksheet
using QueryTable and I have the following problem.

My code is as follows:

'*********************************************
strConn = "ODBC;DRIVER={Client Access ODBC Driver (32-
bit)};SYSTEM=BBIS01;DBQ=QGPL " & _
"AULT1F2000;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/
DEFAULT(IBM)" & _
",2,0,1,0,512; TRANSLATE=1;SIGNON=1;UID=myUID;"

strSQL = "SELECT COUNT(*) FROM SLP05"

Sheets(2).Activate

Set oQT = ActiveSheet.QueryTables.Add( _
Connection:=strConn, _
Destination:=Range("A1"), _
Sql:=strSQL)

oQT.Refresh
'*********************************************


However, the program got failed at the line ".Refresh" and
the error message is:


" Run-time error '1004':
General ODBC error "

Anyone knows how to solve this problem?
thanks a lot.

Stuart

 

[stuart62]

Hi,

I am retrieving data from AS/400 database to a worksheet
using QueryTable and I have the following problem.

My code is as follows:

'*********************************************
strConn = "ODBC;DRIVER={Client Access ODBC Driver (32-
bit)};SYSTEM=BBIS01;DBQ=QGPL " & _
               "AULT1F2000;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/
DEFAULT(IBM)" & _
                ",2,0,1,0,512; TRANSLATE=1;SIGNON=1;UID=myUID;"

strSQL = "SELECT COUNT(*) FROM SLP05"

    Sheets(2).Activate

    Set oQT = ActiveSheet.QueryTables.Add( _
        Connection:=strConn, _
        Destination:=Range("A1"), _
        Sql:=strSQL)

    oQT.Refresh
'*********************************************

However, the program got failed at the line ".Refresh" and
the error message is:

" Run-time error '1004':
  General ODBC error "

Anyone knows how to solve this problem?
thanks a lot.

Stuart

Fixed this now as was problem with SQL, needed to specift library in
connection string, however, now want to connect data to an
ADODB.Recordset and get the following error:

[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified

error occurs when executing this:
rs.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly,
adCmdText

Must be a problem with my connection string, I am using Citrix so
can't add an ODBC connection, query tables don't need this but seems
to be required for recordsets. Would using DAO instead of ADO be a
better option.

Any assistance would be greatly appreciated.

Thanks.

Stuart

 

[dmoney]

make sure that you have microsoft activex data objects 2.7 library selected
in your references.

 

[dmoney]

here is a great website for connection issues
http://www.carlprothman.net/Default.aspx?tabid=81

Hi,

I am retrieving data from AS/400 database to a worksheet
using QueryTable and I have the following problem.

My code is as follows:

'*********************************************
strConn = "ODBC;DRIVER={Client Access ODBC Driver (32-
bit)};SYSTEM=BBIS01;DBQ=QGPL " & _
"AULT1F2000;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/
DEFAULT(IBM)" & _
",2,0,1,0,512; TRANSLATE=1;SIGNON=1;UID=myUID;"

strSQL = "SELECT COUNT(*) FROM SLP05"

Sheets(2).Activate

Set oQT = ActiveSheet.QueryTables.Add( _
Connection:=strConn, _
Destination:=Range("A1"), _
Sql:=strSQL)

oQT.Refresh
'*********************************************

However, the program got failed at the line ".Refresh" and
the error message is:

" Run-time error '1004':
General ODBC error "

Anyone knows how to solve this problem?
thanks a lot.

Stuart

Fixed this now as was problem with SQL, needed to specift library in
connection string, however, now want to connect data to an
ADODB.Recordset and get the following error:

[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified

error occurs when executing this:
rs.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly,
adCmdText

Must be a problem with my connection string, I am using Citrix so
can't add an ODBC connection, query tables don't need this but seems
to be required for recordsets. Would using DAO instead of ADO be a
better option.

Any assistance would be greatly appreciated.

Thanks.

Stuart