Problem Connecting to Oracle 9i

[sdr0303]

I have this older VB6 application that I don't have the time to convert to VS2005 right now. I have the Oracle ODBC client installed and the DSN are created as system DSN's. They are not using the "Microsoft ODBC for Oracle" driver, instead they are using the actual Oracle driver. Ive tried several connection strings including:

cn.connectionstring = "Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"

cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN

cn.connectionstring = "Driver={Microsoft ODBC for Oracle};" & "UID=report;PWD=reportpw;DSN=nurev"
    
cn.connectionstring = "Driver={Microsoft ODBC for Oracle};Server=nurev;Uid=report;Pwd=reportpw;"

I keep getting the error handler from my function:

Public Function OpenDatabaseConnection() As Boolean
On Error GoTo errhandler
Dim connectionstring As String

    Set cn = New ADODB.Connection
    cn.Provider = "MSDAORA.1"
    'cn.connectionstring = "Provider=OraOLEDB.Oracle.1;Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"
    'cn.connectionstring = "Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"
    'cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN
    'cn.connectionstring = "Driver={Microsoft ODBC for Oracle};" & "UID=report;PWD=reportpw;DSN=nurev"
    cn.connectionstring = "Driver={Microsoft ODBC for Oracle};Server=nurev;Uid=report;Pwd=reportpw;"
    cn.Open
    OpenDatabaseConnection = True
    Exit Function
errhandler:
    MsgBox "Connection failed, Enter Login Parameters Again", vbCritical
    OpenDatabaseConnection = False
    Exit Function
End Function

...any ideas what the proper connection string is?

[GaryMazzone]

I SQL* Net installed on the machine and an entry in the TNSNames file made for the database? What is the exact error number and message coming back?

[sdr0303]

It's strange, I've tracked this down to something with the Oracle Client we are using. When I go back a version to v8.1.7.0 this works with no problem, however we are updating our databases and the new client v9.2.0.1.0 this appears to cause the problem. We are careful to install and uninstall using the Oracle universal installer and delete the old version completely as well.

Once the new version is installed we reboot, then create the DSN names in the MS ODBC administrator, using the new Oracle driver as the connection driver. We can connect to the database with several 3rd party tools like WinSQL and Quest TOAD as well as SQL Plus, but whenever we run these older VB apps they will not connect. I think it has something to do with either the connection string or the provider ("MSDAORA.1")?

As for an error, when I place a breakpoint on the message box in the debug window I get:

?error
Oracle error occurred, but error message could not be retrieved from Oracle.

?????

[sdr0303]

I actually got the connection to work by removing the reference to:

cn.Provider = "MSDAORA.1"

and cleaning up the connection string to be a pure DSN connection with:

cn.connectionstring = "PWD=" & gstrPassword & ";Persist Security Info=True;UID=" & gstrUser & ";DSN=" & gstrDSN
cn.Open

The problem now is that I keep getting:

"Run-time error 3265, ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application."

????

The problem is happening in the function:

Public Function ProcessSales_audit() As Boolean
'On Error GoTo errhandler
Dim strSQL As String
Dim strRec As String
Dim intseqNum As Integer
Dim rsdetail As ADODB.Recordset
Set rsmain = New ADODB.Recordset
Set rsdetail = New ADODB.Recordset
Dim iFileNum As Integer

'Get a free file handle
iFileNum = FreeFile

strSQL = "Select STORE_CD,TERM_NUM,TRN_DT FROM POS_TRN_LN " & _
        " WHERE STORE_CD IN (" & gstrStoresforSQL & ")  AND TRN_DT='" & Format(gstrDate, "dd-mmm-yyyy") & _
        "' AND STAT_CD<>'X' and (void is null or void = 'P')" & _
        " AND LN_TP NOT IN('SUB','TOT','VOD') group by STORE_CD,TERM_NUM,TRN_DT "
