Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

adPromptNever not working with DB2 Connection

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
c9k9h
Asked:
c9k9h
1 Solution
 
Chuck WoodCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now