Solved

Where to find ODBC driver for AS400/DB2?

Posted on 2009-05-04
9
1,267 Views
Last Modified: 2013-12-06
I need to access a table in a DB2 database on an AS400 system. The goal is to create a linked table in MS Access so that additional relational data from other parts of the clients' business can be associated with records within that table.

I presume that I'll need to get an ODBC driver from IBM, but can't seem to find it on their website, much less determine the cost of it.

Could somebody please point me in the right direction?

Thanks in advance!

--Jon
0
Comment
Question by:CoastalData
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
I use a function to get data from a DB2 database on the AS/400.
Public Sub SampleCode()

    Dim strSQL As String, blnGood As Boolean, avarData() As Variant

    strSQL = "SELECT * FROM DB.TABLE WHERE FIELD='VALUE'"

    blnGood = AS400GetData("UserName", "Password", strSQL, "LPAR/SYSTEM", avarData)

    If blnGood Then

        ' use the data

    End If

End Sub
 

Public Function AS400GetData(ByVal strUserName As String, _

  ByVal strPassword As String, ByVal strSQL As String, _

  ByVal strSystem As String, ByRef avarData() As Variant) As Boolean

' set up error handling

On Error GoTo ErrorHandler

    ' set the initial state of the function

    AS400GetData = False

    ' open a connection to the AS400

    Dim cnn As New ADODB.Connection

    cnn.Open "Provider=IBMDA400;Data Source=" & strSystem & _

        ";User ID=" & strUserName & ";Password=" & strPassword & ";"

    ' using a recordset, get the data and field names

    Dim rst As New ADODB.Recordset

    With rst

        ' open the recordset

        .Open strSQL, cnn

        ' if data was found,

        If Not .EOF Then

            ' get the data

            avarData = .GetRows

            ' eliminate leading and trailing spaces in the data elements

            AS400TrimData avarData

            ' indicate data was retrieved

            AS400GetData = True

        End If

        .Close

    End With

    ' close the connection

    cnn.Close

    ' clean up

    Set rst = Nothing

    Set cnn = Nothing

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & Err.Number & vbNewLine & _

        "Description:" & Err.Description & vbNewLine & _

        "From:" & Err.Source, vbExclamation, _

        "AS400GetData Failed"

    Resume ExitHere

End Function

Open in new window

0
 
LVL 84
Comment Utility
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
IBM ODBC Driver ships as part of the installation media for IBM Client Access (iSeries Access, System i Access, whatever) that comes with every AS/400.  There is no additional fee for using or installing this product.

If you don't have or can't find your copy of the installation kit, IBM or your IBM Business Partner can help you order replacement media.

- Gary Patterson
0
 
LVL 3

Author Comment

by:CoastalData
Comment Utility
Interesting!

LSM: So it looks like MS provides an ODBC driver for DB2... I was thinking for sure I'd have to buy one from IBM! I don't yet see a download link from that page, but I'll dig some more and see what I can come up with, maybe I should've been looking on the MS site from the start.

CWood: Nice DSNless connection! However, I prefer to "link" to the table so that updates to the AS400 are always automatically reflected to the Access database. Nonetheless, did you have to install additional software to make that work? I don't see any evidence of that provider on my system, even though I have tons and tons of MS development software and sdks installed.

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 3

Author Comment

by:CoastalData
Comment Utility
Okay, I just noticed that that LSMs' link comes from the section of "Host Integration Server", which is actually what I first thought I'd need, but then I thought that that was overkill, that maybe I could get *just* the ODBC driver.

Anybody got a feel for HIS, what it is, and how it fits into the Windows Server picture? Do I need it all, can I use just parts, etc.?

Thanks!
0
 
LVL 3

Author Comment

by:CoastalData
Comment Utility
Gary: Aha, there we go, that must be what I'm missing, I need to speak to the other IT guy at the client site. No MS Host Integration Server needed? Our needs are so basically simple that I hate to add unneccessary puzzle pieces to support!
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 250 total points
Comment Utility
LSM's solution requires the Microsoft Host Integration Server product:

http://www.microsoft.com/HIServer/default.mspx

CWood's solution requires the IBM OLE DB Provider (Provider=IBMDA400) from the Client Access installation materials that I mentioned earlier.

The easiest, cheapest, most widely supported  thing to do is to just install the (free) drivers from the Client Access installation media.

http://www-03.ibm.com/systems/i/software/access/cd.html

- Gary Patterson
0
 
LVL 16

Expert Comment

by:Chuck Wood
Comment Utility
<Nonetheless, did you have to install additional software to make that work?>

The only additional software I have installed is the IBM Client Access.
0
 
LVL 3

Author Closing Comment

by:CoastalData
Comment Utility
Thank you very much, that's exactly what I needed to know!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Just about everyone has an old PC laying around.  Ask anyone in the IT industry, whether they are a professional or play in it as a hobby.  From outdated Desktops to cheap "throwaway" laptops, they are all around and not as hard to "fix up" as you m…
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now