rsmain.Open strSQL, cn, adOpenKeyset, adLockReadOnly
If Not rsmain.EOF Then
    InitializeAccountArray
    InitializeNameArray
    'If the file is not there, one will be created
    'If the file does exist, this one will
    'overwrite it.
   ' On Error GoTo errfilepath
    Open gstrFilePath For Output As iFileNum

   ' On Error GoTo errhandler
    Do While Not rsmain.EOF()
        strSQL = "Select LN_TP,sum(AMT) AMT From POS_TRN_LN" & _
        " where STORE_CD='" & rsmain("STORE_CD") & "' and TERM_NUM='" & _
        rsmain("TERM_NUM") & "' and TRN_DT='" & Format(rsmain("TRN_DT"), "dd-mmm-yyyy") & _
        "' AND STAT_CD<>'X' and (VOID is null or VOID = 'P')" & _
        " and LN_TP not in ('SUB','TOT','VOD') group by LN_TP"
        
        rsdetail.Open strSQL, cn, adOpenStatic, adLockReadOnly
        If Not rsdetail.EOF() Then
            ReInitialise_Qty_Array
            Do While Not rsdetail.EOF()
                Select Case Trim(UCase(rsdetail("ln_tp")))
                    Case Is = "AMX"
                        garrAmts(CAMEX) = garrAmts(CAMEX) + rsdetail("AMT")
                    Case Is = "ATM"
                        garrAmts(CATM) = garrAmts(CATM) + rsdetail("AMT")
                    Case Is = "CHK"
                        garrAmts(CCHECKS) = garrAmts(CCHECKS) + rsdetail("AMT")
                    Case Is = "CSH"
                        garrAmts(CCASH) = garrAmts(CCASH) + rsdetail("AMT")
                    Case Is = "TCK"
                        garrAmts(CCASH) = garrAmts(CCASH) + rsdetail("AMT")
                    Case Is = "DIS"
                        garrAmts(CDISCOVER) = garrAmts(CDISCOVER) + rsdetail("AMT")
                    Case Is = "GFC"
                        garrAmts(CGIFTS_COUPONS) = garrAmts(CGIFTS_COUPONS) + rsdetail("AMT")
                    Case Is = "ID", "PRO", "TD", "TLD", "VCN", "EMP", "FIR", "IDA", "IDP", "IDS", "MM", "POL", "SEN", "STU", "LNL", "TDD", "TPD"
                        garrAmts(CSALES_DISCOUNTS) = garrAmts(CSALES_DISCOUNTS) - rsdetail("AMT")
                    Case Is = "MED"
                        garrAmts(CCASH) = garrAmts(CCASH) - rsdetail("AMT")
                    Case Is = "MER"
                        garrAmts(CCRS_ISSUED) = garrAmts(CCRS_ISSUED) + rsdetail("AMT")
                    Case Is = "MRR"
                        garrAmts(CCRS_RECIEVED) = garrAmts(CCRS_RECIEVED) + rsdetail("AMT")
                    Case Is = "MSC"
                        garrAmts(CMASTERCARD) = garrAmts(CMASTERCARD) + rsdetail("AMT")
                    Case Is = "PDO"
                        garrAmts(CPAID_OUT) = garrAmts(CPAID_OUT) - rsdetail("AMT")
                    Case Is = "RET"
                        garrAmts(CRETURNS) = garrAmts(CRETURNS) - rsdetail("AMT")
                    Case Is = "SAL"
                        garrAmts(CSALES) = garrAmts(CSALES) - rsdetail("AMT")
                    Case Is = "TAX"
                        garrAmts(CSALES_TAX) = garrAmts(CSALES_TAX) - rsdetail("AMT")
                    Case Is = "VIS"
                        garrAmts(CVISA) = garrAmts(CVISA) + rsdetail("AMT")
                End Select
                rsdetail.MoveNext
            Loop
        End If
        rsdetail.Close
        intseqNum = ((Val(rsmain("TERM_NUM") - 1)) * 100) + 1
        For inti = 0 To 14
            strRec = garrAccountNos(inti) & Right(rsmain("STORE_CD"), 3) & ".." & _
                     Format(rsmain("TRN_DT"), "yymmdd") & _
                     ".." & Right(rsmain("STORE_CD"), 2) & _
                     Lpad(rsmain("TERM_NUM"), 4) & ".." & _
                     Lpad(intseqNum, 3) & ".." & _
                     Space(10 - Len(AmtValue(garrAmts(inti)))) & AmtValue(garrAmts(inti)) & _
                     "..POS " & garrNames(inti) & " Store:  " & _
                     Store_Val(rsmain("STORE_CD")) & " Reg:  " & _
                     Right(rsmain("TERM_NUM"), 1) & "  .."

            Print #iFileNum, strRec
            intseqNum = intseqNum + 1
        Next
        rsmain.MoveNext
    Loop
    Close iFileNum
Else
    frmSA.lblProcessing.Caption = ""
    Screen.MousePointer = vbDefault
    MsgBox " No data available for the entered paramters", vbInformation
    ProcessSales_audit = False
    Exit Function
End If
rsmain.Close
ProcessSales_audit = True
frmSA.lblProcessing.Caption = ""
Screen.MousePointer = vbDefault
Exit Function
errhandler:
    frmSA.lblProcessing.Caption = ""
    Screen.MousePointer = vbDefault
    MsgBox "Errors occured while processing Sales Audit file", vbCritical
    ProcessSales_audit = False
    If rsmain.State = adStateOpen Then
        rsmain.Close
    End If
    If rsdetail.State = adStateOpen Then
        rsdetail.Close
    End If
    Exit Function
errfilepath:
     frmSA.lblProcessing.Caption = ""
     Screen.MousePointer = vbDefault
     MsgBox " Invalid file name/type, Could not open file - Cannot process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
     ProcessSales_audit = False
     Exit Function
End Function

...the first SQL statement pulls back an array that feeds the second statement. Something in this mechanism is causing the problem though?