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

[Greg Strong]

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

,----- [ 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 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

PS. Sorry for double post, but since subject expanded (i.e. DSN-less) I
thought a cross post with more relevant subject appropriate.

 

[MGFoster]

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

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

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

< SNIP >

 

[Greg Strong]

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"

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!

 

[Douglas J. Steele]

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.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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"

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!

 

[Greg Strong]

Hello Douglas,

On Sun, 9 Jul 2006 19:51:58 -0400, "Douglas J. Steele"

First thanks for the help!

What value do you have for strOConn?

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.

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

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

Just curious as to where that came from. Carl Prothman doesn't list that
particular driver anywhere at
http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx

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!

 

[david epsom dot com dot au]

DoCmd.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.

form, it does NOT close the connection. The only way to close
connection is to close Access. This is not good if different

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)

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

,----- [ 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 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

PS. Sorry for double post, but since subject expanded (i.e. DSN-less) I
thought a cross post with more relevant subject appropriate.

 

[Albert D.Kallal]

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 ...).

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
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

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)

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

,----- [ 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 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

PS. Sorry for double post, but since subject expanded (i.e. DSN-less) I
thought a cross post with more relevant subject appropriate.

 

[Greg Strong]

DoCmd.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.

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.

 

[david epsom dot com dot au]

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

the application.

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)

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 ...).

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
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

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)

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

,----- [ 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 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

PS. Sorry for double post, but since subject expanded (i.e. DSN-less) I
thought a cross post with more relevant subject appropriate.