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">

<dfn>From</dfn>: Charles Hooper <hooperc2000_at_yahoo.com>

<dfn>Date</dfn>: 9 Jul 2006 13:14:24 -0700
<dfn>Message-ID</dfn>: <1152476064.761619.153050_at_h48g2000cwc.googlegroups.com>

</address>

 

Greg Strong wrote:
 
> 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" 
 
<snip a lot of code>
 
> 
> Thanks again!!! 
> 
> -- 
> Regards, 
> 
> Greg Strong 

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.
 

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

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
 

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

 

End If

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

 

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
 

snpData.Close
 

Set snpData = Nothing

dbDatabase.Close
 

Set dbDatabase = Nothing
 

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):
SELECT

 

ID,
 
 DESCRIPTION,

 

RetrieveData("SYS","SYSPASSWORD")
 
FROM

 
 PART

 
WHERE

 
 ID='ABC123';


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
 

PC Support Specialist
 

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