473,573 Members | 2,919 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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={O racle in XE};Dbq=XE;UID= MyUID;PWD=MyPsw d;"
`-----

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.tblRevenuePr Yr;
`-----

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.Worksp aces(0)
Set dbCur = wsCur.Databases (0)
Call SetConStr
If strCnn = "" Then
Exit Sub
End If
Set qd = dbCur.QueryDefs ("qrySumInvcTes t")
qd.Connect = "ODBC;" & strCnn
qd.ReturnsRecor ds = True
DoCmd.OpenQuery "qrySumInvcTest "
qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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("f rmMain").Contro ls("txtUserID") .Value) Then
MsgBox "Please enter your User ID!", , "Enter User ID"
Exit Sub
End If
If IsNull(Forms("f rmMain").Contro ls("txtPswd").V alue) Then
MsgBox "Please enter your Password!", , "Enter Password"
Exit Sub
End If
strUID = Forms("frmMain" ).Controls("txt UserID").Value
strPswd = Forms("frmMain" ).Controls("txt Pswd").Value
strCnn = "DSN=OracleXE;D ATABASE=XE;;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"

' strOConn = "Driver={Or acle in XE};" & _
' "Dbq=XE;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"
strOConn = "ODBC;Driver={O racle in XE};" & _
"Dbq=XE;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
' strOConn = "ODBC;Driver={O racle in XE};" & _
' "DATABASE=X E;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"

strMSOCnn = "Driver={Micros oft ODBC for Oracle};" & _
"Server=DEDICAT ED;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
Debug.Print "strCnn: "; strCnn
Debug.Print "strOConn: "; strOConn
Debug.Print "strMSOCnn: "; strMSOCnn
End Sub
======CONNECT STR====<End Code><========= =============== =============<
======DSN-LESS=======>Beg in 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.Worksp aces(0)
Set dbCur = wsCur.Databases (0)
Call SetConStr
If strOConn = "" Then
Exit Sub
End If

Set qd = dbCur.QueryDefs ("qrySumInvcTes t")
qd.Connect = strOConn
qd.ReturnsRecor ds = True
DoCmd.OpenQuery "qrySumInvcTest "
'qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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.

--
Regards,

Greg Strong
Jul 9 '06 #1
8 9618
-----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 ("qrySumInvcTes t")
qd.Connect = strOConn
qd.Close
DoCmd.OpenQuery "qrySumInvcTest "

