Close ODBC connection to Oracle 10g Express Edition

From: Greg Strong <news.REMOVE_at_geedubeeu.com.invalid>
Date: Sun, 09 Jul 2006 13:04:05 -0500
Message-ID: <jkf2b2h6toa9lnn0ra7b2263pnrqaqigq1@4ax.com>

Hello All,

The short questions are

1 Do you know how to make DSN connection close in Access to Oracle 10g   Express Edition?

               &/or

2 Do you know how to make a DSN-less pass-through query work from

   Access 2k2 to Oracle 10g Express Edition?

I'm experimenting using Access 2k2 as front-end to Oracle 10g Express Edition. I've tried a DSN connection, and it works. The problem is that once Access creates the connection using a password and UserID from a form, it does NOT close the connection. The only way to close the connection is to close Access. This is not good if different users use the same workstation and have different rights.

Well I've tried a DSN-less connection. I got it to work to convert DSN linked tables to DSN-less linked tables per Doug Steele's code at http://www.accessmvp.com/djsteele/DSNLessLinks.html. However, when I replaced the pass-through query ODBC Connect String with the following:

,----- [ pass-through query ODBC Connect String ] | "ODBC;Driver={Oracle in XE};Dbq=XE;UID=MyUID;PWD=MyPswd;" `-----

I get an error. It reads as follows:

,----- [ Error on pass-through query run from Access or code ] | Reserved error(-7778); there is no message for this error `-----

The error occurs on the following line:

,----- [ VBA error line ]
| DoCmd.OpenQuery "qrySumInvcTest"
`-----

I've tried MS's driver without luck. So I'm running out of straws to grasp. So either I find the solution to closing a DSN connectin with Access, or I find a way to make DSN-less pass-through query work with Access. So far no luck. The only difference between the 2 types of connection is the connection string used. The code is below for both scenarios.

Thanks for any ideals!!!

======DSN============>Begin Code>===================================>
Sub TestQryDef()

    Dim wsCur As DAO.Workspace
    Dim dbCur As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String
    On Error GoTo CheckError     

    Set wsCur = DBEngine.Workspaces(0)
    Set dbCur = wsCur.Databases(0)
    Call SetConStr
    If strCnn = "" Then

        Exit Sub
    End If
    Set qd = dbCur.QueryDefs("qrySumInvcTest")     qd.Connect = "ODBC;" & strCnn
    qd.ReturnsRecords = True
    DoCmd.OpenQuery "qrySumInvcTest"
    qd.Connect = "ODBC;DSN=OracleXE;DATABASE=4GetU;;UID=NoGo;" & _

                    "PWD=4GetIt;"

    qd.Close
    dbCur.Close
    wsCur.Close
    Set qd = Nothing
    Set dbCur = Nothing
    Set wsCur = Nothing
    Exit Sub     

CheckError:

    If Err.Number = 3151 Then

        MsgBox "You must enter a valid UserID and Password!!!", _
            vbOKOnly, "UserID and Password"
        Set qd = Nothing
        Set dbCur = Nothing
        Set wsCur = Nothing
        Exit Sub

    End If
    MsgBox "Error Number: " & Err.Number & " Error Desc: " & _

        Err.Description, , "Error"
    Set qd = Nothing
    Set dbCur = Nothing
    Set wsCur = Nothing
End Sub
======DSN============<End Code<=====================================<

======PUBLIC VAR=====>Begin Code>===================================>
Public strCnn As String, strOConn As String, strMSOCnn As String
=====================<End Code<=====================================<

======CONNECT STR====>Begin Code>===================================>
Sub SetConStr()

    Dim strUID As String, strPswd As String     Forms("frmMain").Refresh
    strCnn = ""
    strOConn = ""
    If IsNull(Forms("frmMain").Controls("txtUserID").Value) Then

        MsgBox "Please enter your User ID!", , "Enter User ID"
        Exit Sub

    End If
    If IsNull(Forms("frmMain").Controls("txtPswd").Value) Then

        MsgBox "Please enter your Password!", , "Enter Password"
        Exit Sub

    End If
    strUID = Forms("frmMain").Controls("txtUserID").Value     strPswd = Forms("frmMain").Controls("txtPswd").Value     strCnn = "DSN=OracleXE;DATABASE=XE;;" & _

                "UID=" & strUID & _
                ";PWD=" & strPswd & ";"
    
'    strOConn = "Driver={Oracle in XE};" & _
'                    "Dbq=XE;" & _
'                "UID=" & strUID & _
'                ";PWD=" & strPswd & ";"
    strOConn = "ODBC;Driver={Oracle in XE};" & _
                    "Dbq=XE;" & _
                "UID=" & strUID & _
                ";PWD=" & strPswd & ";"
'    strOConn = "ODBC;Driver={Oracle in XE};" & _
'                    "DATABASE=XE;" & _
'                "UID=" & strUID & _
'                ";PWD=" & strPswd & ";"
    
    strMSOCnn = "Driver={Microsoft ODBC for Oracle};" & _
           "Server=DEDICATED;" & _
                "UID=" & strUID & _
                ";PWD=" & strPswd & ";"
    
    
    Debug.Print "strCnn: "; strCnn

    Debug.Print "strOConn: "; strOConn
    Debug.Print "strMSOCnn: "; strMSOCnn End Sub
======CONNECT STR====<End Code<=====================================<

======DSN-LESS=======>Begin Code>===================================>
Sub TestQD_DSNless()

    Dim wsCur As DAO.Workspace
    Dim dbCur As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String
    'On Error GoTo CheckError     

    Set wsCur = DBEngine.Workspaces(0)
    Set dbCur = wsCur.Databases(0)
    Call SetConStr
    If strOConn = "" Then

        Exit Sub
    End If     

    Set qd = dbCur.QueryDefs("qrySumInvcTest")     qd.Connect = strOConn
    qd.ReturnsRecords = True
    DoCmd.OpenQuery "qrySumInvcTest"
    'qd.Connect = "ODBC;DSN=OracleXE;DATABASE=4GetU;;UID=NoGo;" & _

                    "PWD=4GetIt;"
    

    qd.Close
    dbCur.Close
    wsCur.Close
    Set qd = Nothing
    Set dbCur = Nothing
    Set wsCur = Nothing
    Exit Sub     

CheckError:

    If Err.Number = 3151 Then

        MsgBox "You must enter a valid UserID and Password!!!", _
            vbOKOnly, "UserID and Password"
        Set qd = Nothing
        Set dbCur = Nothing
        Set wsCur = Nothing
        Exit Sub

    End If
    MsgBox "Error Number: " & Err.Number & " Error Desc: " & _

        Err.Description, , "Error"
    Set qd = Nothing
    Set dbCur = Nothing
    Set wsCur = Nothing
End Sub
=======DSN-LESS======<End Code<=====================================<

Thanks again!!!

-- 
Regards,

Greg Strong

Received on Sun Jul 09 2006 - 13:04:05 CDT