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

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"
`-----
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12835-close-odbc-connection-dsn-vs-dsn.html

,----- [ 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!!!", _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
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============<=====================================<


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


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


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

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.

--
Regards,

Greg Strong

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

-----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 earthlink 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+1Q1kLJ9N30xd VoEOgAn279
4kKfJEG75td11TtjwyO+lHkZ
=uOPb
-----END PGP SIGNATURE-----

Greg Strong wrote:
> 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?
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835

< SNIP >

> 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 ]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
> | DoCmd.OpenQuery "qrySumInvcTest"
> `-----

< SNIP >

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

On Sun, 09 Jul 2006 2155 GMT, MGFoster wrote:

>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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
`-----

The error still occurs on the following line:

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


Thanks for the help!

--
Regards,

Greg Strong

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/Technolo...0/Default.aspx

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Greg Strong" wrote in message
news:nf23b25632l21jf51rcej2tb09f1tkoroa@4ax.com...
> On Sun, 09 Jul 2006 2155 GMT, MGFoster wrote:
>
>>You need to close the QueryDef before running the OpenQuery command and
>>you don't need the QueryDefs' ReturnsRecords property set.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
>>
>>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!
>
> --
> Regards,
>
> Greg Strong


Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835

Hello Douglas,

On Sun, 9 Jul 2006 1958 -0400, "Douglas J. Steele"
wrote:

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/Technolo...0/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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
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)
)
)
=====================
Again thanks for all of the help!

--
Regards,

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.

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

"Greg Strong" wrote in message
news:v4l2b2pd2pfe239e4khno2spdclgomici4@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 ]
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
> | 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============<=====================================<
>
>
> ======PUBLIC VAR=====>Begin Code>===================================>
> Public strCnn As String, strOConn As String, strMSOCnn As String
> =====================<=====================================<
>
>
> ======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====<=====================================<
>
>
> ======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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
> 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======<=====================================<
>
> 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.
>
> --
> Regards,
>
> Greg Strong

> 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
pleaseNOOSpamKallal@msn.com
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)
>
> "Greg Strong" wrote in message
> news:v4l2b2pd2pfe239e4khno2spdclgomici4@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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
>> 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============<=====================================<
>>
>>
>> ======PUBLIC VAR=====>Begin Code>===================================>
>> Public strCnn As String, strOConn As String, strMSOCnn As String
>> =====================<=====================================<
>>
>>
>> ======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====<=====================================<
>>
>>
>> ======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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
>> 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======<=====================================<
>>
>> 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.
>>
>> --
>> Regards,
>>
>> Greg Strong
>
>

On Mon, 10 Jul 2006 1402 +1000, "david epsom dot com dot au"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
wrote:

>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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
saved in the PTQ's properties, so the code you quote above is to
actually change it to some bogus value.

--
Regards,

Greg Strong

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

"Albert D.Kallal" wrote in message
news:uTY4Bl9oGHA.4116@TK2MSFTNGP03.phx.gbl...
>> You're using Application.DoCmd to display the data. Once
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
>> 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
> pleaseNOOSpamKallal@msn.com
> 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)
>>
>> "Greg Strong" wrote in message
>> news:v4l2b2pd2pfe239e4khno2spdclgomici4@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"
>>> `-----
>>>
>>> ,----- [ 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============<=====================================<
>>>
>>>
>>> ======PUBLIC VAR=====>Begin Code>===================================>
>>> Public strCnn As String, strOConn As String, strMSOCnn As String
>>> =====================<=====================================<
>>>
>>>
>>> ======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====<=====================================<
>>>
>>>
>>> ======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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12835
>>> 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======<=====================================<
>>>
>>> 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.
>>>
>>> --
>>> Regards,
>>>
>>> Greg Strong
>>
>>
>
>