Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by Greg Stron » Tue, 11 Jul 2006 03:57:49


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

,----- [ Pass-Through Query "qrySumInvcTest" ]
| select sum(invcamt) as TotInvc from sc.tblRevenuePrYr;
`-----

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 ent
 
 
 

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by Greg Stron » Tue, 11 Jul 2006 04:11:07

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

,----- [ Pass-Through Query "qrySumInvcTest" ]
| select sum(invcamt) as TotInvc from sc.tblRevenuePrYr;
`-----

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 ent
 
 
 

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by MGFoste » Tue, 11 Jul 2006 06:40:55

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need to close the QueryDef before running the OpenQuery command and
you don't need the QueryDefs' ReturnsRecords property set.

Set qd = dbCur.QueryDefs("qrySumInvcTest")
qd.Connect = strOConn
qd.Close
DoCmd.OpenQuery "qrySumInvcTest"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRLF344echKqOuFEgEQK6jgCfdqq+an5DA+1Q1kLJ9N30xdVoEOgAn279
4kKfJEG75td11TtjwyO+lHkZ
=uOPb
-----END PGP SIGNATURE-----



< SNIP >


< SNIP >


< SNIP >
 
 
 

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by Greg Stron » Tue, 11 Jul 2006 08:01:44


I now receive an error that reads as follows:

,----- [ Error ]
| Run-time error '3000':
| Reserved error (-7778); there is no message for this error.
`-----

The error still occurs on the following line:

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


Thanks for the help!

--
Regards,

Greg Strong
 
 
 

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by Douglas J. » Tue, 11 Jul 2006 08:51:58

What value do you have for strOConn?

In your previous post, you listed a connection string "ODBC;Driver={Oracle
in XE};Dbq=XE;UID=MyUID;PWD=MyPswd;"

Just curious as to where that came from. Carl Prothman doesn't list that
particular driver anywhere at
http://www.yqcomputer.com/

--
Doug Steele, Microsoft Access MVP
http://www.yqcomputer.com/
(no private e-mails, please)
 
 
 

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by Greg Stron » Tue, 11 Jul 2006 10:22:42

ello Douglas,

On Sun, 9 Jul 2006 19:51:58 -0400, "Douglas J. Steele"
< XXXX@XXXXX.COM > wrote:

First thanks for the help!


ODBC;Driver={Oracle in XE};Dbq=XE;UID=MyUID;PWD=MyPswd;

The above is copy & pasted from a debug.print of strOConn. Obviously
I've modified the actual UID & Password.


Same thing as above except not actual values for MyUID& MyPswd.


The actual file is "SQORA32.dll" dated 2/9/2006 for the driver with the
"Oracle in XE" name. The actual driver must be installed with Oracle 10g
Express Edition, since I did NOT download and install it. It is located
in the following directory:

X:\oraclexe\app\oracle\product\10.2.0\server\BIN

This is sub-directory of where I installed Oracle 10g Express Edition.
You are correct in that he doesn't really list it under "ODBC Driver for
Oracle (from Oracle)". So I kind of ad lib from the instructions for
"ODBC Driver for Oracle - from Oracle" which state "Where: The DBQ name
must be defined in the tnsnames.ora file". This may be where I have a
problem since I haven't really worked with the "tnsnames.ora" file
previously, and there is no direct cross reference with "DBQ" in the
file. The really strange part is this same connection string works with
your "FixConnections" function located at
http://www.accessmvp.com/djsteele/DSNLessLinks.html. Now I modified it
to call the "Sub SetConStr()" in the original post. The modifications
are as follows:

Sub FixConnections()
...
Call SetConStr

tdfCurrent.Connect = strOConn
...
End Sub

I didn't have to pass the ServerName & Database name because they are
included in "strOConn". The just of it is your "FixConnections" sub
works and my test sub on a pass-through query does NOT for what ever
reason. The interesting part is the ODBC connection remains open with
the DSN-less connection as well unless you close Access altogether. So
even though I might eventually solve the DSN-less connection, I will
still have the open connection problem that exists with the DSN. I do
like the DSN-less solution better.

This test is actually part of solution on building a sample DB
that I had working in SQL Server using an Access ADP file for the front
end. I thought using an Access MDB file as the front end would add some
flexibility for temporary tables to build combo boxes as well as
learning Oracle and interface issues with Access. I am sure I have some
higher hurdles down the road. :)

For the benefit I will include my tnsnames.ora file below:

=====================>Begin File>===================================>
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gws-p4-2-4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
=====================<End File<=====================================<

Again thanks for all of the help!

--
Regards,

Greg Strong
 
 
 

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by david epso » Tue, 11 Jul 2006 13:09:02

oCmd.OpenQuery "qrySumInvcTest"
qd.Connect = "ODBC;DSN=OracleXE;DATABASE=4GetU;;UID=NoGo;" & _

You're trying to open the query before you set the connect?
That's never going to work.


You're using Application.DoCmd to display the data. Once
Application opens the connection, it stays open until you
close Application (or it times out or ...).

If you want to close the connection, you are going to have
to use a different display method (an unbound form), with
a seperate ADO object or DAO.dbEngine object.

(david)

"Greg Strong" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by Albert D.K » Tue, 11 Jul 2006 13:28:02

gt; You're using Application.DoCmd to display the data. Once

With odbc to sql server, the connections remain open until you shut down the
application.

I also would like to be able to close the connection also....

As far as I know, this behaviors does not change if you use dsn-less, or
not..

To close the connection...you have to shutdown ms-access....which often I
don't want to do....

There might be another way, but not spent the time looking for, or trying
solutions.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
XXXX@XXXXX.COM
http://www.members.shaw.ca/AlbertKallal







 
 
 

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by Greg Stron » Tue, 11 Jul 2006 13:42:44

On Mon, 10 Jul 2006 14:09:02 +1000, "david epsom dot com dot au"



If you look a few lines above you will find:

qd.Connect = "ODBC;" & strCnn

I found the connection string which includes the UID and password is
saved in the PTQ's properties, so the code you quote above is to
actually change it to some bogus value.

--
Regards,

Greg Strong
 
 
 

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Post by david epso » Tue, 11 Jul 2006 14:32:09

gt; With odbc to sql server, the connections remain open until you shut


Or until they time out, or are shut by system that limits
the number of open connections, or, if you use a separate object,
until the object that you used to open them closes.

(david)

"Albert D.Kallal" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...