--
MGFoster:::mgf0 0 <atearthlink <decimal-pointnet
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/AwUBRLF344echKq OuFEgEQK6jgCfdq q+an5DA+1Q1kLJ9 N30xdVoEOgAn279
4kKfJEG75td11Tt jwyO+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?
< 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 ]
| DoCmd.OpenQuery "qrySumInvcTest "
`-----
< SNIP >
Set qd = dbCur.QueryDefs ("qrySumInvcTes t")
qd.Connect = strOConn
qd.ReturnsRecor ds = True
DoCmd.OpenQuery "qrySumInvcTest "
'qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;UID=NoGo;" & _
"PWD=4GetIt ;"

qd.Close
< SNIP >
Jul 9 '06 #2
On Sun, 09 Jul 2006 21:40:55 GMT, MGFoster <me@privacy.com 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 ("qrySumInvcTes t")
qd.Connect = strOConn
qd.Close
DoCmd.OpenQuer y "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
Jul 9 '06 #3
What value do you have for strOConn?

In your previous post, you listed a connection string "ODBC;Driver={O racle
in XE};Dbq=XE;UID= MyUID;PWD=MyPsw d;"

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" <ne*********@ge edubeeu.com.inv alidwrote in message
news:nf******** *************** *********@4ax.c om...
On Sun, 09 Jul 2006 21:40:55 GMT, MGFoster <me@privacy.com 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 ("qrySumInvcTes t")
qd.Connect = strOConn
qd.Close
DoCmd.OpenQue ry "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

Jul 9 '06 #4
Hello Douglas,

On Sun, 9 Jul 2006 19:51:58 -0400, "Douglas J. Steele"
<NOSPAM_djsteel e@NOSPAM_canada .comwrote:

First thanks for the help!
>What value do you have for strOConn?
ODBC;Driver={Or acle in XE};Dbq=XE;UID= MyUID;PWD=MyPsw d;

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={O racle
in XE};Dbq=XE;UID= MyUID;PWD=MyPsw d;"
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.dl l" 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.o ra" 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.Conn ect = 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_CONNECT ION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE) )
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

ORACLR_CONNECTI ON_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
Jul 10 '06 #5
DoCmd.OpenQuery "qrySumInvcTest "
qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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.DoC md 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" <ne*********@ge edubeeu.com.inv alidwrote in message
news:v4******** *************** *********@4ax.c om...
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={O racle in XE};Dbq=XE;UID= MyUID;PWD=MyPsw d;"
`-----

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.tblRevenuePr Yr;
`-----

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.Worksp aces(0)
Set dbCur = wsCur.Databases (0)
Call SetConStr
If strCnn = "" Then
Exit Sub
End If
Set qd = dbCur.QueryDefs ("qrySumInvcTes t")
qd.Connect = "ODBC;" & strCnn
qd.ReturnsRecor ds = True
DoCmd.OpenQuery "qrySumInvcTest "
qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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("f rmMain").Contro ls("txtUserID") .Value) Then
MsgBox "Please enter your User ID!", , "Enter User ID"
Exit Sub
End If
If IsNull(Forms("f rmMain").Contro ls("txtPswd").V alue) Then
MsgBox "Please enter your Password!", , "Enter Password"
Exit Sub
End If
strUID = Forms("frmMain" ).Controls("txt UserID").Value
strPswd = Forms("frmMain" ).Controls("txt Pswd").Value
strCnn = "DSN=OracleXE;D ATABASE=XE;;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"

' strOConn = "Driver={Or acle in XE};" & _
' "Dbq=XE;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"
strOConn = "ODBC;Driver={O racle in XE};" & _
"Dbq=XE;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
' strOConn = "ODBC;Driver={O racle in XE};" & _
' "DATABASE=X E;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"

strMSOCnn = "Driver={Micros oft ODBC for Oracle};" & _
"Server=DEDICAT ED;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
Debug.Print "strCnn: "; strCnn
Debug.Print "strOConn: "; strOConn
Debug.Print "strMSOCnn: "; strMSOCnn
End Sub
======CONNECT STR====<End Code><========= =============== =============<
======DSN-LESS=======>Beg in 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.Worksp aces(0)
Set dbCur = wsCur.Databases (0)
Call SetConStr
If strOConn = "" Then
Exit Sub
End If

Set qd = dbCur.QueryDefs ("qrySumInvcTes t")
qd.Connect = strOConn
qd.ReturnsRecor ds = True
DoCmd.OpenQuery "qrySumInvcTest "
'qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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.

--
Regards,

Greg Strong

Jul 10 '06 #6
You're using Application.DoC md 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...yo u 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
pl************* ****@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" <ne*********@ge edubeeu.com.inv alidwrote in message
news:v4******** *************** *********@4ax.c om...
>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={O racle in XE};Dbq=XE;UID= MyUID;PWD=MyPsw d;"
`-----

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.tblRevenuePr Yr;
`-----

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.Worksp aces(0)
Set dbCur = wsCur.Databases (0)
Call SetConStr
If strCnn = "" Then
Exit Sub
End If
Set qd = dbCur.QueryDefs ("qrySumInvcTes t")
qd.Connect = "ODBC;" & strCnn
qd.ReturnsReco rds = True
DoCmd.OpenQuer y "qrySumInvcTest "
qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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.Descriptio n, , "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><========= =============== =============<
======CONNEC T STR====>Begin Code>========== =============== ==========>
Sub SetConStr()
Dim strUID As String, strPswd As String
Forms("frmMain ").Refresh
strCnn = ""
strOConn = ""
If IsNull(Forms("f rmMain").Contro ls("txtUserID") .Value) Then
MsgBox "Please enter your User ID!", , "Enter User ID"
Exit Sub
End If
If IsNull(Forms("f rmMain").Contro ls("txtPswd").V alue) Then
MsgBox "Please enter your Password!", , "Enter Password"
Exit Sub
End If
strUID = Forms("frmMain" ).Controls("txt UserID").Value
strPswd = Forms("frmMain" ).Controls("txt Pswd").Value
strCnn = "DSN=OracleXE;D ATABASE=XE;;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"

' strOConn = "Driver={Or acle in XE};" & _
' "Dbq=XE;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"
strOConn = "ODBC;Driver={O racle in XE};" & _
"Dbq=XE;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
' strOConn = "ODBC;Driver={O racle in XE};" & _
' "DATABASE=X E;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"

strMSOCnn = "Driver={Micros oft ODBC for Oracle};" & _
"Server=DEDICA TED;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
Debug.Print "strCnn: "; strCnn
Debug.Print "strOConn: "; strOConn
Debug.Print "strMSOCnn: "; strMSOCnn
End Sub
======CONNEC T STR====<End Code><========= =============== =============<
======DSN-LESS=======>Beg in 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.Worksp aces(0)
Set dbCur = wsCur.Databases (0)
Call SetConStr
If strOConn = "" Then
Exit Sub
End If

Set qd = dbCur.QueryDefs ("qrySumInvcTes t")
qd.Connect = strOConn
qd.ReturnsReco rds = True
DoCmd.OpenQuer y "qrySumInvcTest "
'qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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.Descriptio n, , "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.

--
Regards,

Greg Strong


Jul 10 '06 #7
On Mon, 10 Jul 2006 14:09:02 +1000, "david epsom dot com dot au"
<david@epsomdot comdotauwrote:
>DoCmd.OpenQuer y "qrySumInvcTest "
qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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.

--
Regards,

Greg Strong
Jul 10 '06 #8
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" <Pl************ *******@msn.com wrote in message
news:uT******** ******@TK2MSFTN GP03.phx.gbl...
>You're using Application.DoC md 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...yo u 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
pl************* ****@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" <ne*********@ge edubeeu.com.inv alidwrote in message
news:v4******* *************** **********@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={O racle in XE};Dbq=XE;UID= MyUID;PWD=MyPsw d;"
`-----

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.tblRevenuePr Yr;
`-----

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.Worksp aces(0)
Set dbCur = wsCur.Databases (0)
Call SetConStr
If strCnn = "" Then
Exit Sub
End If
Set qd = dbCur.QueryDefs ("qrySumInvcTes t")
qd.Connect = "ODBC;" & strCnn
qd.ReturnsRec ords = True
DoCmd.OpenQue ry "qrySumInvcTest "
qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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.Descripti on, , "Error"
Set qd = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
End Sub
======DSN==== ========<End Code><========= =============== =============<
======PUBLI C VAR=====>Begin Code>========== =============== ==========>
Public strCnn As String, strOConn As String, strMSOCnn As String
============= ========<End Code><========= =============== =============<
======CONNE CT STR====>Begin Code>========== =============== ==========>
Sub SetConStr()
Dim strUID As String, strPswd As String
Forms("frmMai n").Refresh
strCnn = ""
strOConn = ""
If IsNull(Forms("f rmMain").Contro ls("txtUserID") .Value) Then
MsgBox "Please enter your User ID!", , "Enter User ID"
Exit Sub
End If
If IsNull(Forms("f rmMain").Contro ls("txtPswd").V alue) Then
MsgBox "Please enter your Password!", , "Enter Password"
Exit Sub
End If
strUID = Forms("frmMain" ).Controls("txt UserID").Value
strPswd = Forms("frmMain" ).Controls("txt Pswd").Value
strCnn = "DSN=OracleXE;D ATABASE=XE;;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"

' strOConn = "Driver={Or acle in XE};" & _
' "Dbq=XE;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"
strOConn = "ODBC;Driver={O racle in XE};" & _
"Dbq=XE;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
' strOConn = "ODBC;Driver={O racle in XE};" & _
' "DATABASE=X E;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"

strMSOCnn = "Driver={Micros oft ODBC for Oracle};" & _
"Server=DEDIC ATED;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
Debug.Print "strCnn: "; strCnn
Debug.Print "strOConn: "; strOConn
Debug.Print "strMSOCnn: "; strMSOCnn
End Sub
======CONNE CT STR====<End Code><========= =============== =============<
======DSN-LESS=======>Beg in 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.Worksp aces(0)
Set dbCur = wsCur.Databases (0)
Call SetConStr
If strOConn = "" Then
Exit Sub
End If

Set qd = dbCur.QueryDefs ("qrySumInvcTes t")
qd.Connect = strOConn
qd.ReturnsRec ords = True
DoCmd.OpenQue ry "qrySumInvcTest "
'qd.Connect = "ODBC;DSN=Oracl eXE;DATABASE=4G etU;;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.Descripti on, , "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.

--
Regards,

Greg Strong



Jul 10 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3511
by: kakaz | last post by:
Hi All there! I am quite new in MS SQL administration so let me explain how it work on Your instances of SQL Servers. We have several DTS packages on our server, all of them managed on some station which have seriously hardvare problem. So we wolud like to catch two problems at one time and decided to develop systematic way of DTS...
2
3249
by: John Thorne | last post by:
Hello Windows XP sp2, Installed MyODBC 3.51 and the driver. It does not appear in the User/System DSN ODBC control panel. Does not appear in the driver tab Is listed in the ODBCINST.INI and appears in the registry REGEDIT4 Windows Registry Editor Version 5.00
1
2930
by: eight02645999 | last post by:
hi i have a piece of code: .... def connectdb(sql): import dbi import odbc import sys try:
8
3478
by: Philip Wright | last post by:
I am trying to connect to a DB2 v7.2 database from Lotus 123 v9.8 on a Windows XP Pro machine. I have the Software Development Client installed on the XP machine and have run the Client Configuration Assistant and registered the SAMPLE and my TEST databases with the ODBC Administrator as System DSN data sources. Everything seems fine, but...
2
6178
by: serge | last post by:
Do I have to use the DB2 Configuration Assistant in order to create an ODBC System DSN? I had a windows computer with no IBM DB2 installed. I downloaded the IBM DB2 client i think which after the installation i saw the ODBC Driver added for IBM DB2. However i couldn't create a system dsn that worked when i tried to connect to an IBM DB2...
8
2836
by: DerekS | last post by:
Hi, I've been pulling my hair out trying to write a simple method to programatically create a system DSN with all parameters on a remote machine. I have ensured I have the correct permissions on that remote machine. I have also review many MSDN and codeproject.com articles. Any help would be great. Thanks, Derek
1
1398
by: Maileen | last post by:
Hi, I'm writing a VC++.net 2005 application and i would like to use the ODBC connector. I would like to allow user to choose the DB he wants to have (MS SQL, PostgreSQl, MySQL, Oracle,...) by choosing the write ODBC driver. After that, i want to create a system DSN with information enter by user. But how can i do that ?
3
3471
by: Niks | last post by:
Hi, I need to connect to SQL server Database using a System DSN. Can anyone tell me how to connect to SQL Server using DSN in ASP.NET (VB.Net). Using a Try Catch block. Does anyone know how to create a "filename.DSN" file. I know how to create a system DSN, but my N/W admin has asked me to create a DSN file to send him. which would be a...
0
2084
by: amg | last post by:
I'm looking for a way to automate the creation of an ODBC System DSN for an Oracle driver (Oracle client 10g). Any help (scripts, pointers, sample code) will be greatly appreciated. Thanks,
2
2745
by: Dale Sampson | last post by:
My app uses an ODBC connection for a .mdb file. I want the app to create the source if it does not already exist (I copy a blank .mdb file to the users data store if it doesn't exist). I see how to do this using SQLConfigDataSource() and that works ok. Thing is, how to handle if the source already exists. Is there a way to check & see if...
0
7771
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7686
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8198
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7771
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8060
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6406
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3731
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2194
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1296
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.