Solved

adPromptNever not working with DB2 Connection

Posted on 2008-10-28
1
324 Views
Last Modified: 2013-11-27
I'm trying to get the correct connection string down to connect to a DB2 table that is linked to a query. The connection string itself appears to be correct as it is not throwing any errors, however, I am still getting prompted for a username and password even though it is embedded in the connection string. I've tried this same method with Teradata tables and it works fine...  Any ideas?

Private Sub CalcKim_Click()
Dim cnUDB As ADODB.Connection
Set cnUDB = New ADODB.Connection
With cnUDB
.Provider = "IBMDADB2"
.ConnectionString = "Data Source=DB2PSN;UID=c1teh;PWD=test1"
.Properties("Prompt") = adPromptNever
.Open
End With


This works

as does this:

cnUDB.Open "Provider=IBMDADB2;Data Source=DB2PSN;UID=c1teh;PWD=test1"

no errors, yet still prompting for user and pass...
0
Comment
Question by:c9k9h
1 Comment
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 500 total points
ID: 22825851
If you have IBM Client Access or IBM iSeries Access for Windows installed on your computer, you can use this code:
'===========================================================

'  requires:

'    reference to:

'       Microsoft ActiveX Data Objects 2.5 Library or later

'===========================================================

' set up global variables

Public strPassword As String

Public strUserName As String
 

' requires IBM Client Access or IBM iSeries Access for Windows be installed on the local computer

Public Function GetAS400Data(ByRef avarData() As Variant, ByVal strSQL As String, _

  ByVal strUserName As String, ByVal strPassword As String, ByVal strLibrary As String, _

  ByVal strSystem As String, Optional ByVal strRows As String = "") As Boolean

' set up error handling

On Error GoTo ErrorHandler

    Dim rst As New ADODB.Recordset, intI As Integer, strConnect As String

    ' set the initial state of the function

    GetAS400Data = False

    ' create the connect string

    strConnect = CreateConnectString(strUserName, strPassword, strLibrary, strSystem)

    With rst

        ' turn off error handling

        On Error Resume Next

        ' open the recordset

        .Open strSQL, strConnect

        ' if there was an error

        If err.Number <> 0 Then

            ' clear the error

            err.Clear

            ' turn error handling back on

            On Error GoTo ErrorHandler

            ' open the recordset

            .Open strSQL, strConnect

        End If

        ' turn error handling back on

        On Error GoTo ErrorHandler

        If Not .EOF Then

            ' if the rows string is blank,

            If strRows = "" Then

                ' load the data into the array

                avarData = .GetRows()

            Else

                ' load the number of rows into the array

                avarData = .GetRows(strRows)

            End If

            DoEvents

            ' set the function state to true

            GetAS400Data = True

        End If

        .Close

    End With

    Set rst = Nothing

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbCrLf & "Description:" & err.Description & vbCrLf & "From:" & _

        err.Source & vbCrLf & "In:basAS400Data_GetAS400Data", vbExclamation, "Program Error"

    Resume ExitHere

End Function
 

' Creates an AS/400 ADODB connection string using the Client Access ODBC driver

Public Function CreateConnectString(ByVal strUserName As String, ByVal strPassword As String, _

    ByVal strLibrary As String, ByVal strSystem As String) As String

' set up error handling

On Error GoTo ErrorHandler

    ' create the AS/400 connection string

    CreateConnectString = "Provider=MSDASQL.1;" & _

        "Driver=Client Access ODBC Driver (32-bit);" & _

        "Persist Security Info=True;" & _

        "User ID=" & strUserName & ";" & _

        "Password=" & strPassword & ";" & _

        "Connect Timeout=120;" & _

        "General Timeout=120;" & _

        "Extended Properties=" & _

        "SYSTEM=" & strSystem & ";" & _

        "DBQ=" & strLibrary & ";" & _

        "CMT=0;NAM=0;DFT=5;DSP=1;TFT=0;TSP=0;DEC=0;XDYNAMIC=1;" & _

        "RECBLOCK=2;BLOCKSIZE=512;SCROLLABLE=0;TRANSLATE=1;" & _

        "LAZYCLOSE=1;LIBVIEW=0;REMARKS=1;CONNTYPE=0;SORTTYPE=0;" & _

        "PREFETCH=0;DFTPKGLIB=QGPL;LANGUAGEID=ENU;SORTWEIGHT=0;" & _

        "SSL=2;MAXFIELDLEN=32;COMPRESSION=1;ALLOWUNSCHAR=0;"

ExitHere:

    Exit Function

ErrorHandler:

    MsgBox "Error:" & err.Number & vbCrLf & "Description:" & err.Description & vbCrLf & "From:" & _

        err.Source & vbCrLf & "In:basAS400Data_CreateConnectString", vbExclamation, "Program Error"

    CreateConnectString = ""

    Resume ExitHere

End Function

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 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

20 Experts available now in Live!

Get 1:1 Help Now