Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Close ODBC connection to Oracle 10g Express Edition

Re: Close ODBC connection to Oracle 10g Express Edition

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 9 Jul 2006 13:14:24 -0700

<div class="mail">
<address class="headers">
<span id="from">
<dfn>From</dfn>: Charles Hooper <hooperc2000_at_yahoo.com>
</span>
<span id="date"><dfn>Date</dfn>: 9 Jul 2006 13:14:24 -0700</span>
<span id="message-id"><dfn>Message-ID</dfn>: <1152476064.761619.153050_at_h48g2000cwc&#46;<!--nospam-->googlegroups.com>
</span>
</address>

<br>

Greg Strong wrote:
<br>
<i class="quotelev1">> Hello All,</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> The short questions are</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> 1 Do you know how to make DSN connection close in Access to Oracle 10g</i><br>
<i class="quotelev1">> Express Edition?</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> &/or</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> 2 Do you know how to make a DSN-less pass-through query work from</i><br>
<i class="quotelev1">> Access 2k2 to Oracle 10g Express Edition?</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> I'm experimenting using Access 2k2 as front-end to Oracle 10g Express</i><br>
<i class="quotelev1">> Edition. I've tried a DSN connection, and it works. The problem is that</i><br>
<i class="quotelev1">> once Access creates the connection using a password and UserID from a</i><br>
<i class="quotelev1">> form, it does NOT close the connection. The only way to close the</i><br>
<i class="quotelev1">> connection is to close Access. This is not good if different users use</i><br>
<i class="quotelev1">> the same workstation and have different rights.</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> Well I've tried a DSN-less connection. I got it to work to convert DSN</i><br>
<i class="quotelev1">> linked tables to DSN-less linked tables per Doug Steele's code at</i><br>
<i class="quotelev1">> http://www.accessmvp.com/djsteele/DSNLessLinks.html. However, when I</i><br>
<i class="quotelev1">> replaced the pass-through query ODBC Connect String with the following:</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> ,----- [ pass-through query ODBC Connect String ]</i><br>
<i class="quotelev1">> | "ODBC;Driver={Oracle in XE};Dbq=XE;UID=MyUID;PWD=MyPswd;"</i><br>
<i class="quotelev1">> `-----</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> I get an error. It reads as follows:</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> ,----- [ Error on pass-through query run from Access or code ]</i><br>
<i class="quotelev1">> | Reserved error(-7778); there is no message for this error</i><br>
<i class="quotelev1">> `-----</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> The error occurs on the following line:</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> ,----- [ VBA error line ]</i><br>
<i class="quotelev1">> | DoCmd.OpenQuery "qrySumInvcTest"</i><br>
<br>
<snip a lot of code>
<br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> Thanks again!!!</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> --</i><br>
<i class="quotelev1">> Regards,</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> Greg Strong</i><br>

Closing and reopening connections to an Oracle database is a very bad idea. There is a certain amount of overhead on the server required to create a new connection - verify security, spawn a new process or create a new thread, etc. Connection pooling helps to minimize the impact of accidentally closing and reopening database connections.

Here is something to play with in Access, tested with Access 2003 and Oracle 10g R2.
<br>

#1. Add a reference to Microsoft ActiveX Data Objects Library - I used version 2.6.
<br>

In the module, add the following code (replace MYSID with the Oracle SID for your database):

'-----------------------------------------
Option Compare Database

Dim dbDatabase As New ADODB.Connection

Function RetrieveData(strUserName As String, strPassword As String) As String

    Dim strSQL As String
<br>

    Dim snpData As New ADODB.Recordset

    On Error Resume Next

    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MYSID;User ID=" & strUserName & ";Password=" & strPassword

    dbDatabase.ConnectionTimeout = 40
    dbDatabase.CursorLocation = adUseClient
    dbDatabase.CommandTimeout = 60
    dbDatabase.Open
    If (dbDatabase.State <> 1) Or (Err <> 0) Then
        dbDatabase.Close
        Set dbDatabase = Nothing
        RetrieveData = "ERROR: CONNECT FAIL"
        Exit Function

<br>

    End If

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  COUNT(*) TABLE_COUNT" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  DBA_TABLES"

<br>

    snpData.Open strSQL, dbDatabase

    If snpData.State = 1 Then

        If Not (snpData.EOF) Then
            RetrieveData = snpData("table_count")
        Else
            RetrieveData = "ERROR: NO ROWS"
        End If
    Else
        RetrieveData = "ERROR: " & Error
    End If
<br>

    snpData.Close
<br>

    Set snpData = Nothing

    dbDatabase.Close
<br>

    Set dbDatabase = Nothing
<br>

End Function

'-----------------------------------------


Testing the function. Create a view/query that accesses one of the tables defined in Access and the function (I used a PART table):
<strong>SELECT
</strong>
<br>

  ID,
<br>
<strong> DESCRIPTION,
</strong>
<br>

  RetrieveData("SYS","SYSPASSWORD")
<br>
<strong>FROM
</strong>
<br>
<strong> PART
</strong>
<br>
<strong>WHERE
</strong>
<br>
<strong> ID='ABC123';
</strong>

If the wrong password is specified, the field result will be "ERROR: CONNECT FAIL". If this is not a DBA user account, the field result will be "ERROR: ORA-00942: table or view does not exist". If this is a DBA user, the field result will be the count of the tables in the database.

The way the code is written, it is possible to change the user name and password to test the various scenarios without closing Access.

Once again, Closing and reopening connections to an Oracle database is a very bad idea.

Charles Hooper
<br>

PC Support Specialist
<br>

K&M Machine-Fabricating, Inc.
<span id="received"><dfn>Received on</dfn> Sun Jul 09 2006 - 15:14:24 CDT</span>
</div>
Received on Sun Jul 09 2006 - 15:14:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US