Excel >> VBA ODBC connect to DB2

by R2Vvcmdl » Fri, 12 Nov 2004 03:29:01 GMT

I would like to use Excel to retrieve data from a DB2 database.
Specifically, I would like to use VBA and connect to the database (which is
already available through ODBC) and use and SQL string to query the database.

Thanks for any help or sites that have code examples.

Excel >> VBA ODBC connect to DB2

by jamiecollins » Fri, 12 Nov 2004 17:47:03 GMT

George wrote ...

Try searching the google groups Usenet archive for code exmaples. Suggested search:

http://groups.google.com/groups?q=group :microsoft.public.excel.programming+ado+db


Similar Threads

1. Connecting to a ODBC data source in Excel VBA - Tutorial Recommendations


I'm looking at using Excel to produce analysis from an ODBC link to a third
party application through Microsoft Query. To make it as simple to use as
possible I'd like to use VBA as much as possible.

While I have a good knowledge of Excel, VBA and ADO/DAO, I'm completely
unfamiliar with using Excel to obtain data through an ODBC link.

Simply, can anyone point me in the right direction for any web resources /
books / articles etc that would act as a introduction.

I can already use MS query to link to the app and retrieve data, however
there are things like programmatically defining query parameters and so on
that I would like to be able to do.

Just for some background, I'm going to be looking at reporting from a 3rd
party application (a warehouse management system named ATMS). This app. can
have an ODBC link set up. However as there will often be more than 65000
rows of data I'm going to have to filter it before I fetch it.

I realise that this is possible with Access (and indeed would probably be
easier in some important aspects), however because any data fetched *must*
be read only and because I'm probably going to want to do some quite
advanced analysis I have decided on using Excel.

Any and all advice is gratefully received.

Kind thanks


2. Connect to Oracle Database through ODBC with VBA - Excel

3. My Excel crashes when the VBA code calls a C dll which connects to ODBC

I have and Excel spreadsheet with a VBA macro. The macro calls a dll
which in turn connects to Oracle database using ODBC. This causes the
Excel spreadsheet to crash. Has anybody experienced such a problem and
how did you resolve it.


4. Export from Excel to DB2 via ODBC - Excel

5. issue with ODBC connection to DB2


I am connecting to DB2 from Excel using IBM OBDC driver. I am able to
connect and getting the data but.. there is a window that pops up
asking me to select the database.. is there a way to get rid of it

here is my code.
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;Driver={IBM DB2 ODBC
        , Destination:=Range("A1"))
        .CommandText = strQueryText
        .Name = strQueryName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .EnableRefresh = True
        .EnableEditing = False
        .Refresh BackgroundQuery:=False --- this row pops the
    End With

I also see the error message in the window "SQL1013N  The database
alias name or database name "" could not be found.  SQLSTATE=42705"

looks like i am not defining the database properly..

Can someone please help ..


6. ODBC error to Access to DB2 - Excel

7. Accessing DB2 on an AIX server via ODBC from MS Excel

Windows XP Pro SP2
Excel 2003 SP2
IBM DB2 Personal Edition accessing a DB2 database on an AIX server via
the DB2 ODBC driver

I am accessing an external DB via MS Excel using Data-->Import External
Data -->New Database Query.  Last week I connected quickly and
reliably.  As of Monday morning, It now takes 7.5 minutes to connect to
the DB and all the while the hard disk is spinning constantly.  Another
tool no longer connects at all with the following message, "[IBM][CLI
Driver] SQL1336N  The remote host "dbt_rpts" was not found.". While
another component of the same tool connects fine.  I can also connect
from the DB2 Control Center without a problem.

Has anyone experienced such a selective degradation in access time and
have an idea for a fix?

Thanks, Jim D.

8. Excel VBA read DB2/400 *without* journaling - AS/400